SOLVED

Salary cut calculation

%3CLINGO-SUB%20id%3D%22lingo-sub-1256917%22%20slang%3D%22en-US%22%3ESalary%20cut%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1256917%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHi%2C%20how%20do%20I%20calculate%20salary%20cut%20by%2030%20%25%20only%20for%20the%20salary%20value%20which%20for%20example%20is%20USD%20210.000%3F%20The%20cut%20is%20to%20be%20calculated%20only%20for%20amount%20equal%20or%20exceeding%20the%20USD%20200.000%20value%2C%20not%20for%20the%20whole%20salary.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1256917%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Occasional Contributor

Hi, how do I calculate salary cut by 30 % only for the salary value which for example is USD 210.000? The cut is to be calculated only for amount equal or exceeding the USD 200.000 value, not for the whole salary.

11 Replies
Highlighted

@Dalia_Dargyte 

Perhaps

=A1-MAX(0,A1-200000)*0.3

if salary is in A1 

Highlighted
Thanks, how do I calculate 0,A1?
Highlighted

@Dalia_Dargyte 

Afraid I din't catch the question.

 

Please check how this

image.png

in attached file

Highlighted

@Dalia_Dargyte 

 

Hello, a simple IF formula will do the job. See the caption below

Abiola1_0-1585264851011.png

 

Highlighted

@Sergei Baklan thank you for your answer once again! I am still a bit confused because of this. 

What will be salary cut for 200.000 USD salary? Salary cut is applied to amount equal or higher than 200.000 USD. 

If I understand it correct for 175.000 USD the cut would be calculated (25.000 USD*20%), or as you write:

Dalia_Dargyte_1-1585307072292.png

 

 But what do I do when the salary is equal to threshold? 

Dalia_Dargyte_0-1585306367486.png

I really appreciate your answer! Thank you once again!

Highlighted

@Abiola1 thank you for answer.

It looks like this reduction is for the whole initial salary, not only the amount exceeding the threashold of 200. Maybe I was not clear enough: the salary reduction is not for the whole salary, but only for the amount equal or exceeding 200. For example for 250 salary the reduction is applied only for 50 USD.

 

But what happens to the  salary  which is exactly 200.000 USD? How the cut should be calculated in this case?

Dalia_Dargyte_0-1585307694955.png

 

Highlighted

@Dalia_Dargyte 

Sorry, I'm not familiar with these rules, could you please give entire picture? Formula depends on this. First question was about 30% from the top of 200K. Now it looks like 20% from the top of 150K till 200K PLUS 20% from above 200K. Does that mean 0% from any sum below 150K?

Highlighted
Sorry, for not explaining the whole picture at once. It is 15% from the top of 100K, and 10 % from the top of 50K.
Thank you so much for your time!
Best regards,
Dalia
Highlighted
Best Response confirmed by Dalia_Dargyte (Occasional Contributor)
Solution

@Dalia_Dargyte 

Dalia,

 

It's better to create helper range with parameters

image.png

as in the right site of this screenshot. Actually it could be at any place within workbook. Formula in B1 could be

=SUMPRODUCT((($F$2:$F$6-$E$2:$E$6)*($F$2:$F$6-A1<0)+(A1>$E$2:$E$6)*(A1<$F$2:$F$6)*(A1-$E$2:$E$6))*$G$2:$G$6)

drag it down.

Highlighted

@Sergei Baklan thank you so much for your help! It worked great:) 

Highlighted

@Dalia_Dargyte , you are welcome