# Coping with missing data...

#### vesc

##### Board Regular
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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### Richard Schollar

##### MrExcel MVP
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
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
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

Replies
2
Views
254
Replies
3
Views
508
Replies
0
Views
385
Replies
8
Views
632
Replies
15
Views
574

Threads
1,170,942
Messages
5,872,857
Members
432,950
Latest member
ALeXceLBr

### 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

### 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