|
how do i set up a mileage allowance on excel.
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 |
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! ============================================== "spannerhappy" wrote in message ... 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 |
Thats Great
Thankyou Roland "RagDyeR" wrote: 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! ============================================== "spannerhappy" wrote in message ... 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 |
Appreciate the feed-back.
-- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit! ------------------------------------------------------------------- "spannerhappy" wrote in message ... Thats Great Thankyou Roland "RagDyeR" wrote: 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! ============================================== "spannerhappy" wrote in message ... 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 |
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! ============================================== "RagDyer" wrote in message ... Appreciate the feed-back. -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit! ------------------------------------------------------------------- "spannerhappy" wrote in message ... Thats Great Thankyou Roland "RagDyeR" wrote: 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! ============================================== "spannerhappy" wrote in message ... 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 |
| All times are GMT +1. The time now is 12:42 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com