How to find a value in a table?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi everyone!

I have some problems with finding a value in a table. I have a Poisson table (i.e. probablities that x will be between zero and k for different values of m, which is the mean value). Now, I want to look up a specific column (if I for example have a Poisson distribution with mean 10, I want to go to that column) and then find a specific probability in that column. Then I would like to get the value of the first column (which is the value of k) in that corresponding row. How can I solve this?

The poisson table looks like the following:
k\m 0.1 0.2 0.3 0.4 ............. 15.0
0 0.90 0.82 0.74 0.67 ............. 0.00
1 0,99 ...............................................
2 ........................................................
3 .........................................................
4 .........................................................
.. .........................................................
.. .........................................................
.. ................................................... 0.99
35 1.00

I will use this for calculating safety stocks and I therefore need to find the number of items that should be kept in stock so that the probability that stock out won't occur can be set to a certain probability.

Thanks!
 
Elisabeth,

Assuming your Poisson Table starts in cell A1, your table headings are
in row 1, and your first column is column A, then

=INDEX(A:A,MATCH(Specific Probability,OFFSET(A1,0,MATCH(Mean to look
up,1:1,FALSE)-1,100,1),FALSE))

will return the value you are looking for. Of course, change Specific
Probability and Mean to look up to either numbers or cell references.

Change the second FALSE to TRUE if your specific probability may not
be present in the table - e.g., you are looking for .5345 but your
table has .53 and .54. You may also want to add +1 to the second
parameter of the INDEX function, to return the next higher value from
the first column, since the TRUE parameter means the match is on the
lower value and not the higher - an extra degree of safety.

HTH,
Bernie
MS Excel MVP

Elisabeth said:
Hi everyone!

I have some problems with finding a value in a table. I have a
Click to expand...
Poisson table (i.e. probablities that x will be between zero and k for
different values of m, which is the mean value). Now, I want to look
up a specific column (if I for example have a Poisson distribution
with mean 10, I want to go to that column) and then find a specific
probability in that column. Then I would like to get the value of the
first column (which is the value of k) in that corresponding row. How
can I solve this?
The poisson table looks like the following:
k\m 0.1 0.2 0.3 0.4 ............. 15.0
0 0.90 0.82 0.74 0.67 ............. 0.00
1 0,99 ...............................................
2 ........................................................
3 .........................................................
4 .........................................................
. .........................................................
. .........................................................
. ................................................... 0.99
35 1.00

I will use this for calculating safety stocks and I therefore need
Click to expand...
to find the number of items that should be kept in stock so that the
probability that stock out won't occur can be set to a certain
probability.
 
Back
Top