Cap Percentages-Please Help

bbarth

New Member
Joined
Oct 19, 2006
Messages
5
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%?
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

DominicB

Well-known Member
Joined
Oct 3, 2005
Messages
1,569
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

New Member
Joined
Oct 19, 2006
Messages
5
I'm still confused. The formula is =1/(AVERAGE(E7:E9)). Any suggestions?
 

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345
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

New Member
Joined
Oct 19, 2006
Messages
5

ADVERTISEMENT

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

Forum statistics

Threads
1,137,333
Messages
5,680,881
Members
419,937
Latest member
Talic

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top