Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Validation of a sum
I want to validate that a sum of cells is 0 or 100. How do I do that?
-- Jane |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation Data using Validation Table cell range..... | Excel Discussion (Misc queries) | |||
Data Validation Update Validation Selection | Excel Worksheet Functions | |||
data validation invalid in dynamic validation list | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Worksheet Functions | |||
Data validation with validation lists and combo boxs | Excel Discussion (Misc queries) |