Forum Discussion
Random numbers from 0 to 100 procent?
Hello everybody,
I'm trying to let Excel do something but I don't know how.
This is what I have.
I have for each day in a year a number reaching from 1.0 to 59.9 kWh (for example) in 7 days next to each other and 52 weeks below each other. (The numbers are energy I have over during the winter and energy I need to buy at night in the summer).
So to know what battery I want to buy I need to know which range of numbers I have the most.
So what I want excel to calculate and let me know is the follwing from the 365 random numbers:
0 to 5 is 19%
0 to 10 is 25%
0 to 20 is 56%
0 to 30 is 63%
0 to 40 is 89%
0 to 50 is 98%
0 to 60 is 100%
For the example above I can look at a battery from (to keep it simple) minimum 20 kWh or one at 40 kWh.
So who knows the magic for this?
Thank you,
Greetings Peter
Peer81234 In the attached I created a simulated random array of values in A2:G53 and the bins in I2:I8
then used the formula:
=COUNTIF(A2#, "<="&I2:I8)/COUNT(A2#)I also included another option next to that using the FREQUENCY() function but that is for each bin and not cumulative and it appears you wanted cumulative
I forgot to mention that A2# is shortcut for the entire 'spill range' produced by the formula in A2 which is that A2:G53 range.
Peer81234 In the attached I created a simulated random array of values in A2:G53 and the bins in I2:I8
then used the formula:
=COUNTIF(A2#, "<="&I2:I8)/COUNT(A2#)I also included another option next to that using the FREQUENCY() function but that is for each bin and not cumulative and it appears you wanted cumulative
I forgot to mention that A2# is shortcut for the entire 'spill range' produced by the formula in A2 which is that A2:G53 range.
- Hi M Tarler,
That is exactly what I need! Thank you for the reply and also the frequency option which I think is also a good idea!
Now I can see which battery would be the best option for us.
Greetings Peter
