Cap Percentages-Please Help

[bbarth]

I'm working on a spreadsheet that calculates percentage. My issue is that in some cases the percentage exceeds 100%, which is ok, but I need it to cap at 100%. Is there anyway to cap a column or cell formula at a max of 100%?

 

[DominicB]

Good afternoon bbarth

It depends on what your formula is, but if you are using A1/B1 then this formula would cap the formula at 100% :
=IF(A1/B1>1,1,A1/B1)

This also depends on the cell containing the formula being in %age format.

HTH

DominicB

 

[bbarth]

I'm still confused. The formula is =1/(AVERAGE(E7:E9)). Any suggestions?

 

[patrickmuldoon99]

Try,

=if(1/(AVERAGE(E7:E9)) > 1,"1",1/(AVERAGE(E7:E9)))

I'm assuming you have the cell formatted as a percentage?

Patrick

 

[bbarth]

yes sir, let me give this a shot. thanks for the help!!

 

[Oaktree]

Or, =MIN(1/AVERAGE(E7:E9),1)

 

[bbarth]

PERFECT!!!!! Thank you so much.

 

[bbarth]

Alright!!! Thanks for the help guys.