Excel formula with set minimum/maximum result values

S

shmoo2

First Post

• Apr 29, 2007

• #1

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!

 

log in or register to remove this ad

D

drothgery

First Post

• Apr 29, 2007

• #2

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!

Click to expand...

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

Adventurer

• Apr 29, 2007

• #3

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...

 

S

shmoo2

First Post

• Apr 30, 2007

• #4

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

 

D

drothgery

First Post

• Apr 30, 2007

• #5

shmoo2 said:

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

Click to expand...

No problem.

 

W

waleeed00

First Post

• Mar 14, 2016

• #6

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

 

MarkB

Legend

• Mar 14, 2016

• #7

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

A suffusion of yellow

• Mar 14, 2016

• #8

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.

Click to expand...

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