Dice Roll

[Monitor]

I have tried many different functions to get a text response when I roll - 1,1,1,1,1 or 2,2,2,2,2 using five dice. The table has 100 rows and the numbers are random. I've tried if, Vlookup, Hlookup and or.

 

[shg]

Or =IF(COUNTIF(A1:E1,A1)<>5,"Fail","Success") confirmed normally.

 

[pgc01]

Or =IF(COUNTIF(A1:E1,A1)<>5,"Fail","Success") confirmed normally.

... or 1 microsecond quicker

=IF(COUNTIF(A1:D1,E1)<>4,"Fail","Success")

 

[pgc01]

The last one can be simplified to:

=IF(COUNTIF(A1:E1,A1:E1)<>5,"Fail","Success")

entered with ctrl+shift+enter

Hi

This formula is wrong, it returns an array. Please check.

 

[shg]

... or 1 microsecond quicker

Now I'll finally have time to take that vacation ...

All variations are faster than my suggestion in post#8.

 

[Rick Rothstein]

There are only five cells total, why not eliminate all but the IF function call (or are the concatenations less efficient than using COUNTIF)?

=IF(A1&B1&C1&D1&E1=A1&A1&A1&A1&A1,"Hooray!","")

 

[d'Holbach]

Hi

This formula is wrong, it returns an array. Please check.

That is weird as it works for me and it produces correct results. Anyway, a more elegant version was already suggested.

 

[shg]

There are only five cells total, why not eliminate all but the IF function call (or are the concatenations less efficient than using COUNTIF)?

=IF(A1&B1&C1&D1&E1=A1&A1&A1&A1&A1,"Hooray!","")

	A​	B​	C​	D​	E​	F​	G​
1​	1​		11​	1​	1​	Hooray!	F1: =IF(A1&B1&C1&D1&E1=A1&A1&A1&A1&A1,"Hooray!","")

 

[Rick Rothstein]

	A​	B​	C​	D​	E​	F​	G​
1​	1​		11​	1​	1​	Hooray!	F1: =IF(A1&B1&C1&D1&E1=A1&A1&A1&A1&A1,"Hooray!","")

<TBODY>
</TBODY>

Wouldn't the random numbers the OP is generating to represent dice rolls be limited to the numbers 1 through 6?

 

[d'Holbach]

=IF(DELTA(A1,B1),IF(DELTA(C1,D1),IF(DELTA(E1,D1),"Success","Failure"),"Failure"),"Failure")

 

[pgc01]

As a side note it's really a case to say "Hooray!"

There are 7776 permutations with repetition of 6 elements taken 5 at a time. This means that on average a row will display "Hooray!" only 1 out of 1000+ times.

I'd say it would be maybe to consider instead of one particular record displaying all the 5 numbers equal to consider if after you press F9 any of the 100 records displays the 5 numbers equal.

It would still only display "Hooray!" about 1 out of 13 times, but that's not so bad.