Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable Percentages
I could do this manually, but it would take me forever. Is there a
program/formula that could be written to automatically find the Individual Job percentages - column C?? The variable to this equation is that the number of Individual Jobs varies. And how to refer to the correct company total? ------------- Column A = Company Name Column B = Total Sales amount for the company followed by Individual Job amounts (which add up to the Total Sales). The number of Individual Jobs can vary. Column C = Individual Job percentage of the company's Total Sales. See the example below. A B C XYZ Total 485,000 XYZ 265,000 55% XYZ 150,000 31% XYZ 55,000 11% XYZ 15,000 3% ABC Total 800,500 ABC 500,000 62% ABC 300,000 37% JKL Total 1,400 JKL 500 36% JKL 400 29% JKL 300 21% JKL 200 14% JKL 100 7% JKL 50 4% Thank you in advance!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable Percentages
Hi,
Assuming it starts in row 2 in D2: =IF(UPPER(RIGHT(A2,6))=" TOTAL","",B2/VLOOKUP(A2&" TOTAL",$A:$B,2,FALSE)) -- Regards, Sébastien "jtoy" wrote: I could do this manually, but it would take me forever. Is there a program/formula that could be written to automatically find the Individual Job percentages - column C?? The variable to this equation is that the number of Individual Jobs varies. And how to refer to the correct company total? ------------- Column A = Company Name Column B = Total Sales amount for the company followed by Individual Job amounts (which add up to the Total Sales). The number of Individual Jobs can vary. Column C = Individual Job percentage of the company's Total Sales. See the example below. A B C XYZ Total 485,000 XYZ 265,000 55% XYZ 150,000 31% XYZ 55,000 11% XYZ 15,000 3% ABC Total 800,500 ABC 500,000 62% ABC 300,000 37% JKL Total 1,400 JKL 500 36% JKL 400 29% JKL 300 21% JKL 200 14% JKL 100 7% JKL 50 4% Thank you in advance!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable Percentages
....and of course copy/paste the above formula in D down along the data.
-- Regards, Sébastien |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable Percentages
wow - I'm so impressed with the help for this Discussion Group. The formula
worked! however I was wondering if you could explain how it works. I'm trying to learn more about formulas and how to create them. I'm be interested in learning how you put the structure together. thanks! "sebastienm" wrote: ...and of course copy/paste the above formula in D down along the data. -- Regards, Sébastien |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable Percentages
The function has basically an IF and a VLOOKUP.
VLOOKUP ----------- VLOOKUP( Search_What, Search_Where, Returned_Column, Approximate_Match) - Search_what = value to search for - Search_Where = Source Range including column where to search Search_What and column of values to return. Search_What is search only in first column of Search_Where. - Returned_Column = column number within Search_Where of the value to return - Aprrox_Matc = False if only searching for an exact match of search_what in first column of Search_Where. Else True (for approximate match). Eg: VLOOKUP(Z1, $A$10:$D$100, 3, False) - Search for value in Z1 - Search within first column of A10:D100, ie in A10:A100 - 3: When a match is found, return the corresponding value in the 3rd column of A10:D100, ie in C10,C100 - Search for an exact match (for more info on the lookup functions, check at the online file) FULL FUNCTION ------------------ =IF(UPPER(RIGHT(A2,6))=" TOTAL","",B2/VLOOKUP(A2&" TOTAL",$A:$B,2,FALSE)) means : If the 6 last characters of A2 in uppercase are " TOTAL" Then it is a total row so just return an empty string "" Else A2 is a regular item so: - search for its total (VLOOKUP): Search for value <A2 & " TOTAL" in column A and return corresponding value from B (ie total amount) - return < B2 / total_amount from the above line End If I hope this helps, -- Regards, Sébastien |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable Percentages
thank you!! Exactly the answer I was looking for.
"sebastienm" wrote: The function has basically an IF and a VLOOKUP. VLOOKUP ----------- VLOOKUP( Search_What, Search_Where, Returned_Column, Approximate_Match) - Search_what = value to search for - Search_Where = Source Range including column where to search Search_What and column of values to return. Search_What is search only in first column of Search_Where. - Returned_Column = column number within Search_Where of the value to return - Aprrox_Matc = False if only searching for an exact match of search_what in first column of Search_Where. Else True (for approximate match). Eg: VLOOKUP(Z1, $A$10:$D$100, 3, False) - Search for value in Z1 - Search within first column of A10:D100, ie in A10:A100 - 3: When a match is found, return the corresponding value in the 3rd column of A10:D100, ie in C10,C100 - Search for an exact match (for more info on the lookup functions, check at the online file) FULL FUNCTION ------------------ =IF(UPPER(RIGHT(A2,6))=" TOTAL","",B2/VLOOKUP(A2&" TOTAL",$A:$B,2,FALSE)) means : If the 6 last characters of A2 in uppercase are " TOTAL" Then it is a total row so just return an empty string "" Else A2 is a regular item so: - search for its total (VLOOKUP): Search for value <A2 & " TOTAL" in column A and return corresponding value from B (ie total amount) - return < B2 / total_amount from the above line End If I hope this helps, -- Regards, Sébastien |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Runtime Error '91' Object variable or With block variable not set | Excel Discussion (Misc queries) | |||
why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? | Excel Worksheet Functions | |||
Run-time error '91': "Object variable or With block variable not set | Excel Programming | |||
setting a range variable equal to the value of a string variable | Excel Programming | |||
Cells.Find error Object variable or With block variable not set | Excel Programming |