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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
 

vesc

Board Regular
Joined
Mar 17, 2006
Messages
83
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
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
 

Forum statistics

Threads
1,136,420
Messages
5,675,731
Members
419,585
Latest member
popsin

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
Top