?

Log in

No account? Create an account

Previous Entry | Next Entry

Excel AKICOLJ

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

( 9 comments — Leave a comment )
king_pellinor
May. 12th, 2012 08:53 pm (UTC)
Try MAX(Die_roll_formula,0)
kargicq
May. 12th, 2012 09:04 pm (UTC)
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
May. 12th, 2012 09:19 pm (UTC)
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
May. 14th, 2012 01:56 pm (UTC)
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
May. 14th, 2012 08:36 pm (UTC)
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
May. 12th, 2012 09:15 pm (UTC)
I'd also do max(x,0).

But I note that would weight 0 more strongly than it would otherwise be
philmophlegm
May. 12th, 2012 10:48 pm (UTC)
"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
May. 12th, 2012 10:10 pm (UTC)
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
May. 12th, 2012 10:47 pm (UTC)
Thanks everyone - the MAX function did exactly what I was looking for.

( 9 comments — Leave a comment )