Forum Discussion
Solved
Choosing hourly rate tables in Excel to calculate fee budgets
Hopefully someone can help with this challenge! I have a client that I work for which we have two sets of hourly rates depending on the location of the work. I have an excel sheet that I trac...
=IF(C2=1,B2*VLOOKUP(B2,$F$3:$G$10,2,FALSE),B2*VLOOKUP(B2,$I$3:$J$10,2,FALSE))
This formula could return the intended result in the sample sheet.
=IF(C2=1,B2*VLOOKUP(B2,$F$3:$G$10,2,FALSE),B2*VLOOKUP(B2,$I$3:$J$10,2,FALSE))
This formula could return the intended result in the sample sheet.
Amazing, thats really helpful.
I've updated it so that it is the below, so E3 is which rate table to use, so in this example, with E3 showing that you refer to rate table 1, the fees are worked out below.
=IF(E3=1,(F3*H14)+(G3*H15))
However, if there are 2 rate tables, so you can choose 1 or 2 - i can't seem to work out how you have a second option - i thought it would be the below, but that doesn't seem to work (comes up with #value error
=IF(E3=1,(F3*H14)+(G3*H15)),IF(E3=2,(F3*H18)+(G3*H19))
I've updated it so that it is the below, so E3 is which rate table to use, so in this example, with E3 showing that you refer to rate table 1, the fees are worked out below.
=IF(E3=1,(F3*H14)+(G3*H15))
However, if there are 2 rate tables, so you can choose 1 or 2 - i can't seem to work out how you have a second option - i thought it would be the below, but that doesn't seem to work (comes up with #value error
=IF(E3=1,(F3*H14)+(G3*H15)),IF(E3=2,(F3*H18)+(G3*H19))
=IF(E3=1,(F3*H14)+(G3*H15),IF(E3=2,(F3*H18)+(G3*H19)))
This works in my sheet. I've made a small change with one closing bracket.
- fabulous, thanks!
What a fab community!
delete the IF(E3=2, in the second half.
the IF is dictating that if E3 =1 then do a certain thing, but if E3 is any other value at all then do the other thing.
hence no need to state the IF as a second value as since E3 didn’t equal 1 then the other thing happens.
if you had three or more choices then either nested IF, or IFS, or the SWITCH or CHOOSE functions could be used, but the current choice of a one or a two doesn’t need those.
Mike
