Calculating probability with Excel?

Oops, there's still something I haven't understood :)
Well, in my formula, it gives you just one value because it sums the probabilities of events 1 to 50
You need the detail of the probabilities by event or by several sets of events?
Do you want the list of events whose probability in your table is less than 50%?

I don't think I'm visualizing it well :)

PS: I divide by the number of numerical values (NB) to get a probability; otherwise, I only have the number of events.

PPS: FREQUENCY allows you to retrieve the number of events (only numerical values) according to intervals defined in another list.

If you want, for example, the probability of 1, 2, and 3 (beware, only if you have whole values; otherwise, you have to be careful with the intervals, it complicates things)
you need a column, for example, the range H2:H6 with the values
0
1
2
3
4

then you select the range (for example) I2:I7, type =FREQUENCY(A2:G198;H2:H6)/NB(A2:G192)
validate with Ctrl+Shift+Enter
and there you have:
I2 = probability of events in ]-infinity, 0]
I3 = probability of events in ]0, 1]
I4 = probability of events in ]1, 2]
I5 = probability of events in ]2, 3]
I6 = probability of events in ]3, 4]
I7 = probability of events in ]4, +infinity[

Ok, I think I understand the thing better: actually, you don't need a column that defines the probability of each event...
You want the number of values between [1-50] divided by the total number of values.

=COUNT(IF(A2:G198<=50, IF(A2:G198>=1, A2:G198, ""), ""))/COUNT(A2:G198)
by validating with Ctrl+Shift+Enter (array function)

Let me know if that’s correct...

There might be a simpler function: FREQUENCY, maybe :)

Hi Jimmy,

Glad you appreciate the help, even if sometimes I'm a bit off the mark :)
I’m not really sure what a forecast result is, but if you want the most common stock values, I found on this site ( http://xlbysteph.free.fr/aideinformatique/action_denombrement.htm ) something that looks like what we're looking for:

I tweaked it a bit to work better with the empty cells from A2:G5

We start with I2, for example:

I2 contains =MODE(A2;G198)

I3 contains =MODE(IF(IF($A$2:$G$198="";"";COUNTIF(I$2:I2;$A$2:$G$198))=0;$A$2:$G$198;""))
Confirm with Ctrl+Shift+Enter (array function)

Then just select I3 and drag the selection down to get the next n most frequent values from A2:G198

WARNING: The MODE function returns the most repetitive value from a range, but does not work for values that appear only once... so the list skips values that only exist once....

-----------------------------

Next, if you want to know how many times the stock is at a certain value, you need to put in the next column:

J2 contains =COUNTIF($A$2:$C$5;I2)
then extend the formula the same way as for I3

-----------------------------

PS: The principle for I3 is to take A2:G198, remove the values of I2 (or from I2:I4 if we're in I5), spaces also cause problems, hence the additional IF statements, and then do a MODE on it to get the most repetitive value.

PPS: We can somewhat solve the problem of unique values
(but it elongates the formula a bit and raises other issues...) with this formula:
I3 contains =IF(ISNA(MODE(IF(IF($A$2:$G$198="";"";COUNTIF(I$2:I2;$A$2:$G$198))=0;$A$2:$G$198;"")) );
MAX(IF(IF($A$2:$G$198="";"";COUNTIF(I$2:I2;$A$2:$G$198))=0;$A$2:$G$198;"")) ;
MODE(IF(IF($A$2:$G$198="";"";COUNTIF(I$2:I2;$A$2:$G$198))=0;$A$2:$G$198;"")) )
Ctrl+Shift+Enter

I hope you find what you're looking for in this wall of text :)
See you soon

Hello Jimy,

Okay, I have a better understanding of your needs.
You need the forecast() function, knowing that we consider the progression as a straight line.
So you need 1 column of dates (A) and 1 column of data (B). You are only interested in the stock, not the price.
If you have 6 columns of different data representing the same item, you need to put them in the same column. If they are 6 different items, you need to calculate for each one.
- your dates in A1:A11
- your data in B1:B11
- in D1, enter 01/01/08
- in D2, enter =D1+90 (to have a forecast every 90 days)
- select D2, click on the small square at the bottom right, and without releasing, drag the mouse down until you have enough dates.
- in E1, enter =ROUND(FORECAST(D1;$B$1:$B$11;$A$1:$A$11);0) and there you go, your numbers...

What I had you enter in columns D and E can be input under your stocks in A and B, which will allow you to create a chart that is more visual.
If your progression is not a straight line but a curve that rises increasingly fast, or on the contrary flattens out, it would need to be done differently but it's more complicated.

Eric

Hello Eric :)
Wow! That was so simple! I can't believe it :-P and all those formulas I was stressing over! :o
But what do the data in B1:B11 correspond to? Is it the stock history? Because it is more substantial: (E4:I198) just that!
And for (A1:A11), are those the dates of the history? Or future dates?
Thank you so much, ha :))) I'm happy
Jimmy

Hello Jimmy,

I don't quite understand what you want to do:
E2:E192 is the list of your events (in numerical values?)
F2:F198 the associated probabilities, what values are you using?
G2:G198 the lower bound or the event sought?
1:50 what does this value correspond to?

Can you give an example of your columns?

Yes, the list of events is indeed E2:E198, but what do we do when we have several columns?
F2:F198 is a second column of events, in short, there are 7 columns like that, 5 up to "I".
The lower bound is 1 and the upper bound is 50 (that’s what (1:50) refers to).
Thank you for your reply :) this site is really nice :))
Jimmy

Sorry for the typo! The "5" just before "until I" shouldn't be there, it was just a typo.
Sorry...
I downloaded a tutorial in "pdf" format but it doesn't help much and is very confusing, plus it's for Excel 2003 lol
Jimmy

No, it's not working, it no longer shows me an error but: 1 while it should give me a series of numbers. :s
Is there a way to get the different results in several cells? Like in about twenty cells for example...
By the way, I don't understand why "divide by the total number of values"!
I'm still looking on my side :) ..... Note, I've already progressed a bit since the beginning :))
Frequency? It would give me the numbers that appear the most often, right?
Jim
P.S. What is a circular reference?

Hello, Baribari
I've been working on it all weekend and there’s a formula that works, but I can't seem to contain the lower and upper limits of my results (between 1 and 50)
either it gives me a negative value or a value above 50 :s
Here’s my formula: =COUNTIF(A2:G198;H2:H6)-(H2:H6<=50;IF(H2:H6>=1;)))
It's strange!? logically it shouldn't exceed 50 or fall below zero!?
Thanks for your help (or any other help is welcome)
Bye
Jimmy

Hello Jimmy,

Sorry for the delayed response, but I took a bit of an extended weekend :)

About the FREQUENCY formula, the values 1 2 3 4 define the intervals for which the FREQUENCY function will calculate the number of values.

But your last post leaves me a bit puzzled: you explain to me that your result must necessarily be positive (I agree with that) and less than or equal to 50, but the range A2:G198 contains 197*7=1379 cells, so the maximum number of cells corresponding to your criteria is 1379.

So I'm wondering if you're actually looking to get a list of the values that appear most frequently in the range A2:G198, is that right?

Hello Bari.
Yes, I'm looking for this kind of information, given that I can't get the probability formula to work for predicting whether a certain number will come up again. Yes, the range A2:G198 indeed contains 1379 cells of stock history (all whole numbers) which I would like; what I want is to have a forecast result in about 15 cells.
Whether it's probability or forecast, I can't seem to make either work :(
For the values 1 2 3 4 thanks for the information, I understand better now, your help is precious in any case :))
Thank you very much
Jimmy

Hello Bari
The first formula (starting from the top) works but I only get a single value "1"?
However, the second one, the one in cell I2 =MODE(A2;G198) returns #Value
Phew, it’s complex :p
And finally, the last formula: =IF(ISNA(MODE(IF(IF($A$2:$G$198="";"";COUNTIF(I$2:I2;$A$2:$G$198))=0;$A$2:$G$198; ""))) ;
MAX(IF(IF($A$2:$G$198="";"";COUNTIF(I$2:I2;$A$2:$G$198))=0;$A$2:$G$198; "")) ;
MODE(IF(IF($A$2:$G$198="";"";COUNTIF(I$2:I2;$A$2:$G$198))=0;$A$2:$G$198; "")))
Ctrl+Shift+Enter
Excel does not accept it, it says there is an error!? When I click "OK" on this last message the cursor jumps to:
$;][&quot ( just before the &quot)
To indicate where the error is...
Given the length of the formula, I really can't correct anything (ouch ouch ouch) I'm already confused at 15,000 just looking at it :p lol
Another question, why all these "$" signs and what functions do they have
Thanks again Phew!
Jimmy

Hello Bari.
I've tried to tweak the first formula to see what it would give, but it always gives me the same result: 1 or 50 no matter what I do, the result is invariant. Let me explain the process;
I'm changing A2 to A3 or 4 etc. to see if it predicts approximately the line above... without success of course.
For the second formula; =IF(ISNA(MODE.... nothing works, it doesn't function because Excel keeps rejecting it.
I hope I'm not bothering you too much :$ But thanks anyway
Bye and have a good weekend
Jimmy

Hello Jimmy,
Don’t worry, you’re not bothering me ;)
I’m just rarely around on weekends

You’re right: the part of the formula is actually MAX(IF(IF($A$2:$G$198="","",COUNTIF(I$2:I2,$A$2:$G$198))=0,$A$2:$G$198, ""))
it should work (tested it on my end). (don’t forget to validate with

The dollar signs are used to "anchor" (not sure if that’s the right term) the coordinates: for the rest of the values, we will extend the formula downwards (by pulling it from the bottom right corner) and the coordinates of the cells in the formula will change automatically in the new cells to maintain the same relative position to the new cell, the dollar signs indicate to prevent this automatic renumbering.

That said, I also find that these formulas are becoming a bit tedious :(

I think that if we want to keep it understandable, we will need to resort to macros or Visual Basic for Applications (VBA)
I will think of a way to do it ..

See you soon

Hello Bari.
No, I don’t understand, it doesn’t work :s
I have properly inserted the formula: MAX(IF(IF($A$2:$G$198="","",COUNTIF(I$2:I2,$A$2:$G$198))=0,$A$2:$G$198, "")) )
between =IF(ISNA(MODE(IF(IF($A$2:$G$198="","",COUNTIF(I$2:I2,$A$2:$G$198))=0,$A$2:$G$198, "")) ) ;
And: MODE(IF(IF($A$2:$G$198="","",COUNTIF(I$2:I2,$A$2:$G$198))=0,$A$2:$G$198, "")) )
But it keeps saying there’s an error!?
It places the cursor here: )) ][ ) MODE(IF....
I put a ; then " finally I tried everything but nothing works or should I delete the rest of the formula?
I really don’t know what to do, :( or maybe I misunderstood what you sent me as instructions, should I only keep this formula? but again the result remains 50 without changing :s
ppffff :( what have I gotten myself into :(
anyway, thank you for your patience.
Jim

Hello,

To analyze a list of values and give you an idea of how they are distributed, you have the mean and the standard deviation.
You know the mean, I suppose. The standard deviation gives you an idea of their dispersion, meaning whether few or many values deviate from the mean.
For example, with a mean of 60, if you have a standard deviation of 3, it means that roughly 75% of the values are between 57 and 63. Your values are therefore quite clustered around this mean. You can associate the minimum and maximum values for a better view of your data.
Still with a mean of 60, a standard deviation of 15 means that 75% of your values are between 45 and 75, so much more dispersed. If this is for stock, you would need to increase the stock to avoid a supply disruption.
So under each column, I would put:
=AVERAGE(A1:A198)
=STDEV(A1:A198)
and optionally:
=MIN(A1:A198)
=MAX(A1:A198)

You also have the function PERCENTILE(A1:A198;x) which can be useful. For example, PERCENTILE(A1:A198,0.9) returns the mean of 90% of the values, excluding the most extreme 10% (the explanation is approximate here).
Now all these functions will not return whole numbers; it’s up to you to round them.
Maybe this can clarify your thinking.
Eric

Hello everyone.
Thank you Eric for your help, but I'm not looking for a stock value; I'm looking for Physical Stock Forecast, so the Percentile formula doesn't help me much.
That's why I'm interested in the forecast or Probability formula, which I can't seem to get to work :(
Thanks everyone
Jimmy

Good evening Jimy,

I had understood that it was for stock forecasting.
I'll elaborate on what I had in mind, but keep in mind that this is not to convince you, just to give you an idea.
Especially since I have no pretensions in statistics since it was far from being my favorite part of math :-s

First of all, I don't think Probability() will be of any help for your problem.
Forecast() is based on linear regression and it's interesting if your line has a slope, so if you're in a growth phase. Is that the case?

If not, if you're at a steady pace, I think you should use Average(), weighted according to certain criteria.
If you just take the average, it is certain that you will have stockouts.
If you take the average plus the standard deviation, you will largely reduce these stockouts. And for sensitive items, you increase it by 2 or even 3 standard deviations (if it's not too high).
Obviously, your cost constraints come into play here. If the item is very expensive (or very bulky and the calculated stock forces you to build a new warehouse), it's clear that this is not realistic.
Another thing you can do if it’s seasonal is to prioritize the Average() calculated over a similar period. For example, take 3 times the average of last winter + the average() of the last month and divide by 4.
So I would make a first calculation with Average() + 1 standard deviation as a starting point (a bit less if it's expensive), and then it’s your experience (the famous intuition that lets you know and not know why) that would allow you to refine it.

The role of Percentile() in this...
Imagine your stock history with only 4 values. 30, 28, 1 (train strike and you weren't delivered) and 33.
Average = 23 and standard deviation = 14.7!!!
=PERCENTILE(A1:A4,0.03) returns 3.52
This means that if you want to exclude the lowest 3% of values (to eliminate atypical values), you calculate the averages with values greater than 3.52 (thus starting from 4) and your average goes from 23 to 30.3, which is more realistic. Meanwhile, your standard deviation, which was 14.7 (very dispersed values), goes down to 1.5.
And you do the same for the high values with =PERCENTILE(A1:A4,0.97) and you eliminate the values greater than the returned result.
This is just an example; I'm not sure it will help you move forward, but it might open up other ways to approach the problem.
Now, for implementation, you will have help here, but it would be good for you to have an idea of how you would like to do it, the parameters to consider, etc.
And you have the advantage of apparently having a good history, which can allow you to test the quality of your model and refine it.

Good luck
Eric