I am pre-planning (pre-calculating) Allocation unit levels for retail stores,
of high to low sales volume (Grades), located in any of 10 geo regions. Due
to seasonal issues, some regions do not receive allocations. With the
formulas below, I was able to pre-calculate the Allocation levels by store
and by region using:
=AND(K$2=1,H17=1,K$3<"n")*J1+AND(L$2=2,H17=2,L$3< "N")*J1+AND(M$2=3,H17=3,M$3<"N")*J1+AND(N$2=4,H1 7=4,N$3<"N")*J1+AND(O$2=5,H17=5,O$3<"N")*J1+AND( P$2=6,H17=6,P$3<"N")*J1+AND(Q$2=7,H17=7,Q$3<"N") *J1+AND(R$2=8,H17=8,R$3<"N")*J1

and according to each store's Grade using:
=IF(F170,IF(F17<4,K5,IF(F17<7,L5,IF(F17<11,M5,0)) )) which was placed in
column J. K5, L5, and M5 assigned the unit level per Grade.

I have also calculated prorated unit levels according the actual units
received.
=(K$11)/$E$3)*$F$3 which was placed in K5, l%, and M5

2 problems:
1. I need to limit how low or how high the allocation levels go ie, no less
than 6 units or no more than 18 units. how do I add "but not less than" or
"not more than"

2. If I have 1200 units received, the calculation totals come in at more or
less than I actually received. The actual allocated total must roll up to
match the actual received. I tried using INT where appropriate which brought
my totals closer but they must match.

Any suggestions? Please let me know if you need more information to help
with a solution

Thank you!!