#### bbarth

##### New Member
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
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
I'm still confused. The formula is =1/(AVERAGE(E7:E9)). Any suggestions?

#### patrickmuldoon99

##### Active Member
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

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

#### Oaktree

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

#### bbarth

##### New Member
PERFECT!!!!! Thank you so much.

#### bbarth

##### New Member
Alright!!! Thanks for the help guys.

Replies
3
Views
31
Replies
3
Views
84
Replies
4
Views
159
Replies
8
Views
304
Replies
2
Views
304

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?

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