# Calculate output percentage based on input considering criteria

1. ## Calculate output percentage based on input considering criteria

Hello friends,

I am unable to understand the mathematics behind a logic and that is why I cannot write a formula.
I have two columns, Input and Output. Based on input, the output should be generated as per criteria below
1. If the input is equal to or below 20% then output should remain same as input
2. if the input is equal to or more than 100% then the output should be half of the input
3. If the input ranges between 20% and 100% then it should calculate proper weightage and give the output.

The cells marked in yellow are the desired output i am looking at. Please help me with a formula.

Thank you

inputoutput.png

And BTW

Thanks

4. ## Re: Calculate output percentage based on input considering criteria

I thought I attached the file. I am attaching it now

5. ## Re: Calculate output percentage based on input considering criteria

=SIGN(A2)*(MIN(ABS(A2),0.2)+MAX(0,ABS(A2)-0.2)/0.8*0.3)

6. ## Re: Calculate output percentage based on input considering criteria

Thanks @Bo_Ry
I spent some time to understand the maths behind it. I understood the logic in formula where you have considered min and max values to look but could not figure out last part *0.3
if possible can you please shed some light on it?
Thank you

EDIT : I tried with a new example from 10% 11% .... to 100%
In this example, the first evaluated value should be 10% and last should be 50%
Once I understand the reason behind the last part *0.3, I can work on other examples to clearly understand the solution.

7. ## Re: Calculate output percentage based on input considering criteria

0.3 is from
20% --> 20%
100% -> 50%

50%-20% = 30% = 0.3

8. ## Re: Calculate output percentage based on input considering criteria

Thank you for the explanation. I tried to incorporate that in another example but the last figure shows 46% instead of 50%. Could you please check the revised attachment? Thanks

9. ## Re: Calculate output percentage based on input considering criteria

=sign(a2)*(min(abs(a2),0.1)+max(0,abs(a2)-0.1)/0.9*0.4)

20%- 100% --> /0.8

10%-100% --> /0.9

10. ## Re: Calculate output percentage based on input considering criteria

Oh yes ! silly mistake from my side. I overlooked that 0.9 part in this bigger sample.
Thank you so much for the explanation
God bless !

There are currently 1 users browsing this thread. (0 members and 1 guests)