|
totalling values based on conditions
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% |
totalling values based on conditions
Can you be more clear about the "rules" the percentages should follow? I
don't follow you I'm afraid. -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "Sherry" wrote: 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% |
totalling values based on conditions
On Mon, 28 Jul 2008 10:40:01 -0700, Sherry
wrote: 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 |
totalling values based on conditions
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. "Wigi" wrote: Can you be more clear about the "rules" the percentages should follow? I don't follow you I'm afraid. -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "Sherry" wrote: 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% |
totalling values based on conditions
Column B is Tier and Column D is Weight:
=SUMIF(B7:B29,"=1",D7:D29) 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" wrote: On Mon, 28 Jul 2008 10:40:01 -0700, Sherry wrote: 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 |
totalling values based on conditions
On Mon, 28 Jul 2008 13:19:52 -0700, Sherry
wrote: Column B is Tier and Column D is Weight: =SUMIF(B7:B29,"=1",D7:D29) 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" wrote: On Mon, 28 Jul 2008 10:40:01 -0700, Sherry wrote: 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 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 |
totalling values based on conditions
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" wrote: On Mon, 28 Jul 2008 13:19:52 -0700, Sherry wrote: Column B is Tier and Column D is Weight: =SUMIF(B7:B29,"=1",D7:D29) 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" wrote: On Mon, 28 Jul 2008 10:40:01 -0700, Sherry wrote: 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 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 |
totalling values based on conditions
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 On Mon, 28 Jul 2008 14:28:45 -0700, Sherry wrote: 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" wrote: On Mon, 28 Jul 2008 13:19:52 -0700, Sherry wrote: Column B is Tier and Column D is Weight: =SUMIF(B7:B29,"=1",D7:D29) 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" wrote: On Mon, 28 Jul 2008 10:40:01 -0700, Sherry wrote: 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 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 |
totalling values based on conditions
Thank you.
"Lars-Ã…ke Aspelin" wrote: 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 On Mon, 28 Jul 2008 14:28:45 -0700, Sherry wrote: 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" wrote: On Mon, 28 Jul 2008 13:19:52 -0700, Sherry wrote: Column B is Tier and Column D is Weight: =SUMIF(B7:B29,"=1",D7:D29) 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" wrote: On Mon, 28 Jul 2008 10:40:01 -0700, Sherry wrote: 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 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 |
| All times are GMT +1. The time now is 05:00 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com