fourmla with result no less than assigned value

[Jane]

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,H17=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(F17>0,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!!

 

[Harlan Grove]

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:

[reformatted]
=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,H17=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

The H17=.. test makes these mutually exclusive - if H17 = 3, say, then H17
can't equal any of the others. I'd suggest replacing this with either of the
following.

If H17 can only be an integer in 1..8,

=IF(AND(INDEX(K$2:R$2,H17)=H17,INDEX(K$3:R$3,H17)<>"N"),J1,0)


If error H17 could be blank or contain garbage,

=IF(OR(H17={1,2,3,4,5,6,7,8}),

and according to each store's Grade using:
=IF(F17>0,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.

This formula returns FALSE when F17<=0. Also, if F17 contained text or
boolean values, F17>0 would evaluate TRUE. I'd suggest replacing this with

=IF(NOT(ISNUMBER(F17)),"",
IF(F17>=11,0,IF(F17>=7,M5,IF(F17>=4,L5,IF(F17>=0,K5)))))

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

By l% do you mean L5?

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"

To restrict x to the interval 6..18, use MAX(6,MIN(18,x)).

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.

Actual units received are in K11 or E3?

Anyway, you're using chunks of K5, L5 or M5, so there's no guarantee the
sums of these chunks will total your units received. For example, if 'A'
gets 2, 'B' gets 3 and 'C' gets 5, 4 stores have grades A, B, A, C, and you
receive 10 units, you can't allocate them in chunks of 2, 3 and 5 according
to grade. In this case, grade chunks would total 12. The simple way to fix
this would be to arbitrarily allocate only 3 untis to the 4th store (grade
C) despite the chunk its grade entitles it to receive.

There's no good way to use Excel or any other spreadsheet to do something
like this. It's intrinsically iterative and chaotic, not amenable to single
pass formulas.

If you have only 8 stores, it'd be expedient to balance to actual units
received manually.