Check a budget cap, allocation total, and order-pack multiples

Separate audit checks from enforced validation

The interactive example evaluates formulas in cells. It does not automatically install a validation rule, block edits, or validate a server-side submission. Configure and test the application’s validation feature separately when enforcement is required.

Absolute references keep the budget and weight ranges fixed. A cap-only check does not reject negative budgets, and a sum-to-one check alone can accept negative weights. The combined formula below also requires numeric, nonnegative budgets and weights between zero and one.

Rounding the allocation sum to six decimal places defines a tolerance of about half a millionth; it is not an exact-equality test. Choose a tolerance appropriate to your reporting requirements.

The combined gate covers budgets and weights only. Pack quantities are checked separately for each row. Enforcing a complete 100% allocation during every intermediate edit can make entry difficult; a submission-time audit is often more practical.

Combined budget and allocation audit

Checks all three budget inputs and all three weights. Pack quantities are outside this combined check.

=AND(COUNT(B2:B4)=3,MIN(B2:B4)>=0,ISNUMBER(E2),E2>=0,SUM(B2:B4)<=E2,COUNT(D2:D4)=3,MIN(D2:D4)>=0,MAX(D2:D4)<=1,ROUND(SUM(D2:D4),6)=1)

Validate positive whole case-pack multiple (PackSize = 12)

Rejects zero, negative numbers, decimals, and partial cartons.

=IF(ISNUMBER(C2),AND(C2>0,MOD(C2,12)=0),FALSE)

Further reading

• Microsoft Support — Apply data validation to cells