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
6	22		57	39%	39%
7	22		4	550%	160%
8	22		23	96%	96%
9	22		15	147%	147%
10	22		-5	-440%	-440%
11	22		27.5	80%	80%

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula H6 =E6/G6 I6 =MIN(E6/G6,1.6) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[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.