Forum Discussion
Solved
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...
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"
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)