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.
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!