Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Strange request: Counting cells and percentages
I'm not even sure how to formulate this right, so please forgive the generic
thread title. I'm racking my brains trying to come up with a formula for an analytical spreadsheet I'm working on. What I need to do, and haven't been able to figure it out, is to count how many occurrences o X I have, excluding some cells, and represent that in a percentage of the total. To give a clearer picture, I have many columns with years. Say, 2008, 2007, 2006, and so on. Each year has, say, either X or Y. Now I can figure out the times X occurs, and its percentage of total, no problem. But what I can't see how to formulate, is getting the total and percentage of total since a given year. For instance, if my spreadsheet has years going back to 1980, I want to get a percentage of the total since 1995 only. Am I being clear? It makes sense to me... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Strange request: Counting cells and percentages
Let's assume:
B1:H1 = year numbers 2008,2007,2006 ...2002 B2:H10 = X or Y To count X from 2008 to a chosen year: A15 = chosen year =SUMPRODUCT(--(B2:B10:INDEX(B2:H10,,MATCH(A15,B1:H1,0))="X")) From that, you should be able to figure out how to get the %. -- Biff Microsoft Excel MVP "jmj713" wrote in message ... I'm not even sure how to formulate this right, so please forgive the generic thread title. I'm racking my brains trying to come up with a formula for an analytical spreadsheet I'm working on. What I need to do, and haven't been able to figure it out, is to count how many occurrences o X I have, excluding some cells, and represent that in a percentage of the total. To give a clearer picture, I have many columns with years. Say, 2008, 2007, 2006, and so on. Each year has, say, either X or Y. Now I can figure out the times X occurs, and its percentage of total, no problem. But what I can't see how to formulate, is getting the total and percentage of total since a given year. For instance, if my spreadsheet has years going back to 1980, I want to get a percentage of the total since 1995 only. Am I being clear? It makes sense to me... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Strange request: Counting cells and percentages
Thanks for this formula, but I think I'm either implementing it wrong, or it
doesn't fit what I'm looking for. What I need to have are several things: Total (from 1980 to 2008), Total X (number and percent of total), Total Y (number and percent of total), and for each X and Y also Total from 1995 to 2008, and this number also represent as percent of total since 1995. Does that make sense? This should give me seven numbers. And the "total percent of X" and the "total percent of X since 1995" should theoretically be different numbers. "T. Valko" wrote: Let's assume: B1:H1 = year numbers 2008,2007,2006 ...2002 B2:H10 = X or Y To count X from 2008 to a chosen year: A15 = chosen year =SUMPRODUCT(--(B2:B10:INDEX(B2:H10,,MATCH(A15,B1:H1,0))="X")) From that, you should be able to figure out how to get the %. -- Biff Microsoft Excel MVP "jmj713" wrote in message ... I'm not even sure how to formulate this right, so please forgive the generic thread title. I'm racking my brains trying to come up with a formula for an analytical spreadsheet I'm working on. What I need to do, and haven't been able to figure it out, is to count how many occurrences o X I have, excluding some cells, and represent that in a percentage of the total. To give a clearer picture, I have many columns with years. Say, 2008, 2007, 2006, and so on. Each year has, say, either X or Y. Now I can figure out the times X occurs, and its percentage of total, no problem. But what I can't see how to formulate, is getting the total and percentage of total since a given year. For instance, if my spreadsheet has years going back to 1980, I want to get a percentage of the total since 1995 only. Am I being clear? It makes sense to me... |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Strange request: Counting cells and percentages
That is to say, I can figure out how to get a total of X since 1995. I can't
figure out how to get a relative percentage of X since 1995. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Strange request: Counting cells and percentages
I see you started another post so I'm deferring to that other post.
-- Biff Microsoft Excel MVP "jmj713" wrote in message ... Thanks for this formula, but I think I'm either implementing it wrong, or it doesn't fit what I'm looking for. What I need to have are several things: Total (from 1980 to 2008), Total X (number and percent of total), Total Y (number and percent of total), and for each X and Y also Total from 1995 to 2008, and this number also represent as percent of total since 1995. Does that make sense? This should give me seven numbers. And the "total percent of X" and the "total percent of X since 1995" should theoretically be different numbers. "T. Valko" wrote: Let's assume: B1:H1 = year numbers 2008,2007,2006 ...2002 B2:H10 = X or Y To count X from 2008 to a chosen year: A15 = chosen year =SUMPRODUCT(--(B2:B10:INDEX(B2:H10,,MATCH(A15,B1:H1,0))="X")) From that, you should be able to figure out how to get the %. -- Biff Microsoft Excel MVP "jmj713" wrote in message ... I'm not even sure how to formulate this right, so please forgive the generic thread title. I'm racking my brains trying to come up with a formula for an analytical spreadsheet I'm working on. What I need to do, and haven't been able to figure it out, is to count how many occurrences o X I have, excluding some cells, and represent that in a percentage of the total. To give a clearer picture, I have many columns with years. Say, 2008, 2007, 2006, and so on. Each year has, say, either X or Y. Now I can figure out the times X occurs, and its percentage of total, no problem. But what I can't see how to formulate, is getting the total and percentage of total since a given year. For instance, if my spreadsheet has years going back to 1980, I want to get a percentage of the total since 1995 only. Am I being clear? It makes sense to me... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
REQUEST FORMULA FOR COUNTING & SUM IN DUPLICATES | Excel Worksheet Functions | |||
Macro Request: Fill in empty cells with previous Filled cell in column | Excel Worksheet Functions | |||
Strange, Irregular Merge of Cells | Excel Discussion (Misc queries) | |||
Percentages between two cells | Excel Worksheet Functions | |||
Percentages and counting | Excel Discussion (Misc queries) |