how do i set up a mileage allowance on excel.

[Guest]

i can't get my head around the if function!!
i get 0p for the first 15 miles then 19p for each mile after that up to 30
miles, then 29p per mile above that.
I'm sure there is some clever person out there who can help
Thanks

 

[RagDyeR]

With your mileage in A1, try this:

=MAX(A1-30,0)*29+((A1-15)-MAX(A1-30,0))*19

15 mi = 0
16 mi = 19
30 mi = 19
31 mi = 29
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


i can't get my head around the if function!!
i get 0p for the first 15 miles then 19p for each mile after that up to 30
miles, then 29p per mile above that.
I'm sure there is some clever person out there who can help
Thanks

 

[Guest]

Thats Great
Thankyou
Roland

 

[RagDyer]

Appreciate the feed-back.
--


Regards,

RD

 

[RagDyer]

BUT ... It appears that both you and I didn't test for miles below 15.

This should return a "0", when A1 is less then 15:

=MAX(A1-30,0)*29+(MAX(A1-15,0)-MAX(A1-30,0))*19
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Appreciate the feed-back.
--


Regards,

RD