Excel Help, .95's and .05's

[Stalker0]

I'm doing a little excel work, and I could use a little help. When using excel in dnd, there's plenty of times you want values to cap at .95 or never go below .05 (because of the autohit 20 and automiss 1 rules).

I can use "if" statements to make sure my values never exceed these boundaries, but I was wondering if there was a nice simple function out there that would cap these values for me.

 

[mmadsen]

I can use "if" statements to make sure my values never exceed these boundaries, but I was wondering if there was a nice simple function out there that would cap these values for me.

Just MAX and MIN...

 

[Stalker0]

Just MAX and MIN...

Those functions return the largest or smallest of a set of numbers. I need a function will change a value from a number higher than .95 to .95

 

[chriton227]

I'm doing a little excel work, and I could use a little help. When using excel in dnd, there's plenty of times you want values to cap at .95 or never go below .05 (because of the autohit 20 and automiss 1 rules).

I can use "if" statements to make sure my values never exceed these boundaries, but I was wondering if there was a nice simple function out there that would cap these values for me.

Max(A1, 0.05) will give you the contents of cell A1 floored at 0.05
Min(A1, 0.95) will give you the contents of cell A1 capped at 0.95
Max(Min(A1, 0.95), 0.05) will give you the contents of A1 limited to 0.05 to 0.95

 

[mmadsen]

Those functions return the largest or smallest of a set of numbers. I need a function will change a value from a number higher than .95 to .95

MIN(MAX(A1, 0.05), 0.95) will return the value in cell A1, bound by the range [0.05, 0.95].

 

[Stalker0]

Ah, now I see what you mean...thanks, that helps a lot

 

[Olaf the Stout]

Ah, now I see what you mean...thanks, that helps a lot

Of course, you have to make sure that if you are doing a random number generation and 50% of your numbers are over 0.95 then you will get a lot of 0.95 results.

Olaf the Stout

 

[maransreth]

If you install the Analysis Toolpak (from Tools>Addins), you can use the RANDBETWEEN function and set the max and min limits.

The problem with this solution is that if you send the spreadsheet to someone who doesn't have the Toolpak installed, it brings up errors until they install it.