How can I make a table or chart to interpolate between values?

lbeck · #1 09-30-2016, 07:57 PM

I enjoy looking at the performance of my investments over time (not rich - just like the visual feedback). I have constructed a spreadsheet with daily entries but there are many blank rows where I haven't entered data due to inactivity (either mine or the market).

I use the Excel autofill utility for date. So when I construct a graph I get this ugly sawtooth showing zeros where there is no data. What I would like is a nice smooth line chart that connects only the positive/negative values over time.

How do I do that?

Thanks

macropod · #2 09-30-2016, 08:10 PM

There are many possible interpolation algorithms. Perhaps the simplest is a linear one.
Suppose your dates (in ascending order) are in column A and the data are in column B and there are gaps in column B between rows 9 and 19. To generate a linear interpolation for the missing data, you could insert the following formula in B10 and copy down to B18:
=(B$9-B$18)/(A$9-A$18)+B9

lbeck · #3 10-01-2016, 09:19 AM

Quote:

Originally Posted by macropod

There are many possible interpolation algorithms. Perhaps the simplest is a linear one.
Suppose your dates (in ascending order) are in column A and the data are in column B and there are gaps in column B between rows 9 and 19. To generate a linear interpolation for the missing data, you could insert the following formula in B10 and copy down to B18:
=(B$9-B$18)/(A$9-A$18)+B9

Thanks for your thoughtful response, but it didn't work for me. Maybe I did something wrong, but tried several times.

Actually, I probably phrased my question incorrectly. I'm looking for a simple solution and don't need interpolation. I was thinking more along the lines of "if a cell is zero or blank, insert previous cell contents." That would keep the line graph from bottoming out and giving the appearance that investments went to zero on the days where there is no data entered.

charlesdh · #4 10-01-2016, 11:30 AM

Hi,

Can you just delete the "Blank" cells?
There is a code that will do this.

lbeck · #5 10-01-2016, 04:47 PM

Quote:

Originally Posted by charlesdh

Hi,

Can you just delete the "Blank" cells?
There is a code that will do this.

Yes I can manually delete blank cells and I've done that in the past. But it's a pain. If there's a function that will do that automatically I'd appreciate a reference to it if it won't disrupt structure (e.g., shifting cells to the wrong date).

charlesdh · #6 10-01-2016, 04:56 PM

HI,

Here's a code that may work. It's a modified version of a code the "NoSpark" created.

Copy and paste to code module.

Code:

Sub DeleteBlank()
'''Modified code that "NoSparks created.
' remove rows with blank in column "B"

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With


'delete rows where B is blank
Range("B1", Cells(Rows.Count, "B").End(xlUp)).SpecialCells(xlBlanks).EntireRow.Delete

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With

End Sub

Test a a copy of your data.

charlesdh · #7 10-02-2016, 11:18 AM

Hi,

I erred on the previous code posted. The following code should work.

Code:

Sub DeleteBlank()
' remove rows with blank in column "B"

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With


For Each cell In Range("A1", Cells(Rows.Count, "A").End(xlUp))
If Cells(cell.Row, 2).Value = "" Then
    Cells(cell.Row, 2).EntireRow.Delete
End If
Next
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With

End Sub

Pecoflyer · #8 10-03-2016, 12:37 AM

@lebeck
Hello
please do not quote entire posts unnecessarily. They are just clutter and make the thread hard to read.
Thanks

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
how can I make a ScrollBar &/or a SpinButton display values?	jeffk	Excel	3	01-04-2016 04:25 AM
Format Chart X axis - inconsistent values	TimTDP	Excel	2	12-01-2015 02:56 AM
Is it possible to make such chart in Word?	am0	Word	3	12-12-2014 02:13 AM
Generate chart from list of text values	knownunknown	Excel	6	04-24-2013 01:56 AM
Word chart with words for y-values?	Computerbum	Office	0	09-19-2011 07:09 PM