Calculate a number for 80% of column
 

Hi,
I have 2 columns which process days it takes to finalise projects. The 1st
column display the days and the 2nd column display how many projects were
done within this day number:

Days Projects
4 5
12 2 etc.
(400+ projects per month)

Every month the detail is added to the list.

I need to calculate how many days were used for 80% of projects per month.

I use a pivot table to display the list and projects and then do a
calculation next to the pivot table, but this is very time-consuming.
Can you please help me with a function do to this?
Thanks
--
Hennie

Calculate a number for 80% of column
 

Let's see if I understand
In A1,B1,C1 I have labels: Date, Projects, Sum
In A2:A14 I have calendar days (1,2,3 .... ) I have 14 but you could have
more
In B I have the projects finished on that day
In C1 I have the formula =SUM($B$2:B2) and this copied down the column
In E2:E5 I have this text: 80%, sum, position, date
In F1 I have =C14*80% (in my example this is 44)
In F3 =VLOOKUP(F2,C2:C14,1,TRUE) (this returns 42 - closes match to 44)
In F4 =MATCH(F3,C2:C14) (returns 11, since the 42 value is the 11th value in
C2:C14
In F5 =INDEX(A2:A14,F4) (returns 14 - the date when production totalled 42)

We could put all this in one formula but I will leave that to you.
You can copy this from sheet to sheet for different months
If you do no like the under estimate then change =INDEX(A2:A14,F4) to
=INDEX(A2:A14,F4+1)

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Hennie" wrote in message
...
Hi,
I have 2 columns which process days it takes to finalise projects. The 1st
column display the days and the 2nd column display how many projects were
done within this day number:

Days Projects
4 5
12 2 etc.
(400+ projects per month)

Every month the detail is added to the list.

I need to calculate how many days were used for 80% of projects per month.

I use a pivot table to display the list and projects and then do a
calculation next to the pivot table, but this is very time-consuming.
Can you please help me with a function do to this?
Thanks
--
Hennie

Calculate a number for 80% of column
 

Thanks Bernard, this is great!
was very helpful
--
Hennie


"Bernard Liengme" wrote:

Let's see if I understand
In A1,B1,C1 I have labels: Date, Projects, Sum
In A2:A14 I have calendar days (1,2,3 .... ) I have 14 but you could have
more
In B I have the projects finished on that day
In C1 I have the formula =SUM($B$2:B2) and this copied down the column
In E2:E5 I have this text: 80%, sum, position, date
In F1 I have =C14*80% (in my example this is 44)
In F3 =VLOOKUP(F2,C2:C14,1,TRUE) (this returns 42 - closes match to 44)
In F4 =MATCH(F3,C2:C14) (returns 11, since the 42 value is the 11th value in
C2:C14
In F5 =INDEX(A2:A14,F4) (returns 14 - the date when production totalled 42)

We could put all this in one formula but I will leave that to you.
You can copy this from sheet to sheet for different months
If you do no like the under estimate then change =INDEX(A2:A14,F4) to
=INDEX(A2:A14,F4+1)

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Hennie" wrote in message
...
Hi,
I have 2 columns which process days it takes to finalise projects. The 1st
column display the days and the 2nd column display how many projects were
done within this day number:

Days Projects
4 5
12 2 etc.
(400+ projects per month)

Every month the detail is added to the list.

I need to calculate how many days were used for 80% of projects per month.

I use a pivot table to display the list and projects and then do a
calculation next to the pivot table, but this is very time-consuming.
Can you please help me with a function do to this?
Thanks
--
Hennie