Excel Formula Situation

Posted by Jason White on December 29, 2000 9:03 AM

I need to get Excel to think like I do.

I'm trying to get it to figure out the miles driven when the odometer reading is entered.

Only the data isn't entered every day....there are days where nothing is entered so....this poses a problem to me at getting excel to 'find' the last entry of the month and subtract it from the first entry of the month.

Does anyone know if this is possible....I'm sure it is...but I have little hair left to pull out.

Thanks in advance.

Jason

Posted by Aaron on December 29, 2000 10:46 AM

Assuming your range of entries is in A1:A31 use:

=MAX(A1:A31)-MIN(A1:A31)

Change the range as needed.

Posted by cpod on December 29, 2000 11:55 AM

Well, if you would consider adding a third column that would have the mileage differences between each entry Column A being the date, column B the odometer reading, and column C:

B2-B1

Then this will give you twelve rows totaling the mileage for each month:

={SUM(N(MONTH($A$1:$A$10)=ROW()-1)*($C$1:$C$10))}

This is an array formula, so you must press Control + Shift + Enter, and must be placed in the second row.

If I figure out how to do this without the added column I will let you know.

Posted by Jason White on December 29, 2000 12:51 PM

THANK YOU!!!!

That's the ticket!!.....I've modified it to work across sheets also.

1. Had to add an IF, THEN statement to keep from getting wrong values when column C has all zero's (ie. months not yet here for the odometer readings)

=IF(C5:C35=0,0,MAX(C5:C35)-MIN(Mar!C5:C35))

April has to subtract from the last odometer reading from March....It works just perfect....I can't thank you enough!!!

I have to commend you, you are good!

Jason

Posted by Jason White on December 29, 2000 1:25 PM

This seemed to work...but I've corrected it.

=IF(MAX(C5:C35)=0,0,MAX(C5:C35)-MIN(Mar!C5:C35))

This works every time now.

Thanks again.

Jason