SOLVED

# Formula based on value on another column

Copper Contributor

# Formula based on value on another column

Hello - I am trying to modify this formula to apply the calculation for all except where the value in "Job Type" column equals Officer. When the job type is Officer, the multiplication should be by 1.5 instead of 2.0. The other numbers such as 11, 12, etc represent columns, I just shortened them for the purpose of this example.

2.0*sum(11,12)+sum(14,15)/100*13+sum(14,15)/100*2.0*sum(11,12)+2.0*18+sum(13,16,17,19,20,21)

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

# Re: Formula based on value on another column

@analysta
Let's assumed that the numbers of your columns are the actual column numbers:
K=11, L=12 and so on...
Here's the formula:
=IF(A2="Officer",
1.5*SUM(K2:L2) + SUM(N2:O2)/100*M2 + SUM(N2:O2)/100*1.5*SUM(K2:L2) + 1.5*R2 + SUM(M2, P2, Q2, S2, T2, U2, V2),
2.0*SUM(K2:L2) + SUM(N2:O2)/100*M2 + SUM(N2:O2)/100*2.0*SUM(K2:L2) + 2.0*R2 + SUM(M2, P2, Q2, S2, T2, U2, V2))

it will check if cell A2 is "Officer", then the 1.5 multiplication will be applied when it's "Officer", the multiplication of 2 will be applied if it's not "Officer"

# Re: Formula based on value on another column

As variant

``````=(2 - 0.5*(JobType="Officer") )*( 18+ SUM(11,12)*(1 + SUM(14,15)/100 ) ) +
13*SUM(14,15)/100 + SUM(13,16,17,19,20,21)``````

# Re: Formula based on value on another column

Thank you! If I wanted to add an additional condition like if A2 contains "Officer" and location equals Houston. I have tried playing around with this but not getting the expected results.

# Re: Formula based on value on another column

@Rodrigo_  Thank you! If I wanted to add an additional condition like if A2 contains "Officer" and location equals "Houston". I have tried playing around with this but not getting the expected results.

# Re: Formula based on value on another column

Assumed that location/area is on column B.
=IF(AND(A2="Officer", B2="Houston"),
1.5*SUM(K2:L2) + SUM(N2:O2)/100*M2 + SUM(N2:O2)/100*1.5*SUM(K2:L2) + 1.5*R2 + SUM(M2, P2, Q2, S2, T2, U2, V2),
2.0*SUM(K2:L2) + SUM(N2:O2)/100*M2 + SUM(N2:O2)/100*2.0*SUM(K2:L2) + 2.0*R2 + SUM(M2, P2, Q2, S2, T2, U2, V2))
1 best response

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

# Re: Formula based on value on another column

@analysta
Let's assumed that the numbers of your columns are the actual column numbers:
K=11, L=12 and so on...
Here's the formula:
=IF(A2="Officer",
1.5*SUM(K2:L2) + SUM(N2:O2)/100*M2 + SUM(N2:O2)/100*1.5*SUM(K2:L2) + 1.5*R2 + SUM(M2, P2, Q2, S2, T2, U2, V2),
2.0*SUM(K2:L2) + SUM(N2:O2)/100*M2 + SUM(N2:O2)/100*2.0*SUM(K2:L2) + 2.0*R2 + SUM(M2, P2, Q2, S2, T2, U2, V2))

it will check if cell A2 is "Officer", then the 1.5 multiplication will be applied when it's "Officer", the multiplication of 2 will be applied if it's not "Officer"