I have four cells across that must equal 100%. In the fourth cell I have a
data validation formula such as =sum(A1:D1)=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?

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

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

--
Biff
Microsoft Excel MVP


"SJT" wrote in message
...
I have four cells across that must equal 100%. In the fourth cell I have a
data validation formula such as =sum(A1:D1)=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?

SJT,

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

Paul


"SJT" wrote in message
...
I have four cells across that must equal 100%. In the fourth cell I have a
data validation formula such as =sum(A1:D1)=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?

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




SJT wrote:

I have four cells across that must equal 100%. In the fourth cell I have a
data validation formula such as =sum(A1:D1)=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?

--

Dave Peterson

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"SJT" wrote:

I have four cells across that must equal 100%. In the fourth cell I have a
data validation formula such as =sum(A1:D1)=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?