Help with an excel formula to calculate amount per trip
Discussion
I am trying to write an excel formula for amount paid per delivery. If over 0.99 miles amount = X, if over 1.99 miles amount = x so that i can just enter mileage and the amount paid is automatically calculated. I have = IF(B4>0.99, "3", "2.5", IF(B4>1.99, "3.5", IF(B4>2.99, "4", IF(B4>3.99,"4.5,)))) but excel says there is an error with the formula. Anyone know how to correct it?
Thanks.
Thanks.
SRhysJones said:
I am trying to write an excel formula for amount paid per delivery. If over 0.99 miles amount = X, if over 1.99 miles amount = x so that i can just enter mileage and the amount paid is automatically calculated. I have = IF(B4>0.99, "3", "2.5", IF(B4>1.99, "3.5", IF(B4>2.99, "4", IF(B4>3.99,"4.5,)))) but excel says there is an error with the formula. Anyone know how to correct it?
Thanks.
IF(test statement, value if true, [value if false])Thanks.
I think you want
=IF(B4>3.99, 4.5, IF(B4>2.99, 4, IF(B4>1.99, 3.5, IF(B4>0.99, 3, 2.5))))
If there’s a lot of values then a VLOOKUP might work better
Edited by essayer on Sunday 10th May 13:14
You've built the IF statement in the wrong order as any value over 0.99 will be true for the first test & give you a result of 3 - the formula posted above works correctly. Excel doesn't carry along the chain testing, it stops after the first TRUE result.
Your particular issue is the first IF statement has too many arguments - it's saying IF B4 is greater than 0.99 THEN B4=3, if not THEN B4= 2.5 but you've then got the rest of the IF nest as a third argument.
If you do a lot of this then either learn to use VLOOKUP or at least create the mileage & rate values as a little table & refer to the relevant cells in your IF nest. Then it's much easier to update the values if required.
Your particular issue is the first IF statement has too many arguments - it's saying IF B4 is greater than 0.99 THEN B4=3, if not THEN B4= 2.5 but you've then got the rest of the IF nest as a third argument.
If you do a lot of this then either learn to use VLOOKUP or at least create the mileage & rate values as a little table & refer to the relevant cells in your IF nest. Then it's much easier to update the values if required.
>= is better than >
<= is better than <
otherwise your formula may fall over when the mileage entered is exactly on the boundary between categories.
If you're comfortable with SUMPRODUCT, put the mileage and rates into a table and use SUMPRODUCT instead. Its more robust than LOOKUPs. If not, send it to me and I'll do it for you.
<= is better than <
otherwise your formula may fall over when the mileage entered is exactly on the boundary between categories.
If you're comfortable with SUMPRODUCT, put the mileage and rates into a table and use SUMPRODUCT instead. Its more robust than LOOKUPs. If not, send it to me and I'll do it for you.
You could also look at the IFS function if you have Excel 2016 or later. It does the same as IF but IF gets a bit messy when you have a lot of conditions. IFS keeps things in pairs so it is easier to read and add more conditions:
=IFS(B4>=4,"4.5",B4>=3,"2.99",B4>=2,"3.52,...,...)
Mike...
=IFS(B4>=4,"4.5",B4>=3,"2.99",B4>=2,"3.52,...,...)
Mike...
Gassing Station | Finance | Top of Page | What's New | My Stuff