determine the percentile from 0 to 100 in accordance to started and ended date

Hi
Please can you help me .
My spreadsheet is like this (please see the attached):

1. Column A : Dates (Every day's Date):
2. Column B : Staging number (from 1 to 365)
3. Column I to K : Is where I want the formula to be: (column I to K: determine the percentile from 0 to 100 (see column I) in accordance to started and ended date).


The formula must look at every in-Column A and analyze the corresponding data in column B and give me the expected result on column I,J, K for example.



Many Thanks.

From what I can see, what you have in column I should do just fine. Columns J and K need the percentile reference adjusted to be to column H, but otherwise they look fine, too. What do you need these formulas to do differently?

Hi MrShorty,

I have a very large dataset and I want an formular that i can do automatically. The attached excel is just a simplified version of my original dataset.

Anyway, I have updated my excel and problem in according to the started data and ended date.

Appreciate your though on that.

That sound like you need some kind of IF() array or FILTER() function or similar nested inside of your PERCENTILE.INC() function. Something that would return an array of values from column C only for the records where A is between the specified dates.

Unfortunately, I never use those kind of array formulas, so I'm no good at building them. I will see if I can get someone in here who is better at that sort of thing.

Try:

Formula:

Please Login or Register  to view this content.

Or, if you have 365

Formula:

Please Login or Register  to view this content.

Or even

Formula:

Please Login or Register  to view this content.

Should be able to carry this one across

Formula:

Please Login or Register  to view this content.

You're welcome. Thanks for the rep.


Do you have 365? If so, please update your profile. Thanks.