Fill Blanks With Averages
Hi
I use Excel rarely and almost exclusively to generate data which is used to create Corel Draw images.
Searching on another site I found the following macro (written by Rick Rothstein) which seems to be close to what I am after. I think I understand everything except the important bit within the loop, which is not enough to be able to alter it to my needs, and wonder if someone would give an explanation of the line within the loop.
Code:
Sub FillBlanksWithAverages()
Dim LastRow As Long, Ar As Range
LastRow = Cells(Rows.Count, "G").End(xlUp).Row
For Each Ar In Range("G2:G" & LastRow).SpecialCells(xlBlanks).Areas
Ar.Value = (Ar(1).Offset(-1) + Ar(Ar.Count).Offset(1)) / 2
Next
End Sub
To try and explain what Im trying to achieve Im using an example of a grey scale image where each row generates a single integer between 0 and 255 representing a shade of grey but the intention is to triplicate the method to represent RGB.
In a spreadsheet with 2 columns 'A' & 'B': In Column A, I want to enter a value of between 0 and 255 in at least the top and bottom rows but may also enter values in any number of rows between them – in this example imagine we have entered the following four values –
10 in A1 (top row)
150 in A100,
75 in A900 and
20 in A1000 (bottom row).
In Column B I would like Excel to fill the intermediary values so that in this example they would gradually step up in value between B1 where it would show 10, to B100 where it would show 150 and then decreasing in value from B100 to B900 where it shows 75, and then decreasing from 75 to 20 between B900 to B1000.
I think the logic would go something like;
If this row has a value in Column A, use that value in Column B, otherwise
To calculate the value for column B in this row;
Use the A column value and row number of the first row ABOVE which has a value in column A and the A column value and row number of the first row BELOW which has a value in column A.
Take the value of ABOVE from the value of BELOW and divide by the number of rows between them, multiply that result by the number of rows between the current row and the row ABOVE, and then add the value in ABOVE.
I have already asked this question at Colour Gradients - Lookup next non-empty cell
