Validation of a sum
I want to validate that a sum of cells is 0 or 100. How do I do that?
-- Jane |
Validation of a sum
Let's say the cells in question are A1 thru A20. In another cell enter:
=IF(OR(SUM(A1:A20)=0,SUM(A1:A20)=100),"Valid","Not Valid") -- Gary''s Student - gsnu200755 "Jane" wrote: I want to validate that a sum of cells is 0 or 100. How do I do that? -- Jane |
Validation of a sum
Hi Jane
One way =IF(OR(ROUND(SUM(D1:D10),0)=0,ROUND(SUM(D1:D10),0) =100),"OK","Wrong") -- Regards Roger Govier "Jane" wrote in message ... I want to validate that a sum of cells is 0 or 100. How do I do that? -- Jane |
Validation of a sum
I would apply conditional formatting for the cell containing the SUM (say A2)
with formula =AND(A2<0,A2<100) and red color! Regards, Stefi €˛Jane€¯ ezt Ć*rta: I want to validate that a sum of cells is 0 or 100. How do I do that? -- Jane |
Validation of a sum
Thanks - but I know that formula.
I look for a solution, where the sum is being validated in a way, that make it impossibly to move to the next step until the rule is satisfied. -- Jane "Gary''s Student" skrev: Let's say the cells in question are A1 thru A20. In another cell enter: =IF(OR(SUM(A1:A20)=0,SUM(A1:A20)=100),"Valid","Not Valid") -- Gary''s Student - gsnu200755 "Jane" wrote: I want to validate that a sum of cells is 0 or 100. How do I do that? -- Jane |
Validation of a sum
The solution is even more simple. Once again we want the sum of cells A1
thru A20 to be 100. We allow the user to fill only cells 1 thru A19. In cell A20 we enter: =100-SUM(A1:A19) and protect this cell. This will absolutely force the condition to be true!! -- Gary''s Student - gsnu200755 "Jane" wrote: Thanks - but I know that formula. I look for a solution, where the sum is being validated in a way, that make it impossibly to move to the next step until the rule is satisfied. -- Jane "Gary''s Student" skrev: Let's say the cells in question are A1 thru A20. In another cell enter: =IF(OR(SUM(A1:A20)=0,SUM(A1:A20)=100),"Valid","Not Valid") -- Gary''s Student - gsnu200755 "Jane" wrote: I want to validate that a sum of cells is 0 or 100. How do I do that? -- Jane |
Validation of a sum
How clever, but unfortunately it doesn't solve my problem.
All the cells need to be open for the user to fill-in values. It is an area of four cells, where the user has to fill in a distribution of 100 percent. It is allowed to fill in one, two, three or four cells, but the sum should add to 100. What I like to do is to allow the user to fill in values in the four cells, then validate the sum and don't allow the user to move to the next step until the rule is satisfied. -- Jane "Gary''s Student" skrev: The solution is even more simple. Once again we want the sum of cells A1 thru A20 to be 100. We allow the user to fill only cells 1 thru A19. In cell A20 we enter: =100-SUM(A1:A19) and protect this cell. This will absolutely force the condition to be true!! -- Gary''s Student - gsnu200755 "Jane" wrote: Thanks - but I know that formula. I look for a solution, where the sum is being validated in a way, that make it impossibly to move to the next step until the rule is satisfied. -- Jane "Gary''s Student" skrev: Let's say the cells in question are A1 thru A20. In another cell enter: =IF(OR(SUM(A1:A20)=0,SUM(A1:A20)=100),"Valid","Not Valid") -- Gary''s Student - gsnu200755 "Jane" wrote: I want to validate that a sum of cells is 0 or 100. How do I do that? -- Jane |
All times are GMT +1. The time now is 05:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com