# Excel formula with set minimum/maximum result values



## shmoo2

Does anyone know how to get excel to return a formula result only within specified minimum and maximum values?

That is, I want Excel to calculate a series of % to hit chances, based on reference cells for to hit bonus and AC; but the values returned should have a minimum of 0.05 and a maximum of 0.95.

Thanks!


----------



## drothgery

shmoo2 said:
			
		

> Does anyone know how to get excel to return a formula result only within specified minimum and maximum values?
> 
> That is, I want Excel to calculate a series of % to hit chances, based on reference cells for to hit bonus and AC; but the values returned should have a minimum of 0.05 and a maximum of 0.95.
> 
> Thanks!




Replace A1 with the cell you want to reference in the formula below.

=MIN(MAX(0.05,A1),0.95)

Basically I get the maximum of .05 and the referenced cell, then the minimum of what came back and .95.


----------



## EricNoah

Maybe use nested IF statements?

Let's say D4 holds the generated value...

=IF(D4<0.05,0.05,IF(D4>0.95,0.95,D4))

Something like that...

edit: Bah, too slow...


----------



## shmoo2

Thanks to both of you- those solutions work for me.


----------



## drothgery

shmoo2 said:
			
		

> Thanks to both of you- those solutions work for me.




No problem.


----------



## waleeed00

Thanks to both of you- those solutions work for me.


----------



## MarkB

One thing to be aware of is that this does create a slightly biased result. By replacing any number outside the available range with the highest/lowest value, you're making those values more likely to appear than any other one value.

I don't have Excel in front of me to check, but a quick Google suggests that using the RANDBETWEEN function might work as an alternative - i.e. *=0.01*RANDBETWEEN(5,95)* will return a completely random percentile number within the specified range.


----------



## Tonguez

MarkB said:


> One thing to be aware of is that this does create a slightly biased result. By replacing any number outside the available range with the highest/lowest value, you're making those values more likely to appear than any other one value.
> 
> I don't have Excel in front of me to check, but a quick Google suggests that using the RANDBETWEEN function might work as an alternative - i.e. *=0.01*RANDBETWEEN(5,95)* will return a completely random percentile number within the specified range.




yup Rand was the first thing that came to mind too, just had to get the right formula which you appear to have done


----------

