Validation of a sum

[Guest]

I want to validate that a sum of cells is 0 or 100. How do I do that?

 

[Guest]

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")

 

[Roger Govier]

Hi Jane

One way
=IF(OR(ROUND(SUM(D110),0)=0,ROUND(SUM(D110),0)=100),"OK","Wrong")

 

[Guest]

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:

 

[Guest]

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.

 

[Guest]

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!!

 

[Guest]

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.