totalling values based on conditions

[Sherry]

I have 2 columns of data. One contains the "tier" or 1, 2, or 3. The other
column contains a weight (%). The sum of all tier "1" must = 100%. The sum
of all tier "2" for a tier 1 must sum to 100%. The sum of all tier "3" for
each tier 2 must sum to 100%. I need to validate that the weights are
correct. I've validated Tier "1" with Sumif, but I'm having trouble with
tier 2 & 3. Then I tried using VBA, but I am having difficulty. Can anyone
help with a simple approach?

Thank you!!! Sherry

Tier Weight
1 50%
2 30%
3 10%
3 90%
2 70%
1 50%
2 40%
2 60%
3 50%
3 50%

 

[Wigi]

Can you be more clear about the "rules" the percentages should follow? I
don't follow you I'm afraid.

 

[Lars-Åke Aspelin]

I have 2 columns of data. One contains the "tier" or 1, 2, or 3. The other
column contains a weight (%). The sum of all tier "1" must = 100%. The sum
of all tier "2" for a tier 1 must sum to 100%. The sum of all tier "3" for
each tier 2 must sum to 100%. I need to validate that the weights are
correct. I've validated Tier "1" with Sumif, but I'm having trouble with
tier 2 & 3. Then I tried using VBA, but I am having difficulty. Can anyone
help with a simple approach?

Thank you!!! Sherry

Tier Weight
1 50%
2 30%
3 10%
3 90%
2 70%
1 50%
2 40%
2 60%
3 50%
3 50%

Which formula did you use to validate the weightsum for Tier "1"?
Can you not use exactly the same method for Tier "2" and Tier "3" by
just replaceing "1" with "2" and "3" respectively?

Lars-Åke

 

[Sherry]

Think of it as a hierarchy. In the example I sited, the first tier 1 has two
tier 2's under it. The weights of these must sum to 100%. One of those tier
2s has 2 tier 3s under it and they must sum to 100%. So, we can't simply add
the 1's, add the 2's and add the 3's. We have to know whether it is a new
tier or not. The actual data started out as:
1.0 50%
1.1 30%
1.1.1 10%
1.1.2 90%
1.2 70%
2.0 40%

But I computed a tier number to operate on. Hope this is more descriptive.

 

[Sherry]

Column B is Tier and Column D is Weight:
=SUMIF(B7:B29,"=1",D729)

I cannot use this same formual because it would tally all the 2's and then
all the 3's. I need the tallies to be by group.

 

[Lars-Åke Aspelin]

Column B is Tier and Column D is Weight:
=SUMIF(B7:B29,"=1",D729)

I cannot use this same formual because it would tally all the 2's and then
all the 3's. I need the tallies to be by group.

OK, I did not understand what tiers werw I guess.

Assuming that your data are in columns A and B from row 2 to row 11
and that the cells A1 and A12 are blank (or zeroes)
The data in columns A and B have to be numbers, not text.

Try the following formula in cell C2:
Note: This is an array formula that has to be entered by
CTRL+SHIFT+ENTER rather than just ENTER

=SUMPRODUCT(- -(A$1:A$12=A2),- -(B$1:B$12),-
-(ROW(A$1:A$12)>MAX(ROW(A$1:A$12)*(- -(A$1:A$12<A2)*(-
-ROW(A$1:A$12)<ROW(A2))))),- -(ROW(A$1:A$12)<1/MAX(1/ROW(A$1:A$12)*(-
-(A$1:A$12)<A2)*(- -ROW(A$1:A$12)>ROW(A2)))))

(all in one line)

Copy this formula down from C2 to C11 and there will be a 1 (100%) for
each row that the corresponding tiers have the correct sum (100%)
If e.g. cell B5 is faulty and has 19% rather than 90% you will get
0.29 (29%) in cells C4 and C5 indicating that the sum of B4 and B5 is
not correct.

Hope this helps / Lars-Åle

 

[Sherry]

It works! That is the longest SUMIF I've ever seen, but I don't care, as
long as it works! I will not know the last row, but I think I can get there
from here.

Thank you again.

 

[Lars-Åke Aspelin]

I will make an attempt to explain the differnet parts of the formula,
which by the way is not a SUMIF but a SUMPRODUCT.

=SUMPRODUCT(- -(A$1:A$12=A2),- -(B$1:B$12),-
-(ROW(A$1:A$12)>MAX(ROW(A$1:A$12)*(- -(A$1:A$12<A2)*(-
-ROW(A$1:A$12)<ROW(A2))))),- -(ROW(A$1:A$12)<1/MAX(1/ROW(A$1:A$12)*(-
-(A$1:A$12)<A2)*(- -ROW(A$1:A$12)>ROW(A2)))))


(A$1:A$12=A2)

This filters out rows with the same tier level (or whatever the number
in column A is called

(B$1:B$12)

This is the actual weights to be added. (maybe this part should have
been the first one as all other parts are different kinds of filters)

(ROW(A$1:A$12)>

Only include rows after a certain startrow

MAX(ROW(A$1:A$12)*(- -(A$1:A$12<A2)*(- -ROW(A$1:A$12)<ROW(A2)))))

The startrow is the row with highest, MAX, rownumber where the tier is
less than the tier on the current row but comes BEFORE the current row

(ROW(A$1:A$12)<

Only include rows before a certain stoprow

ROW(A$1:A$12)<1/MAX(1/ROW(A$1:A$12)*(- -(A$1:A$12)<A2)*(-
-ROW(A$1:A$12)>ROW(A2)

The stoprow is the row with lowest rownumber where the tier is less
than the tier on the current row but comes AFTER the current row.
The inversion of the maximum inverted rownumber is used here insted of
the minimum row number in order not to get row number 0 as the
stoprow.

This result could probably be achieved with a shorter formula, but I
leave that for someone else to try to figure out.

Lars-Åke

 

[Sherry]

Thank you.

I will make an attempt to explain the differnet parts of the formula,
which by the way is not a SUMIF but a SUMPRODUCT.

=SUMPRODUCT(- -(A$1:A$12=A2),- -(B$1:B$12),-
-(ROW(A$1:A$12)>MAX(ROW(A$1:A$12)*(- -(A$1:A$12<A2)*(-
-ROW(A$1:A$12)<ROW(A2))))),- -(ROW(A$1:A$12)<1/MAX(1/ROW(A$1:A$12)*(-
-(A$1:A$12)<A2)*(- -ROW(A$1:A$12)>ROW(A2)))))


(A$1:A$12=A2)

This filters out rows with the same tier level (or whatever the number
in column A is called

(B$1:B$12)

This is the actual weights to be added. (maybe this part should have
been the first one as all other parts are different kinds of filters)

(ROW(A$1:A$12)>

Only include rows after a certain startrow

MAX(ROW(A$1:A$12)*(- -(A$1:A$12<A2)*(- -ROW(A$1:A$12)<ROW(A2)))))

The startrow is the row with highest, MAX, rownumber where the tier is
less than the tier on the current row but comes BEFORE the current row

(ROW(A$1:A$12)<

Only include rows before a certain stoprow

ROW(A$1:A$12)<1/MAX(1/ROW(A$1:A$12)*(- -(A$1:A$12)<A2)*(-
-ROW(A$1:A$12)>ROW(A2)

The stoprow is the row with lowest rownumber where the tier is less
than the tier on the current row but comes AFTER the current row.
The inversion of the maximum inverted rownumber is used here insted of
the minimum row number in order not to get row number 0 as the
stoprow.

This result could probably be achieved with a shorter formula, but I
leave that for someone else to try to figure out.

Lars-Ã…ke