Dice Roll

diddi

Well-known Member

Joined

May 20, 2004

Messages

3,337

Office Version

1. 2010

Platform

1. Windows

• Oct 21, 2013

• #2

hi and welcome to the board. can you give a bit more detail about what you are trying to do. are you trying to create a statistical model?

 

Upvote 0

shg

MrExcel MVP

Joined

May 7, 2008

Messages

21,836

Office Version

1. 2010

Platform

1. Windows

• Oct 21, 2013

• #3

Confirmed with Ctrl+Shift+Enter:

=IF(OR(AND(A1:E1=1), AND(A1:E1=2)),"Hooray","")

 

Upvote 0

M

Monitor

New Member

Joined

Oct 20, 2013

Messages

11

• Oct 21, 2013

• #4

shg said:

Confirmed with Ctrl+Shift+Enter:

=IF(OR(AND(A1:E1=1), AND(A1:E1=2)),"Hooray","")

Click to expand...

I get a value error. I don't think it likes the A1:E1

 

Upvote 0

M

Monitor

New Member

Joined

Oct 20, 2013

Messages

11

• Oct 21, 2013

• #5

I have a spreadsheet with five columns and 100 rows. When F9 is pushed it gives each cell a new number. What I have attempted to do is when the five columns gets 1,1,1,1,1 or 2,2,2,2,2, or 3,3,3,3,3 all the way to 6,6,6,6,6. I want in cell F to show the text Hurray or something equivalent. Thanks

 

Upvote 0

M

Monitor

New Member

Joined

Oct 20, 2013

Messages

11

• Oct 21, 2013

• #6

Monitor said:

I get a value error. I don't think it likes the A1:E1

Click to expand...

I'm using Excel 2010 even though you already know that. When I press the error button to see what it doesn't like. It underlines the A1:E1 and shows a value error in its place. Thanks

 

Upvote 0

L

L. Howard

Well-known Member

Joined

Oct 16, 2012

Messages

4,514

• Oct 21, 2013

• #7

Hi Monitor, welcome to the forum.

Something like this may be adaptable to your sheet, or a place to start maybe.

B1:B5 represent the dice with the formulas in each =RANDBETWEEN(1,6)

In B7:B12:

=IF(COUNTIF($B$1:$B$5,"=1")=5,"Y","")&$B$1
=IF(COUNTIF($B$1:$B$5,"=2")=5,"Y","")&$B$1
=IF(COUNTIF($B$1:$B$5,"=3")=5,"Y","")&$B$1
=IF(COUNTIF($B$1:$B$5,"=4")=5,"Y","")&$B$1
=IF(COUNTIF($B$1:$B$5,"=5")=5,"Y","")&$B$1
=IF(COUNTIF($B$1:$B$5,"=6")=5,"Y","")&$B$1

And this code in the worksheet module.

Code:

Option Explicit

Private Sub Worksheet_Calculate()

If Range("B7").Value = "Y1" Then
    MsgBox "Yatzee in ONES"
    
  ElseIf Range("B8").Value = "Y2" Then
    MsgBox "Yatzee in TWOS"
  
  ElseIf Range("B9").Value = "Y3" Then
    MsgBox "Yatzee in THREES"
    
  ElseIf Range("B10").Value = "Y4" Then
    MsgBox "Yatzee in FOURS"
    
  ElseIf Range("B11").Value = "Y5" Then
    MsgBox "Yatzee in FIVES"
    
  ElseIf Range("B12").Value = "Y6" Then
    MsgBox "Yatzee in SIXS"
End If
 
End Sub

Regards,
Howard

 

Upvote 0

shg

MrExcel MVP

Joined

May 7, 2008

Messages

21,836

Office Version

1. 2010

Platform

1. Windows

• Oct 21, 2013

• #8

	A​	B​	C​	D​	E​	F​	G​
1​	1​	1​	1​	1​	1​	Hooray	F1: {=IF(AND(A1:E1 = TRANSPOSE(A1:E1)),"Hooray","")}
2​	2​	2​	2​	2​	2​	Hooray
3​	3​	3​	3​	3​	3​	Hooray
4​	4​	4​	4​	4​	5​

The curly braces mean that the formula MUST be confirmed with Ctrl+Shift+Enter rather than just Enter. You cannot enter the braces manually.

 

Upvote 0

D

d'Holbach

New Member

Joined

Oct 12, 2013

Messages

47

• Oct 21, 2013

• #9

Few other options:

to check 1s and 2s

Code:

=OR(COUNTIF(A1:E1,1)=5,COUNTIF(A1:E1,2)=5)

and to check all 6:

Code:

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

 

Last edited: Oct 21, 2013

Upvote 0

D

d'Holbach

New Member

Joined

Oct 12, 2013

Messages

47

• Oct 21, 2013

• #10

The last one can be simplified to:

Code:

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

entered with ctrl+shift+enter

 

Upvote 0