Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to calculate and display the number of values in a column | Excel Discussion (Misc queries) | |||
how to calculate the number of non blank cells from any column? | Excel Worksheet Functions | |||
How do I Calculate Percentages for each number in a column? | Excel Discussion (Misc queries) | |||
Calculate number of days in a column of dates | Excel Discussion (Misc queries) | |||
Calculate number of days in a column of dates | Excel Discussion (Misc queries) |