SOLVED

# Choosing hourly rate tables in Excel to calculate fee budgets

Copper Contributor

# 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?

5 Replies
best response confirmed by prcrispxl185 (Copper Contributor)
Solution

# Re: Choosing hourly rate tables in Excel to calculate fee budgets

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

# Re: Choosing hourly rate tables in Excel to calculate fee budgets

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))

# Re: Choosing hourly rate tables in Excel to calculate fee budgets

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

# Re: Choosing hourly rate tables in Excel to calculate fee budgets

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

# Re: Choosing hourly rate tables in Excel to calculate fee budgets

fabulous, thanks!
What a fab community!
1 best response

Accepted Solutions
best response confirmed by prcrispxl185 (Copper Contributor)
Solution

# Re: Choosing hourly rate tables in Excel to calculate fee budgets

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