Select the Correct Rate

[John Gibbons]

Good evening. I need some assistance in writing a formula that will select the correct rate based on Rate1 or Rate2 and a specific region. The file is attached. Thank you in advance.

 

[Logit]

Formula in H3 and drag down : =IF(G3="","",IF(G3=1,B3,IF(G3=2,C3)))

Formula in J3 and drag down : =IF(I3="","",SUM(H3*I3))

 

[bosco_yip]

Or these shorter ……………

H3 : =IF(G3="","",IF(G3=1,B3,C3))

J3 : =IF(I3="","",H3*I3)

All copied down.

Regards
Bosco

 

[John Gibbons]

AWESOME!!! Thank you so much to both of you.

 

[Logit]

You are welcome.

 

[John Gibbons]

Hello Again thank you the answers provided - the formula worked great. Now that I have the formula, I forgot to mention that the formula needs to take into account the randomness of the Region and then assign the correct (Rate 1 or Rate 2) based on the which rate the employee is charging - Rate 1 or Rate 2. I have updated the attached file. Appreciate your efforts in advance.

 

[bosco_yip]

Hello Again thank you the answers provided - the formula worked great. Now that I have the formula, I forgot to mention that the formula needs to take into account the randomness of the Region and then assign the correct (Rate 1 or Rate 2) based on the which rate the employee is charging - Rate 1 or Rate 2. I have updated the attached file. Appreciate your efforts in advance.

In "LaborCalcs" sheet C2, copied down :

=VLOOKUP(A2,RegionRates,B2+1,0)

Regards
Bosco

 

[John Gibbons]

Wow, that was a quick turn around. Thank you so much.