Validation for sum

J

JICDB

I have a row that contains about 26 cells and holds percentages. I want an
error message to display when the sum of those 26 cells goes over 100%. I
didn't know how to do this so based on some suggestions I found in the
archives here I first created a total cell at the end of the row and then
used this formula in the 26 cells of the row =BQ16>100.

I feel stupid cause I'm sure this is so easy it will bite me on the nose.
Can someone help me. Thanks!
 
T

T. Valko

Well, it depends on how you want this to work.

Since the cell (presumably) contains a SUM formula you can't use data
validation.

You could use a formula like this:

=IF(SUM(A1:A5)>100,"Error: "&SUM(A1:A5)&" exceeds 100",SUM(A1:A5))

Or, you could use a separate cell:

A10 = SUM formula
A11 = =IF(A10>100,"Error: sum exceeds 100","")
 
S

Steve Wright

I tend to use conditional formatting for this type of thing. You can set
the colour of the cell to red if it is over 1 or green if it is under.
Makes it very easy to see at a glance.
 
J

JICDB

I tried Jim's suggestion, and it didn't work. I think because as Valko
stated the validation doesn't work on sums. I then tried both Valko's
validation formula and Steve's conditional method. I couldn't get either to
work at first but then I realized that I was making the formula relative and
not absolute so it kept moving the range. I finally got both to work but I
think I will opt for the error message because it flags the user and not as
subtle as a color change.

Jim, T.Valko, and Steve - thanks for your help.
 
Top