SUM Row until certain criteria is met and give out header value.

Hello!

I have a challenge in analyzing supplier data.

I used Pivot table to get the necessary data, but I can`t seem to locate a formula, that I can modify, to calculate and get the necessary results :/

Column A - is item numbers.

Row A1:BF1 is delivery time

B2:BF199 - delivery times in % ( from Pivot table)


My aim is to find out, for each Item number, how long it takes to receive 85%, 90%; 95% and 99% of the goods for a specific Item Nr.
I`m looking to input the formulas in cells BI2; BJ2; BK2; BL2

I would like the formula to look at the data, lets say for Item Nr. 1, for the whole row and give me out a Header number 4 (Column F) based on Suming the Row

For item "number 5" 85% are delivered in 11 days 90%, 95% and 99% are delivered in 12 days.





Can anyone, please, help me? I have been struggling for a few days now...

Hi

Use this formula in BI2 and drag down and forward.

Formula:

Please Login or Register  to view this content.

New formula (no need MUNIT)

Formula:

Please Login or Register  to view this content.

Hi

a first idea (Edit: second...hi José!): I've copied sheet1 (new sheet database)

In database in B2 to be copied across

=Sum(Sheet1!$B2:B2)

In Sheet1

=AGGREGATE(15,6,$B$1:$BF$1/(DATABASE!$B2:$BF2>=BI$1),1)

Regards

Amazing, Thank you! Works perfect!

You made my day!! Happy to receive help in such a short time period!

Thank you for the reply! But I was looking for another result. Maybe we can try one more time?


The data between rows B2:BF2 is the count of deliveries received in a Time scale which is B1:BF57.
I have added the result manualy in columns BI BJ & BK.


Looking forward to your reply.


Edit: I can actualy set up SUM ranges in Columns BI BJ & BK and there will be no ned for a large formula