Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to calculate percentage of values of subtotals
In column one I have the names of many companies. Each company has different
sales amounts. so each company is listed more than once (each company may be listed a different amount of times, depending on the number of sales) I have sales subtotaled for each company. In the next column over I would like to calculate the percentage of a value compared to the subtotal. i need to do this for each group. I could do this if there were the same number of sales for each company, but how do I create a formula to do this when there are different numbers of values? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to calculate percentage of values of subtotals
Let's say you have the subtotaled sales in column C.
Do you want the percentage value to be the subtotal/total for all companies. Let's say your subtotals are in C1:C10 (for this example) In D1, put = C1/sum($c$1:$C$10) and format as a % If that's not what you want, come back and clarify your request. "Alicia" wrote in message ... In column one I have the names of many companies. Each company has different sales amounts. so each company is listed more than once (each company may be listed a different amount of times, depending on the number of sales) I have sales subtotaled for each company. In the next column over I would like to calculate the percentage of a value compared to the subtotal. i need to do this for each group. I could do this if there were the same number of sales for each company, but how do I create a formula to do this when there are different numbers of values? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to calculate percentage of values of subtotals
As I understand it, you want to determine the share of the company's total
volume represented by each transaction. Ignoring the subtotals for a moment, let's suppose the company name is in column A and the sale amount in company B, with headers in row 1 and data beginning in row 2. In c2 you could calculate =b2/sumif(a:a,a2,b:b) and format as a percent. The sumif totals this company's sales, so the division gives you the percent of this company's sales accounted for by this transaction. If the subtotals are interspersed, with the subtotal labels (column A) the same as the company name, you'd need to multiply the formula above by 2 since the sumif would total up the individual transactions as well as the subtotal. HTH. --Bruce "Alicia" wrote: In column one I have the names of many companies. Each company has different sales amounts. so each company is listed more than once (each company may be listed a different amount of times, depending on the number of sales) I have sales subtotaled for each company. In the next column over I would like to calculate the percentage of a value compared to the subtotal. i need to do this for each group. I could do this if there were the same number of sales for each company, but how do I create a formula to do this when there are different numbers of values? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to calculate percentage of values of subtotals
Thank you for your help. What I am trying to say is that, for example, I
have sales and their subtotals in column C. Lets say C2-C6 are sales values for Company X and C7 is the subtotal. THen C8-C20 might be sales values for company Y with C21 the subtotal and so on. THere are different numbers of sales values for hundreds of companies with each company having its own subtotal. I need to find what percentage each sales value is compared to the subtotal. (The % each company sold compared to its own total) So I would need to find the % of C2 and C3 and so on compared to C7. But then I need to go down and find the % of C8 & C9 & so on compared to C21. If there were the same number of values and then a subtotal for each company, I could do it....ANy suggestions? Help is GREATLY appreciated! "bpeltzer" wrote: As I understand it, you want to determine the share of the company's total volume represented by each transaction. Ignoring the subtotals for a moment, let's suppose the company name is in column A and the sale amount in company B, with headers in row 1 and data beginning in row 2. In c2 you could calculate =b2/sumif(a:a,a2,b:b) and format as a percent. The sumif totals this company's sales, so the division gives you the percent of this company's sales accounted for by this transaction. If the subtotals are interspersed, with the subtotal labels (column A) the same as the company name, you'd need to multiply the formula above by 2 since the sumif would total up the individual transactions as well as the subtotal. HTH. --Bruce "Alicia" wrote: In column one I have the names of many companies. Each company has different sales amounts. so each company is listed more than once (each company may be listed a different amount of times, depending on the number of sales) I have sales subtotaled for each company. In the next column over I would like to calculate the percentage of a value compared to the subtotal. i need to do this for each group. I could do this if there were the same number of sales for each company, but how do I create a formula to do this when there are different numbers of values? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to calculate percentage of values of subtotals
Thank you for your help. What I am trying to say is that, for example, I
have sales and their subtotals in column C. Lets say C2-C6 are sales values for Company X and C7 is the subtotal. THen C8-C20 might be sales values for company Y with C21 the subtotal and so on. THere are different numbers of sales values for hundreds of companies with each company having its own subtotal. I need to find what percentage each sales value is compared to the subtotal. (The % each company sold compared to its own total) So I would need to find the % of C2 and C3 and so on compared to C7. But then I need to go down and find the % of C8 & C9 & so on compared to C21. If there were the same number of values and then a subtotal for each company, I could do it....ANy suggestions? Help is GREATLY appreciated! "Barb Reinhardt" wrote: Let's say you have the subtotaled sales in column C. Do you want the percentage value to be the subtotal/total for all companies. Let's say your subtotals are in C1:C10 (for this example) In D1, put = C1/sum($c$1:$C$10) and format as a % If that's not what you want, come back and clarify your request. "Alicia" wrote in message ... In column one I have the names of many companies. Each company has different sales amounts. so each company is listed more than once (each company may be listed a different amount of times, depending on the number of sales) I have sales subtotaled for each company. In the next column over I would like to calculate the percentage of a value compared to the subtotal. i need to do this for each group. I could do this if there were the same number of sales for each company, but how do I create a formula to do this when there are different numbers of values? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to calculate percentage of values of subtotals
That's the problem I attempted to address; did you try the formulas I
suggested (adjusting for the different column references)? "Alicia" wrote: Thank you for your help. What I am trying to say is that, for example, I have sales and their subtotals in column C. Lets say C2-C6 are sales values for Company X and C7 is the subtotal. THen C8-C20 might be sales values for company Y with C21 the subtotal and so on. THere are different numbers of sales values for hundreds of companies with each company having its own subtotal. I need to find what percentage each sales value is compared to the subtotal. (The % each company sold compared to its own total) So I would need to find the % of C2 and C3 and so on compared to C7. But then I need to go down and find the % of C8 & C9 & so on compared to C21. If there were the same number of values and then a subtotal for each company, I could do it....ANy suggestions? Help is GREATLY appreciated! "bpeltzer" wrote: As I understand it, you want to determine the share of the company's total volume represented by each transaction. Ignoring the subtotals for a moment, let's suppose the company name is in column A and the sale amount in company B, with headers in row 1 and data beginning in row 2. In c2 you could calculate =b2/sumif(a:a,a2,b:b) and format as a percent. The sumif totals this company's sales, so the division gives you the percent of this company's sales accounted for by this transaction. If the subtotals are interspersed, with the subtotal labels (column A) the same as the company name, you'd need to multiply the formula above by 2 since the sumif would total up the individual transactions as well as the subtotal. HTH. --Bruce "Alicia" wrote: In column one I have the names of many companies. Each company has different sales amounts. so each company is listed more than once (each company may be listed a different amount of times, depending on the number of sales) I have sales subtotaled for each company. In the next column over I would like to calculate the percentage of a value compared to the subtotal. i need to do this for each group. I could do this if there were the same number of sales for each company, but how do I create a formula to do this when there are different numbers of values? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I break values apart that are in the same cell? | Excel Worksheet Functions | |||
How do I formula a percentage with text values in the data field | Excel Worksheet Functions | |||
Calculate percentage based on cells with conditional formatting | New Users to Excel | |||
calculate percentage | Excel Worksheet Functions | |||
Second serie doesn't use X-as values | Charts and Charting in Excel |