Random numbers from 0 to 100 procent?

[Peer81234]

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

[#2 · Accepted solution]

@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.

[#3]

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