Data Valisdation on Calculated Value

[masouder]

I have a table where users enter a percentage for each of the 12 months of the year. I am attempting to use data validation to prevent users from entering numbers where the total of all months exceeds 100%. See below.

If users enter a value in C4:C15 that causes the total in C16 ro exceed 100% then I want data validation to alert the user and prevent the entry. I cannot figure out to do this. Any help is appreciated. Thanks!

Book1
	A	B	C	D
1
2
3
4		Jan	5%
5		Feb	6%
6		Mar	7%
7		Apr	4%
8		May	3%
9		Jun	9%
10		Jul	4%
11		Aug	6%
12		Sep	8%
13		Oct	9%
14		Nov	4%
15		Dec	1%
16		Total	66%
17
18
19
Sheet1

Cell Formulas
Range	Formula
C16	C16	=SUM(C4:C15)

 

[DRSteele]

I think this will help. You need to make a custom formula in Data Validation. Select c4:c15 and then create this DV rule.

 

[masouder]

I think this will help. You need to make a custom formula in Data Validation. Select c4:c15 and then create this DV rule.

View attachment 112484

Thanks, DRSteele, this worked.

Question: I originally tried to wrie a custom formula - just as you did - based on the total cell (C16). I wrote is as =$C$16 > 1. It did not work. As I see now fropm your solution that it should be <= 1, not > 1. This does make sense to me: my thinking is that the data validation would kick in when the total exceeds 100%. The <= 1 suggests to me that it would kick in when less or equal to 100%. Can you explain the logic?

 

[DRSteele]

The DV rules kick in when they evaluate as true. The formula type DV must be applied to the range itself and must be absolute references for this technique to work.

 

[masouder]

The DV rules kick in when they evaluate as true. The formula type DV must be applied to the range itself and must be absolute references for this technique to work.

Yes, I understand that it must be applied to the range of values to be netered. I have that working. I'm still stuck, however, on when the rule kicks in. We want the rule to kick in (and the message to display) when the total exceeds 100% so why is it <= 1 instead of > 1?

 

[DRSteele]

Well it seems I misunderstood. Yes, change it from <= to <

So sorry.

 

[awoohaw]

Look at it this way: The data entered must be validated... i.e. it must pass a test. So the SUM of the range cannot exceed 1.
To avoid negative numbers being entered, you may consider an AND function.
so something like the data validation for cell C4:

=AND(C4>=0,SUM($C$4:$C$15)<=1)