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 track projects with that I use to understand fees for different levels of input and there is an easy calculation being used of agreeing the budget for a project - so 3 hours of person x at y rate - to give an overall fee budget.
However, if I were to add in a changing rate, so there is a column which says whether rate table 1 or 2 is applicable. The fee calculation then needs to change on the basis of which rate table is referred to - so its an IF? command?
Does this make sense?
=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))=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.
