Formula that does not exceed 160%

[jswillcox]

I am trying to create a formula that does not exceed 160% and 40%

I am using cells E6 (Numerator) and G6 (Denominator). I would like E6/G6 to = 80% and it to not exceed 160%. I plan on using the percentage "number" option to add to the aesthetic appeal.

In addition I would like to another that is almost the same only E9 (Numerator) and G9 (Denominator) when dived E9/G9 to = 20% and not exceed 40%.

Thank you for your help!

 

[Eric W]

Maybe:

=MIN(E6/G6,1.6)

=MIN(E9/G9,.4)

 

[jswillcox]

That is it! Thank you!

 

[jswillcox]

Eric W,

That solution took care of part of my problem but not all of it. I do want the limit to be set at 160% and 40% just like your solution solves. I also want when you dived E6/G6 it = .8 or when you dived E9/G9 it = .2

 

[Eric W]

I'm afraid I don't understand your question. Below is a set of test values with the formula. Which of these, if any, are not returning the result you desire?
Excel 2010

	E	F	G	H	I

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]6[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]39%[/TD]
[TD="align: right"]39%[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]550%[/TD]
[TD="align: right"]160%[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]96%[/TD]
[TD="align: right"]96%[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]147%[/TD]
[TD="align: right"]147%[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-440%[/TD]
[TD="align: right"]-440%[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"]27.5[/TD]
[TD="align: right"]80%[/TD]
[TD="align: right"]80%[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H6[/TH]
[TD="align: left"]=E6/G6[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]I6[/TH]
[TD="align: left"]=MIN(E6/G6,1.6)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[jswillcox]

Eric W,

I appreciate all your help. I apologize I was not explaining it will but I was able to manipulate your first equation to accomplish my desired results

=MIN(E6/G6,2)*.8
and
=MIN(E6/G6,2)*.2

You were very helpful!

 

[Eric W]

I'm glad you got it sorted out.