Excel AKICOLJ

[philmophlegm]

I want a function that returns a value equal to (x six-sided dice minus y six-sided dice) but which returns the answer '0' if the formula would otherwise result in a negative answer.

Or, to put it in role-playing terms, something like "4d6-3d6, but show any negatives as zero".

Doing the 4d6-3d6 bit is easy:
=(RANDBETWEEN(1,6)+RANDBETWEEN(1,6)+RANDBETWEEN(1,6)+RANDBETWEEN(1,6))-(RANDBETWEEN(1,6)+RANDBETWEEN(1,6)+RANDBETWEEN(1,6))

I had thought of then putting this inside an IF function, so that if the formula above was >=0, it showed the formula above, but if it was <0, it showed 0:

=IF((RANDBETWEEN(1,6)+RANDBETWEEN(1,6)+RANDBETWEEN(1,6)+RANDBETWEEN(1,6))-(RANDBETWEEN(1,6)+RANDBETWEEN(1,6)+RANDBETWEEN(1,6)),(RANDBETWEEN(1,6)+RANDBETWEEN(1,6)+RANDBETWEEN(1,6)+RANDBETWEEN(1,6))-(RANDBETWEEN(1,6)+RANDBETWEEN(1,6)+RANDBETWEEN(1,6)),0)

Unfortunately, that second function doesn't work. The reason it doesn't work is that it is recalculating the 4d6-3d6 part. That means that if the original 4d6-3d6 is positive, the formula doesn't show that positive number. Instead it rolls 4d6-3d6 again and shows the answer to that.

Any ideas?

Comments

[king-pellinor.livejournal.com]

Try MAX(Die_roll_formula,0)

[kargicq.livejournal.com]

Could you do something clever with =MAX(blahblahblah, 0)?

Or dump the dice result in a different cell, and refer to the value of that cell? Not sure whether that would trigger a recalculation.

I don't actually have excel on this machine (!!) to try it...

[skordh.livejournal.com]

Yeah I'd do a different cell. Couldn't you have the formula in one cell, then in another cell if first cell < 0 then 0, otherwise first cell?

But then I don't know about MAX...

[king-pellinor.livejournal.com]

MAX just says "show the highest of these: X,Y,Z..."

MIN is the opposite: "show the lowest of these: A,B,C..."

They're very handy and I use them a lot, though I sometimes get a little confused as MIN(FORMULA,X) puts a cap on a calculation and MAX(FORMULA,X) puts a floor on it, and for some reason MIN giving the upper limit and MAX the lower one seems wrong to me :-)

Occasionally you want to MIN-MAX, by working out what the cap on something should be where the cap is at least X but may be higher. Take, for example, the recent proposal that:

Tax_relief=MIN(MAX(Income/4,50000),(Charitable_contributions+Trading_losses))

[skordh.livejournal.com]

Thanks - very useful - and nicely exemplified. I must give those a try.

This just gives the lie to those who say LJ is a useless time sink... :-)

[beckyc.livejournal.com]

I'd also do max(x,0).

But I note that would weight 0 more strongly than it would otherwise be

[philmophlegm.livejournal.com]

"But I note that would weight 0 more strongly than it would otherwise be"



Appreciated, but not a problem in this context. MAX worked, so thanks!

[cheekbones3.livejournal.com]

Maybe split it into two cells. Have your formula as above, but then use a second step to check if it's negative or not using a simple IF statement.

Or is there an ISNEGATIVE function? I can't remember.

[philmophlegm.livejournal.com]

Thanks everyone - the MAX function did exactly what I was looking for.