Need help with an MS Excel Script/Formula
 

I'm working on a form for my job (A monthly mileage statement). We record end of day mileage from the bottom up in column A, and the daily use in column B. 31 lines on the form. So Day 1 is Cell A41. (There are header cells)

What I'd like to do is have Cell B40 and up scan down to the next filled A cell and subtract out the Current day's end from the prior day's end to give the actual use. Essentially B40=A40-A41. However it has to be smart enough to skip a null cell (weekends, days out, etc).

Do I need a script or can this be done in a straight formula in a cell?

Thanks
~Dennys

Re: Need help with an MS Excel Script/Formula
 

You could Conditionally Format it to count Null Cells as 0 can't you?

Re: Need help with an MS Excel Script/Formula
 

Probably, it wouldn't matter. I just need it to recognize the next valid odometer reading for the calculation.

Re: Need help with an MS Excel Script/Formula
 

pm me with your email address and I'll do it for you.

Re: Need help with an MS Excel Script/Formula
 

Can you use column C (or any hidden column) to run a mileage tally (=sum(c40+b39))? Then all you need to do is subtract c40 from c39 to get your daily total.

~Aldin, who suspects there is a formula, but usually cheaps out like this

Re: Need help with an MS Excel Script/Formula
 

Or use a second sheet as a 'crib note/calc' sheet...

Re: Need help with an MS Excel Script/Formula
 

I know there's a COUNTBLANK function that might be helpful. Something along the lines of if the COUNTBLANK function = 1 then don't do the calculation.

Re: Need help with an MS Excel Script/Formula
 

Thanks for all the suggestions. I think I have it

B14 =IF(A14=0,0,(A14-LARGE(A15:A23,1)))

Logic: If Mileage input = 0 then Usage =0
Else Mileage = Existing Mielage -Largest (,1) (LARGE function) in
range of prior week's inputs (A15:A23) .

Turn off Zero results (non viewable)

Re: Need help with an MS Excel Script/Formula
 

That works (unless someone takes a week-long vacation) ;)

~Aldin, formulaicly

Re: Need help with an MS Excel Script/Formula
 

Yeah, I changed the second variable cell

B14 =IF(A14=0,0,(A14-LARGE(A15:$A$23,1)))

Where A23 represents the absolute lowest usable cell, so the search parameter will search from the current line to the entire bottom. Thanks for the catch.