software

I need to find a way to reference a subtotal relative to one of it's lines.

Example
Product # Salesperson Quanity
Percentage
1 Amy 10
**
1 Ron 20
**
1 Jim 25
**
Product # 1 Total 55
2 Amy 15
**
2 Ron 15
**
2 Jim 35
**
2 Bill 20
**
Product # 2 Total 85
3 Jim 35
**
3 Bill 20
**
Product # 3 Total 55

I of course want to calculate the percentage column by dividing the quantity
by the total. The problem is that the relative position of the total varies.
Is there a way to do this without using code?

Thanks,
Arun

My table didn't format well after posting. I reformatted it below.

Product # Salesperson Quanity Percentage
1 Amy 10 **
1 Ron 20 **
1 Jim 10 **
Product # 1 Total 55
2 Amy 15 **
2 Ron 15 **
2 Jim 35 **
2 Bill 20 **
Product # 2 Total 85
3 Jim 35 **
3 Bill 20 **
Product # 3 Total 55

Assumes column A has the product # amp; col B has the Quantity

Also assumes the product # IS a number

=IF(ISNUMBER(A2),C2/SUMIF(A2:A50,A2,C2:C50),quot;quot;)

quot;Arunquot; wrote:

gt; My table didn't format well after posting. I reformatted it below.
gt;
gt; Product # Salesperson Quanity Percentage
gt; 1 Amy 10 **
gt; 1 Ron 20 **
gt; 1 Jim 10 **
gt; Product # 1 Total 55
gt; 2 Amy 15 **
gt; 2 Ron 15 **
gt; 2 Jim 35 **
gt; 2 Bill 20 **
gt; Product # 2 Total 85
gt; 3 Jim 35 **
gt; 3 Bill 20 **
gt; Product # 3 Total 55

=C2/SUMPRODUCT(--($A$2:$A$13=A2),$C$2:$C$13)

Copy down and format as a percentage. This assumes quot;Product #quot; is in cell
A1. It will look like this..

Product #SalespersonQuanityPercentage
1Amy1025.00%
1Ron2050.00%
1Jim1025.00%
Product # 1Total55100.00%
2Amy1517.65%
2Ron1517.65%
2Jim3541.18%
2Bill2023.53%
Product # 2Total85100.00%
3Jim3563.64%
3Bill2036.36%
Product # 3Total55100.00%

The totals show 100% you will probably want to delete them.

quot;Arunquot; wrote:

gt; My table didn't format well after posting. I reformatted it below.
gt;
gt; Product # Salesperson Quanity Percentage
gt; 1 Amy 10 **
gt; 1 Ron 20 **
gt; 1 Jim 10 **
gt; Product # 1 Total 55
gt; 2 Amy 15 **
gt; 2 Ron 15 **
gt; 2 Jim 35 **
gt; 2 Bill 20 **
gt; Product # 2 Total 85
gt; 3 Jim 35 **
gt; 3 Bill 20 **
gt; Product # 3 Total 55

Didn't quite work. The SUMIF function didn't take into account values above
the line it was working on. The final spreadsheet came out like this.

Product # Salesperson Quanity Percentage
1 Amy 10 25%
1 Ron 20 67%
1 Jim 10 100%
Product # 1 Total 55
2 Amy 15 18%
2 Ron 15 21%
2 Jim 35 64%
2 Bill 20 100%
Product # 2 Total 85
3 Jim 35 64%
3 Bill 20 100%
Product # 3 Total 55quot;Duke Careyquot; wrote:

gt; Assumes column A has the product # amp; col B has the Quantity
gt;
gt; Also assumes the product # IS a number
gt;
gt; =IF(ISNUMBER(A2),C2/SUMIF(A2:A50,A2,C2:C50),quot;quot;)
gt;
gt; quot;Arunquot; wrote:
gt;
gt; gt; My table didn't format well after posting. I reformatted it below.
gt; gt;
gt; gt; Product # Salesperson Quanity Percentage
gt; gt; 1 Amy 10 **
gt; gt; 1 Ron 20 **
gt; gt; 1 Jim 10 **
gt; gt; Product # 1 Total 55
gt; gt; 2 Amy 15 **
gt; gt; 2 Ron 15 **
gt; gt; 2 Jim 35 **
gt; gt; 2 Bill 20 **
gt; gt; Product # 2 Total 85
gt; gt; 3 Jim 35 **
gt; gt; 3 Bill 20 **
gt; gt; Product # 3 Total 55

My error - should have been

=IF(ISNUMBER(A2),C2/SUMIF(A$2:A$50,A2,C$2:C$50),quot;quot;)quot;Arunquot; wrote:

gt; Didn't quite work. The SUMIF function didn't take into account values above
gt; the line it was working on. The final spreadsheet came out like this.
gt;
gt; Product # Salesperson Quanity Percentage
gt; 1 Amy 10 25%
gt; 1 Ron 20 67%
gt; 1 Jim 10 100%
gt; Product # 1 Total 55
gt; 2 Amy 15 18%
gt; 2 Ron 15 21%
gt; 2 Jim 35 64%
gt; 2 Bill 20 100%
gt; Product # 2 Total 85
gt; 3 Jim 35 64%
gt; 3 Bill 20 100%
gt; Product # 3 Total 55
gt;
gt;
gt; quot;Duke Careyquot; wrote:
gt;
gt; gt; Assumes column A has the product # amp; col B has the Quantity
gt; gt;
gt; gt; Also assumes the product # IS a number
gt; gt;
gt; gt; =IF(ISNUMBER(A2),C2/SUMIF(A2:A50,A2,C2:C50),quot;quot;)
gt; gt;
gt; gt; quot;Arunquot; wrote:
gt; gt;
gt; gt; gt; My table didn't format well after posting. I reformatted it below.
gt; gt; gt;
gt; gt; gt; Product # Salesperson Quanity Percentage
gt; gt; gt; 1 Amy 10 **
gt; gt; gt; 1 Ron 20 **
gt; gt; gt; 1 Jim 10 **
gt; gt; gt; Product # 1 Total 55
gt; gt; gt; 2 Amy 15 **
gt; gt; gt; 2 Ron 15 **
gt; gt; gt; 2 Jim 35 **
gt; gt; gt; 2 Bill 20 **
gt; gt; gt; Product # 2 Total 85
gt; gt; gt; 3 Jim 35 **
gt; gt; gt; 3 Bill 20 **
gt; gt; gt; Product # 3 Total 55

Wow, it works! But tell me, what do those double negative signs do?

quot;Slothquot; wrote:

gt; =C2/SUMPRODUCT(--($A$2:$A$13=A2),$C$2:$C$13)
gt;
gt; Copy down and format as a percentage. This assumes quot;Product #quot; is in cell
gt; A1. It will look like this..
gt;
gt; Product #SalespersonQuanityPercentage
gt; 1Amy1025.00%
gt; 1Ron2050.00%
gt; 1Jim1025.00%
gt; Product # 1Total55100.00%
gt; 2Amy1517.65%
gt; 2Ron1517.65%
gt; 2Jim3541.18%
gt; 2Bill2023.53%
gt; Product # 2Total85100.00%
gt; 3Jim3563.64%
gt; 3Bill2036.36%
gt; Product # 3Total55100.00%
gt;
gt; The totals show 100% you will probably want to delete them.
gt;
gt; quot;Arunquot; wrote:
gt;
gt; gt; My table didn't format well after posting. I reformatted it below.
gt; gt;
gt; gt; Product # Salesperson Quanity Percentage
gt; gt; 1 Amy 10 **
gt; gt; 1 Ron 20 **
gt; gt; 1 Jim 10 **
gt; gt; Product # 1 Total 55
gt; gt; 2 Amy 15 **
gt; gt; 2 Ron 15 **
gt; gt; 2 Jim 35 **
gt; gt; 2 Bill 20 **
gt; gt; Product # 2 Total 85
gt; gt; 3 Jim 35 **
gt; gt; 3 Bill 20 **
gt; gt; Product # 3 Total 55

Also, is there a more elegant way of replacing all the #DIV/0! errors with
zeros than using an IF(....) statement?

quot;Slothquot; wrote:

gt; =C2/SUMPRODUCT(--($A$2:$A$13=A2),$C$2:$C$13)
gt;
gt; Copy down and format as a percentage. This assumes quot;Product #quot; is in cell
gt; A1. It will look like this..
gt;
gt; Product #SalespersonQuanityPercentage
gt; 1Amy1025.00%
gt; 1Ron2050.00%
gt; 1Jim1025.00%
gt; Product # 1Total55100.00%
gt; 2Amy1517.65%
gt; 2Ron1517.65%
gt; 2Jim3541.18%
gt; 2Bill2023.53%
gt; Product # 2Total85100.00%
gt; 3Jim3563.64%
gt; 3Bill2036.36%
gt; Product # 3Total55100.00%
gt;
gt; The totals show 100% you will probably want to delete them.
gt;
gt; quot;Arunquot; wrote:
gt;
gt; gt; My table didn't format well after posting. I reformatted it below.
gt; gt;
gt; gt; Product # Salesperson Quanity Percentage
gt; gt; 1 Amy 10 **
gt; gt; 1 Ron 20 **
gt; gt; 1 Jim 10 **
gt; gt; Product # 1 Total 55
gt; gt; 2 Amy 15 **
gt; gt; 2 Ron 15 **
gt; gt; 2 Jim 35 **
gt; gt; 2 Bill 20 **
gt; gt; Product # 2 Total 85
gt; gt; 3 Jim 35 **
gt; gt; 3 Bill 20 **
gt; gt; Product # 3 Total 55

SUMPRODUCT ignores any nonnumerical value. the -- converts the logical
values (TRUE and FALSE) to ones and zeros.

quot;Arunquot; wrote:

gt; Wow, it works! But tell me, what do those double negative signs do?
gt;
gt; quot;Slothquot; wrote:
gt;
gt; gt; =C2/SUMPRODUCT(--($A$2:$A$13=A2),$C$2:$C$13)
gt; gt;
gt; gt; Copy down and format as a percentage. This assumes quot;Product #quot; is in cell
gt; gt; A1. It will look like this..
gt; gt;
gt; gt; Product #SalespersonQuanityPercentage
gt; gt; 1Amy1025.00%
gt; gt; 1Ron2050.00%
gt; gt; 1Jim1025.00%
gt; gt; Product # 1Total55100.00%
gt; gt; 2Amy1517.65%
gt; gt; 2Ron1517.65%
gt; gt; 2Jim3541.18%
gt; gt; 2Bill2023.53%
gt; gt; Product # 2Total85100.00%
gt; gt; 3Jim3563.64%
gt; gt; 3Bill2036.36%
gt; gt; Product # 3Total55100.00%
gt; gt;
gt; gt; The totals show 100% you will probably want to delete them.
gt; gt;
gt; gt; quot;Arunquot; wrote:
gt; gt;
gt; gt; gt; My table didn't format well after posting. I reformatted it below.
gt; gt; gt;
gt; gt; gt; Product # Salesperson Quanity Percentage
gt; gt; gt; 1 Amy 10 **
gt; gt; gt; 1 Ron 20 **
gt; gt; gt; 1 Jim 10 **
gt; gt; gt; Product # 1 Total 55
gt; gt; gt; 2 Amy 15 **
gt; gt; gt; 2 Ron 15 **
gt; gt; gt; 2 Jim 35 **
gt; gt; gt; 2 Bill 20 **
gt; gt; gt; Product # 2 Total 85
gt; gt; gt; 3 Jim 35 **
gt; gt; gt; 3 Bill 20 **
gt; gt; gt; Product # 3 Total 55

not that I know of. give me an example of a line that gives the error and I
will try and make a small formula for you.

quot;Arunquot; wrote:

gt; Also, is there a more elegant way of replacing all the #DIV/0! errors with
gt; zeros than using an IF(....) statement?
gt;
gt; quot;Slothquot; wrote:
gt;
gt; gt; =C2/SUMPRODUCT(--($A$2:$A$13=A2),$C$2:$C$13)
gt; gt;
gt; gt; Copy down and format as a percentage. This assumes quot;Product #quot; is in cell
gt; gt; A1. It will look like this..
gt; gt;
gt; gt; Product #SalespersonQuanityPercentage
gt; gt; 1Amy1025.00%
gt; gt; 1Ron2050.00%
gt; gt; 1Jim1025.00%
gt; gt; Product # 1Total55100.00%
gt; gt; 2Amy1517.65%
gt; gt; 2Ron1517.65%
gt; gt; 2Jim3541.18%
gt; gt; 2Bill2023.53%
gt; gt; Product # 2Total85100.00%
gt; gt; 3Jim3563.64%
gt; gt; 3Bill2036.36%
gt; gt; Product # 3Total55100.00%
gt; gt;
gt; gt; The totals show 100% you will probably want to delete them.
gt; gt;
gt; gt; quot;Arunquot; wrote:
gt; gt;
gt; gt; gt; My table didn't format well after posting. I reformatted it below.
gt; gt; gt;
gt; gt; gt; Product # Salesperson Quanity Percentage
gt; gt; gt; 1 Amy 10 **
gt; gt; gt; 1 Ron 20 **
gt; gt; gt; 1 Jim 10 **
gt; gt; gt; Product # 1 Total 55
gt; gt; gt; 2 Amy 15 **
gt; gt; gt; 2 Ron 15 **
gt; gt; gt; 2 Jim 35 **
gt; gt; gt; 2 Bill 20 **
gt; gt; gt; Product # 2 Total 85
gt; gt; gt; 3 Jim 35 **
gt; gt; gt; 3 Bill 20 **
gt; gt; gt; Product # 3 Total 55