Skipping Empty Cells with a Formula

[raccoon588]

I have a worksheet that goes over a 24hr period. Each hour a user enters their hourly count. One of my lines has a running daily count. I have =IF(K1677="","",K1677-K1676) as a formula. It takes the amount enters in the previous hour and subtracts it from the current hour to give the actual number for the hour. there are times when they need to leave a few hours of the day empty. how can i get the formula to grab the number in the last populated cell and subtract that from the current number?


thank you

 

[pgc01]

Hi

Try:

=IF(K1677="","",K1677-IFERROR(LOOKUP(1E+300,$K$1:K1676),0))

 

[raccoon588]

Can you explain what this formula does? I need to be able to understand it so i can apply it. thank you

 

[pgc01]

Hi

LOOKUP(1E+300,$K$1:K1676)

returns the last number value in the range $K$1:K1676, which I understood is what you want to subtract from K1677

 

[raccoon588]

K1775 has 14448
K1776-K1778 are blank
K1779 has 14930
I need a formula that will skip those blank cells then take K1779(14930) and subtract K1775(14448) to give me 482 as a final result. but i need it to work as numbers are added. so say 100 cells later they leave blank cells, it should be able to take the newest entry and subtract it from the last. I tried to insert an image but it wont let me.