Calculating probability with Excel? Solved

Question

Hello,
I would like to create a sheet with Excel 2007
In 7 columns and about 198 rows and generate a list of probabilities with 5 or 7 cells
But there you go, it tells me that my formula contains errors or sometimes it gives me "error #VALUE" or even "error #NUM"
Anyway! no way to get it sorted out! Ossssecours! :p
Here is (for your information) the formula entered:
=PROB (E2:E198; F2:F198; G2:G198; 1:50)
Thank you for your help
Jimmy

Configuration: Windows Xp Internet Explorer 7

Jimy6000 · Accepted Answer

Hello Bari (white)
Yes, I need the details of the probabilities by event or by several sets of events, not the sum.
Yes, I only have integer numerical values, but in the table you provided, you're showing non-integer values (1, 2, etc...) whereas I would like to have integer values.
I typed the formula you gave me and the result is:
I2: 0
I3: 0.010471204
I4: 0.004188482
I5: 0.012565445
I6: 0.007329843
I7: #N/A
In short, this is not quite the result I was hoping for (at least not 0.104.... only integer numbers, do you understand better?
There is another thing I don't understand; why do I have to enter the numbers 0, 1, 2, 3, 4 in the range (H2:H6)?

baribari · Answer

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?

Jimy6000 · Answer

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

Jimy6000 · Answer

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

baribari · Answer

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 :)

Jimy6000 · Answer

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?

baribari · Answer

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[

Jimy6000 · Answer

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

baribari · Answer

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?

Jimy6000 · Answer

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

baribari · Answer

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

Jimy6000 · Answer

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

Jimy6000 · Answer

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

baribari · Answer

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

Jimy6000 · Answer

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

eriiic · Answer

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

Jimy6000 · Answer

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

eriiic · Answer

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

Jimy6000 · Answer

Good evening Eric. I may not have explained myself well, I am a producer so I am not subject to strikes or supply issues, and the reason the stock cannot exceed 50 pieces is that the room where they are stored can ONLY accommodate 50. So to find out if I can expand or make an extension of the building by the end of next year or the year after, I would like to run a simulation based on my stock history to see where it will lead me in 1 or 2 years. (hence - Probability or forecast) Of course, the company is growing, hence this interest... I tried the formula =PERCENTILE(E4:I198, 0.03) Note that [from E4 to I198] are the cells containing the history and J4 to K198 the prices. But it only gives me the min of the stock and here: =PERCENTILE(E4:E198, 0.97) the max (oh! the point doesn’t work, it absolutely requires a comma 0.03 not 0.03) I don’t know enough to create such long formulas and that’s why I need help :-$ Baribari gave me this formula that gives me strange numbers: =MAX(IF(IF($A$2:$G$198="", "", COUNTIF(D$2:D2,$E$2:$I$198))=0, $E$3:$I$198, "")) which I just changed the cell positions to match my table. By the way, is Baribari dead? (did the formula kill him? lol :)) Finally a big thank you to everyone :)) Jimmy At least I'm making a little progress in Excel :))

eriiic · Answer

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

Jimy6000 · Answer

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

eriiic · Answer

Re,  Yes, I confirm, in column A the dates, in column B the stock on that date. And you must replace ranges like $A$1:$A$11 with $A$4:$A$198 if your ranges are like that. But I repeat: only one column of data (not 5!) in addition to the dates column.  eric

Jimy6000 · Answer

Hello Eric Thanks again for your valuable help. But upon reflection, the stock history is not a steady incline but has had its ups and downs... it certainly doesn't flatten out (thankfully). How can we make an estimate based on that? In any case, it can't be more complicated than the formula =IF(ISNA(MODE(IF(IF...... LOL In any case, the formula works from E4 to E198 :) Thank you so much anyway Jimmy

eriiic · Answer

Hello Jimy,  I don't quite understand your question.. Real values are rarely consistent, they deviate more or less from the 'average' line (the standard deviation I mentioned earlier gives an idea of that discrepancy). You have the estimation, it's given to you by the prevision() formula. This estimation will always be more or less reliable, it's still a statistical result. You can trust it somewhat if you have a very low standard deviation, and take the result with a grain of salt if you have a high standard deviation. Use the ecartype() formula on your stock values to find out its value.  Does that clarify things a bit more for you?  Eric

Jimy6000 · Answer

Hello Eric. I tried to apply the standard deviation to each column but it results in quite a high value. My previous question was about the last sentence where you gave me the correct formula that says: "If your progress is not a straight line but a curve that is rising faster and faster, or on the contrary flattening out, you should do it differently but it's more complicated." Since the progression has neither been straight nor flattened, I was wondering if we could integrate a condition =IF(StandardDeviation) into the formula?  To refine the forecasts... Jimmy

eriiic · Answer

Ah ok. "If your progression is not a straight line but a curve that rises faster and faster, or conversely flattens out, it would need to be done differently but that’s more complicated." It’s generally about looking at the trend, not value by value. You can create a scatter plot by selecting dates and one stock column to see if the stock is rising steadily overall (a straight line) or if it tends to accelerate (a curve). Depending on the result, you need to use another type of regression, but no, we do not include the standard deviation in the progression() formula. Give me the average stock, the minimum, the maximum, and the standard deviation so I have an idea of your data... If the standard deviation is high, it means your data doesn’t hold much significance and any forecast will be marred by a large error, therefore not reliable. eric

Jimy6000 · Answer

Hello Eric. Here I am! I'm in the "clouds" of "points" now, obviously from floating around! lol :)) Back to business. Here are the figures obtained with the standard deviation formulas and min and max: Average stock: 24.9394 Min: 2 Max: 50 Standard deviation: 14.7987 And thank you again for your help ;) Bye Jimmy

eriiic · Answer

Hello Jimy,  So this is where you can use percentile() (see above) to eliminate outliers. Try for example with 0.05 and 0.95 and redo your table without the high and low values. Calculate the standard deviation, but if it remains high, you'll have to rely on your intuition to know when to build ;-) Have a good weekend Eric

Jimy6000 · Answer

Hello Eric. I still don't understand the formula =PERCENTILE()... I was looking on a site where you can download tables and I found a pretty interesting table on Probability: +BINOM.DIST(B30,F13,C10,FALSE)*1000 Could I use a formula like this to perform a simulation, and would I need to create a macro to get a result because I would like to make another table in parallel? I hope this doesn't bother you :$ Thanks again Jimmy

eriiic · Answer

Hello Jimmy,  When you have a function whose details you want to know, you click on "insert function," select it from the drop-down list, and then click on "help on this function" at the bottom. You'll see that there's little chance this function will interest you.  Percentile is not that hard to use; it's afterward when it becomes work... I'll give you an example: if you want to eliminate the lowest 5% of values from your stock in column B. You calculate =Percentile(B1:B198,0.05) Excel will respond, for example, with 4.425145. You create a new table where you put all the values except those that are less than or equal to 4. You do the same thing with the highest 5% of values using: =Percentile(B1:B198,0.95). There Excel will respond, for example, with 42.36554, and you eliminate the values that are greater than or equal to 43.  With this new table, you calculate your standard deviation, and if it has returned to a more reasonable value, your FORECAST() calculated on this new table will be more realistic. But it will always remain a forecast... Eric

Jimy6000 · Answer

No, for the sake of the parts, it doesn't work; I can't leave a part aside on the pretext that it isn't manufactured every week, so I can't forget about it. You didn't give me more details about the scatter plot? What would that look like in my problem? Thank you for your answers :) Jimmy

eriiic · Answer

Good evening,  It's not about leaving one piece aside but about eliminating extreme values to try to get a better forecast. If you don't want to do it, it's not a problem for me. What you can possibly do in addition is to group the stocks of pieces with the same size to smooth out the stocks according to your production and further improve the forecast. But if your figures are still dispersed and irregular, you'll never have a valid forecast no matter what you do. As for the scatter plot, it was to see if you could visualize growth and what form it took. But if your points are scattered, nothing clear should emerge, so forget it. I think I've said everything I wanted to say based on my expertise in the matter, and I don't think I can offer you much more now.  Good luck Eric

Jimy6000 · Answer

Hello Eric Thank you for your help even though I don't always understand what such a formula can bring me. Because if =Percentile(e3:e198; 0.95) gives me 49.00 and the other: 2.00 I don't see what to do with these numbers? By the way, when entering this month's figures, I encountered another problem; when inserting a cell, everything shifts and I have to change all the formulas again, otherwise they don't take the new numbers of the month into account. How can we avoid these update issues? Thanks again :) Jimmy

eriiic · Answer

Hello,  Indeed, if it gives you 49 and 2 for max/min of 50 and 0, it doesn’t add anything more. I don’t know what formulas you’ve used, but if it’s for range references, you select your range and go to the menu 'data / list / create a list'. You reselect the same range (without taking the extra row that was added to your selection) and name it under 'insert / name'. This way, you name all your ranges present in a formula and in the formula, you replace these references with their name. Eric

Jimy6000 · Answer

Hello Eric I'm sorry to bother you again, but in the "data" tab there is no "list" menu (at least under Excel 2007) I haven't entered any formula, I've just typed my data in, but now that there are formulas that depend on this data, I have to modify them every time I enter the latest figures. How can I make the cells move down without having to constantly change the formulas? Thank you Jimmy

eriiic · Answer

Hello Jimmy,  I don't have the 2007; we should start another discussion to ask where it is. Eric

Jimy6000 · Answer

Hello everyone! Still on my spreadsheet, I would like to know how to use the standard deviation in my formula Especially, where to insert it? Can anyone help me? Thank you in advance and have a great day --  :) Keep smiling Bye... Jimmy

Jimy6000 · Answer

Hello everyone Anyone to help me? :(  :) Keep smiling Bye... Jimmy

eriiic · Answer

Hello,  You already asked this question in another thread, and some people replied to you and even provided a link with an explanation and a simple and effective example. We won't do better... eric

Jimy6000 · Answer

Hello forum and Eric I just posted another message yesterday saying that I can't open the 2 links and that it doesn't help me at all yet I would really like to know how to use it? Thank you all --  :) Keep smiling Bye... Jimmy

eriiic · Answer

I replied to you in the other discussion. It would be nice if you marked the old discussions (including this one) as resolved instead of reusing them for other questions. No one here wants to reread 42 unnecessary posts for a new question. Thank you eric

ELFICNUMENOR · Answer

Hello Jimmy   I'm interested in your problem and it seems to me, without having seen your initial table, that it's not with probability functions that you'll manage to make a forecast, but with other models like forecasting (as Eric has already pointed out).  You want to make a forecast of your stocks over 2 years if I understand correctly.  Can you explain to me why there are 7 columns of 198 rows and what they correspond to (for example, are they the stock values over 7 years classified by working day, or do these 7 columns correspond to 7 different products, or is it another structure)?  Is there one or multiple products in this stock?  I would need more details than you have provided so far.

Jimy6000 · Answer

Hello everyone. Actually, what I'm looking for is to create a formula that would give me a percentage as a result. Let me explain: I have a range B2: F20 representing a history, I have the average, the standard deviation, and the frequency of occurrence of all values. What formula should I use to get the expected percentage of occurrence of a value? Thanks everyone --   :) Keep smiling  Bye... Jimmy

ELFICNUMENOR · Answer

Hello Jimmy,  I advise you to use the statistical function COUNTIF(range; criteria). Your range will be B2:F20 and the criteria should be written in quotes like this COUNTIF(B2:F20;"apples") for example or another example: COUNTIF(B2:F20;"=33"). This way you will get the total number of occurrences of "apples" in the table or any other criteria like "=33", and then you need to calculate the total (that is, your universe) total number of fruits (apples + bananas + pears + etc.); you will just have to divide the result of the previous function(s) (COUNTIF) by this total to get the desired percentage of apples, then of bananas, etc.  Let me know if this meets your needs.  Best regards.

Jimy6000 · Answer

Hello Elfic Yes, I have already entered this formula, but what I lack now is a formula that unifies all this data (results, gaps, cycles, time curve) In AE42 I have the percentage result of my stats, e.g., 39.88% (the higher the value, the more likely it is to be selected) In D2 I have the results of my cycles In A200 I have my gaps in delay values, e.g., -2 (consider this result from a condition "for example: -18," so strongly delayed, thus highly requested; the more negative the result, the more likely this value is to be selected.) Difficult to be more explicit In A9:A19, always my time curve 04/12/2007... Regarding these 4 cells, how can I combine this data into one cell? Note: I only put the results of the relevant cells. I was thinking of a formula like: =IF(AND(ISBLANK(), what do you think? Any other help is welcome too Have a good day everyone --  :) Keep smiling Bye... Jimmy

Jimy6000 · Answer

Hello everyone I just found out how =probability() works but I don't master it yet I have a result of 0.6657... but what does it mean? Thank you for your help --  :) Keep smiling Bye... Jimmy

dom · Answer

I want to know the probability of a municipal election given that there are 578 registered voters and 2 lists of 15 candidates.

Jimy6000 · Answer

Hello everyone Here it is, for probability, the solution has been found. It's really simple! And to think I struggled to get here: In column A the values. At the end of column A the sum of it. In column B the probabilities; first value of column A/sum and so on to reach the SAME size as column A (Note: the total of column B must be 1) Then =PROBABILITY(A:A;B:B; "the value for which you want to know the probability") That's it. The formula will return the probability of the searched value (in %) if this value appears multiple times in column A, it adds up the probabilities! That's easy Now I still don't know what the Baribari formula is for: =FREQUENCY(A2:G198;H2:H6)/COUNT(A2:G192) I'm starting to get the hang of Excel but I'm wondering what this one returns?   :) Keep smiling Bye... Jimmy

koukou · Answer

try XLstat

Calculating probability with Excel?

49 réponses

How to get the character µ on the android keyboard.

Texture issues in enshrouded

Access issue to my "contact me" profile

Windows spotlight is not working.

Code 80072efe how to get windows update working again

Video in ".3gp" format

How to reactivate sms notifications on my galaxy ace?

Limited wifi connection: invalid ip address

Wii sport resort-hidden target

Total av has charged my bank account.