Data Validation

[SJT]

I have four cells across that must equal 100%. In the fourth cell I have a
data validation formula such as =sum(A11)=1. So in this case the
validation formula is in D1 and there is an error message that tells the user
the sum of the cells is not 100%. All of the cells are formatted for %.
However, in the event that the last cell does not get the sum to 100% and I
hit retry and insert the proper number I am still getting an error message.
It appears to want to put the number in as a general number instead of a
percentage. Any thoughts on how I could resolve this?

 

[T. Valko]

You'd have to re-enter as a percentage.

Insted of typing 70 (for 70%) type either .7 or 70%.

 

[Paul]

SJT,

100%+100%+100%+100%=400% or 1+1+1+1=4 so your formula should read either
=sum(A11)=400% or =sum(A11)=4.

Paul

 

[Dave Peterson]

As a user, I'd be kind of ticked off that you just don't do the calculation for
me:

=1-sum(a1:c1)

Or with a couple of checks:
=IF(OR(COUNTIF(A1:C1,">1")>0,COUNTIF(A1:C1,"<0")>0,SUM(A1:C1)>1),
"Error",1-SUM(A1:C1))

 

[Shane Devenshire]

Hi,

I like the suggestion to calculate it for the user. However, one other
problem might be that the is a decimal error. For example if A1:C1 each
contain a formula like =1/4.1 then the actual number in the cells would be
0.24390243902439 regardless of the format of the cells. Then if the format
was 2 decimals the user would see .24 three times and assume they needed to
enter 1-.72 or .28 of course that would cause the data validation to fail.