27 Apr 2011

I conquered!

Well the formula below took several hours of study to get it right. I could not find anywhere on the net (via Google) to do what I wanted it to do. But I had some help from someone called Sandy Man. [No I don’t know dis person – dey just happened to be out there on the net. Many thanks to Sandy Man].

It’s not about the formula really – it was the challenge of it – and teaching myself more about how excel works.

What the excel formula does is to calculate mileage according to rates that change over a certain threshold. That row 6  was most challenging.

2011_04_27_Mileage_spreadsheet

For those who need it the formula is:

=IF(SUM($B$4:B6,$B$1)<10000,B6*0.4,IF(AND(SUM($B$4:B6,$B$1)>10000,SUM($B$4:B5,$B$1)<10000),(SUM($B$4:B6,$B$1)-10000)*0.25+((B6-SUM($B$4:B6,$B$1-10000))*0.4),B6*0.25))

I have not completely error checked the formula. However it seems to work consistently well for my purposes. Others may borrow and tweak. I’m also thankful in advance for any Excel wizards out there who can show how to write this formula more efficiently.

Comments