Find closest value in a Poisson distribution table-CodePudding

I am trying to find the closest value in this Poisson Distribution table to help create some variation in my NHL game simulator. Right now I have the minimum value set to =(MAX(H4:R14)/1.25) and the max as MAX(H4:R14). My rand (random) value is set to =RAND()*($V$4-$U$4) $U$4. My question is, is how do you find the closest value in the table when compared to the random value? Returning the closest match (percentage) is ideal and the table's values will change whenever the teams change. Eventually, I am trying to return the respective column and row values (goals for each team), but this is step 1 and haven't been able to figure it out yet - even with index and match. Feel like it should be fairly straightforward...

CodePudding user response：

You could create a 2nd table where you calculate the absolute or squared difference between your Poisson table values and the random value.

Then you can simply check for the smallest value in the 2nd table.

In your example, the 2nd table of differences could be created with the formula

=ABS($H$4:$R$14-$U$6)

Let's assume that this 2nd table is just below the 1st table, i.e. in H16:R26 Then the smallest value in this 2nd table is obtained by

=SMALL($H$16:$R$26,1)

from there you can get the row number through

=SUM(($H$16:$R$26=SMALL($H$16:$R$26,1))*($G$4:$G$14))

and the column number through

=SUM(($H$16:$R$26=SMALL($H$16:$R$26,1))*($H$3:$R$3))

Then, to find the closest match in the original table, you can use the INDEX() function and refer to the row and column calculated in the previous step.

You can of course skip all the intermediary steps and write everything into a single formula:

=LET(
    table,$H$4:$R$14,
    diff,ABS(table-$U$6),
    closest,SMALL(diff,1),
    closest_row,SUM((diff=closest)*SEQUENCE(ROWS(table))),
    closest_col,SUM((diff=closest)*SEQUENCE(1,COLUMNS(table))),
    INDEX(table,closest_row,closest_col)
)

Which looks like this:

Note: the last formula uses dynamic arrays and functions only available Excel O365.

CodePudding user response：

You may benefit from SUMPRODUCT and array formula to get closes match and goals values in rows and section. Just as example:

The upper table would be your data. The bottom table is just to check it it works, you can delete. This bottom table is just an absolute value of cell minus your rand. The minimun one is highlighted in green and that's the closest match. Notice how the formulas get the position of that match and the goals values:

Formula to get ROW VALUE:

=SUMPRODUCT(--(ABS(H4:R14-U6)=MIN(ABS(H4:R14-U6)))*G4:G14)

Formula to get COLUMN VALUE:

=SUMPRODUCT(--(ABS(H4:R14-U6)=MIN(ABS(H4:R14-U6)))*H3:R3)

Both of this formulas are array formulas, so to introduce them yo must press CTRL ENTER SHIFT or they won't work!

To get the closest match is just INDEX classic formula (we add 1 due to goals indexed as 0,1,2...):

=INDEX(H4:R14,U9 1,V9 1)

See it working!:

I've uploaded the file in case you want to check the formulas: https://docs.google.com/spreadsheets/d/1wV8bn6B-jZ4jCxAonGdWFMluAEybzwkA/edit?usp=share_link&ouid=114417674018837700466&rtpof=true&sd=true

Please, notice this will work properly as far as there is a single closest match. If by any case two cells are both closest match, formula will return incorrect result.

And remember, as I said before, second table is just to explain the solution, you can delete it and everything will work.