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!!

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!!

Variable Percentages
 

....and of course copy/paste the above formula in D down along the data.
--
Regards,
Sébastien

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

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

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