Max and Min Formula

shawn99michaels

New Member
Joined
May 16, 2007
Messages
38
hi All,

I was wondering if any of the excel gurus might know a trick whereby values could be limited to max and min.

My current formula is a/b and sometimes it gives me figures of 384% and I want it to be limited to 100% or 0%.

is there anyway using max and min this can be corrected ?

thanks,

Shawn
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Asala42

Well-known Member
Joined
Feb 26, 2002
Messages
2,318
=max(0,min(1,a1/b1))



[edit] I thumbs up Jason's for style
 
Last edited:

shawn99michaels

New Member
Joined
May 16, 2007
Messages
38
Sorry I don't think i've been clear what i meant was the value to be capped at 100% and not for it to drop below 0%. however if its like 64% or 35% I would like it to stay that way.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,971
Office Version
  1. 365
Platform
  1. Windows
Shawn,

Both methods should do that, are your figures true percentages? i.e. if you change the format of the cell to number does it change to a decimal value? (64% should change to 0.64)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,123
Messages
5,599,834
Members
414,341
Latest member
Mohammedsobhey

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