Max and Min Formula

[shawn99michaels]

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

 

[jasonb75]

One way

=MEDIAN(0,1,a/b)

 

[Asala42]

=max(0,min(1,a1/b1))



[edit] I thumbs up Jason's for style

 

[shawn99michaels]

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]

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)

 

[shawn99michaels]

It does work my apologies.

thank you both for such a quick response.

thanks,

Shawn