Formula to count values in each row of a spill array

TonyD1016

Board Regular
Joined
Nov 18, 2021
Messages
51
Office Version
  1. 365
Platform
  1. Windows
I've come across another issue related to my probability simulator.

I'm using the following:
Excel Formula:
=RANDARRAY($A$4,6,1,6,TRUE)
to generate a row of numbers to represent a series of dice rolls The row argument is a cell in which I type the number of series I want to simulate.

What I am looking for is a formula that will check each row and return counts of the results to a given criteria, such as all results equal to one, or greater than 1, or equal three. Preferably this formula would be some kind of sequence that automatically spills down to match the size of the test array.

So far the only thing I could think to try was =COUNTIF(INDEX(A5#,SEQUENCE(ROWS(A5#)),0),">1"), but this just returns an array of #VALUE! errors equal in size to the number of tests
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
74,446
Office Version
  1. 365
Platform
  1. Windows
Can you post some sample data & expected results. It's not very clear what you are trying to do.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 

TonyD1016

Board Regular
Joined
Nov 18, 2021
Messages
51
Office Version
  1. 365
Platform
  1. Windows
Oh that's handy.
Here is a sample of what I am working with:
prop calc.xlsx
ABCDEFG
1
2
3
45Tests
52314662
63332361
75524463
83225152
93562342
Sheet2
Cell Formulas
RangeFormula
A5:F9A5=RANDARRAY($A$4,6,1,6,TRUE)
G5:G9G5=COUNTIF(A5:F5,">=5")
Dynamic array formulas.


The above generates a series of random results from 1 to 6, with each row counting as a "test." At the end of each row I have a COUNTIF returning the number of times a result of 5 or 6 appears in each row.
What I'm trying to do is come up with a formula that will automatically go through each row the array produces.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
74,446
Office Version
  1. 365
Platform
  1. Windows
Ok, thanks for that.
How about
Excel Formula:
=MMULT(--(A5#>=5),SEQUENCE(COLUMNS(A5#),,,0))
 
Solution

TonyD1016

Board Regular
Joined
Nov 18, 2021
Messages
51
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Ok, thanks for that.
How about
Excel Formula:
=MMULT(--(A5#>=5),SEQUENCE(COLUMNS(A5#),,,0))
That works perfect.
I had come across MMULT before but only in the context of counting specific values. What purposes does the "--" at the beginning serve?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
74,446
Office Version
  1. 365
Platform
  1. Windows
It;s called a Double Unary & it coerces the True/False results from (A5#>=5) into 1s & 0s
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
74,446
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,299
Messages
5,853,152
Members
431,549
Latest member
NnAa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top