Coping with missing data...

vesc

Board Regular
Joined
Mar 17, 2006
Messages
83
What is the best way to do the following...

You have a list of numbers, let's say vertically in Excel
Next to it you have calculation like formula of B2 = A2/A1
You copy this formula down as far as your list goes.
Now... some entries in your list are blank. If it's only one entry at a time (non-contiguous blanks) it's easy to put an IF clause around this in entries past B2, for example: B3's formula would be IF(ISBLANK(A2),A3/A1,A3/A2)

However, the IFs required to handle any number of contiguous blanks becomes overly complex. Is there another way to say: do this operation on the most recent non-blank cell in column/row A?

Thanks in advance,

Vesc
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Hi Vesc

See if this works for you:

Code:
=LOOKUP(9.99E+307,$A$1:A3)/LOOKUP(9.9999E+307,INDIRECT("$A$1:A" & LOOKUP(9.9999E+307,$A$1:A3,ROW($A$1:A3))-1))

This is the formula as entered in B3 and copied down (assumes your data starts at A1).

Best regards

Richard
 
Upvote 0
Wow...

Richard, I will try this as soon as my daily processes are completed. Does this require the data to be sorted? Just wondering due to the use of he giant number in the lookups.

Thanks,

Vesc
 
Upvote 0
Vesc

No, data doesn't need to be sorted - just ensure you have the absolute referencing symbols in the right place (ie as given). The range that the lookup extends to will change on each line (due to the $s and lack of them).

Richard
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top