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)+RAND BETWEEN(1,6)+RANDBETWEEN(1,6))-(RANDBETW EEN(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)+R ANDBETWEEN(1,6)+RANDBETWEEN(1,6))-(RANDB ETWEEN(1,6)+RANDBETWEEN(1,6)+RANDBETWEEN(1,6) ),(RANDBETWEEN(1,6)+RANDBETWEEN(1,6)+RAN DBETWEEN(1,6)+RANDBETWEEN(1,6))-(RANDBET WEEN(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?
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)+RAND
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)+R
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
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...
But then I don't know about MAX...
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),(Char
This just gives the lie to those who say LJ is a useless time sink... :-)
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!
Or is there an ISNEGATIVE function? I can't remember.