Well, I certainly didn't expect to return to the NG this quickly but...
As I said, the formulas you helped with worked perfectly in my control
situations and, of course, the "real-world" crept in which brings me back...
With the formulas, I was able to pre-plan (pre-calculate) the ALLOCATION
unit level for each store 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 stores Grade using:
=IF(F170,IF(F17<4,K5,IF(F17<7,L5,IF(F17<11,M5,0)) )) which was placed in
column J
The issue arises when the actual purchase order is received and the RECEIVED
unit total is different (+ or -) from the pre-planned ALLOCATION total. If
more or less is received than planned, I want to be able to add or subtract
enough units so that the ALLOCATION and the RECEIVED totals match. Out of
650+ stores, there are plenty of low-volume Grades (7 - 10) which could have
units taken away (in 1's or 2's). On the other end, I would want to add any
additional units to the mid-volume (4 - 6) Grade stores, again in 1's or 2's.
Any suggestions?
again in advance... thank you!
jane
"Bob Phillips" wrote:
LOL.
Glad we sorted it. As with all things, what helps depends on the situation.
Often, dare I say even usually, the regulars here are able to solve the
problem with the sort of info you originally gave. In this case, it was just
not possible to keep extending your formula (as you found yourself). I
sought an idea of the underlying requirement so as to try and give a
different approach (and your last response on that was very good and
informative).
Generally, some details of what is wanted, what the data looks like, and
what you have tried will galvanise the talent around here, and get the best
response.
Look forward to seeing you again.
Regards
Bob
"Jane" wrote in message
...
Bob,
SUCCESS!!
I printed your response and, in my haste given a very busy schedule today,
used your 2nd suggestion of the boorlean multiplication vs the IF(AND. It
worked perfectly! Per my habit, I will save this in my Function
"toolbox".
Thank you again for your time and patience. Advanced warning, you may see
posts in the future and now that I've posted here, will begin my post with
clearer information so the group can more easily provide help.
take good care, jane
"Bob Phillips" wrote:
Not really, it is just a simple statement of the formula, which we can
read
ourselves.
What I really meant was what are you trying to do, design a rocketship,
or a
mortgage calculator, that sort of question? And what are the variables
that
have to be considered (rate, term etc., not K1, F17). I was hoping to
gain
insight into what you were trying to do so that maybe we could suggest a
simpler, more maintainable approach.
Having said that, here is an immediate simplification.
Put this formula in a separate cell, I use J1 in my example
=IF(F170,IF(F17<4,K5,IF(F17<7,L5,IF(F17<11,M5,0)) ))
Then change your massive formula to
=IF(AND(K$2=1,H17=1,K$3<"n"),J1)+IF(AND(L$2=2,H17 =2,L$3<"N"),J2)+IF(AND(M$
2=3,H17=3,M$3<"N"),J1)+IF(AND(N$2=4,H17=4,N$3<"N "),J1)+IF(AND(O$2=5,H17=5,
O$3<"N"),J1)+IF(AND(P$2=6,H17=6,P$3<"N"),J1)+IF( AND(Q$2=7,H17=7,Q$3<"N"),
J1)+IF(AND(R$2=8,H17=8,R$3<"N"),J1)
But it does not overcome the essential problem with the formula, too ma
ny
IFs, you cannot extend it as it stands. That will only be cured by a
different approach.
Just had a thought, we can get rid of the IFs by using a boolean
multiplication. Put the formula in J1 as suggested, and then use
=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")*J 1+AND(R$2=8,H17=8,R$3<"N"
)*J1
This is extendible, by just adding more tests of the form
+AND(test1,test2,test3)*J1
--
HTH
RP
(remove nothere from the email address if mailing direct)
"Jane" wrote in message
...
I wasn't able to do a print screen but here is what I am trying to do:
if k2 = "1" and h17 also = "1" and if K3 does not equal "N" AND if F17
equals either 1, 2, or 3, then give result of 18 BUT IF k2 = "1" and
if
h17
also = "1" and if cell K3 does not equal "N" AND if F17 equals either
4,
5,
or 6, then give result of 12 BUT IF k2 = "1" and if h17 also = "1"
and if
cell K3 does not equal "N" AND if F17 equals either 7, 8, 9, or 10,
then
give
result of 6....
my absolute cells are in row 3: K3 thru T3... K3=1, L3=2, m3=3, and
so
on
my other absolute cells are in row 5: K5 thru M5
'=IF(AND(K$2=1,H17=1,K$3<"n"),IF(OR(F17=1,F17=2,F 17=3),K$5)+IF(OR(F17=4,F17
=5,F17=6),L$5)+IF(OR(F17=7,F17=8,F17=9,F17=10),M$5 ))+
does this clarify?
"Jane" wrote:
my formula is too long and parts are repetitive but I am not sure
how to
shorten it.... any help is appreciated! here it is:
'=IF(AND(K$2=1,H17=1,K$3<"n"),IF(OR(F17=1,F17=2,F 17=3),K$5)+IF(OR(F17=4,F17
=5,F17=6),L$5)+IF(OR(F17=7,F17=8,F17=9,F17=10),M$5 ))+IF(AND(L$2=2,H17=2,L$3<
"N"),IF(OR(F17=1,F17=2,F17=3),K$5)+IF(OR(F17=4,F1 7=5,F17=6),L$5)+IF(OR(F17=
7,F17=8,F17=9,F17=10),M$5))+IF(AND(M$2=3,H17=3,M$3 <"N"),IF(OR(F17=1,F17=2,F
17=3),K$5)+IF(OR(F17=4,F17=5,F17=6),L$5)+IF(OR(F17 =7,F17=8,F17=9,F17=10),M$5
))+IF(AND(N$2=4,H17=4,N$3<"N"),IF(OR(F17=1,F17=2, F17=3),K$5)+IF(OR(F17=4,
F1
7=5,F17=6),L$5)+IF(OR(F17=7,F17=8,F17=9,F17=10),M$ 5))+IF(AND(O$2=5,H17=5,O$3
<"N"),IF(OR(F17=1,F17=2,F17=3),K$5)+IF(OR(F17=4,F 17=5,F17=6),L$5)+IF(OR(F17
=7,F17=8,F17=9,F17=10),M$5))+IF(AND(P$2=6,H17=6,P$ 3<"N"),IF(OR(F17=1,F17=2,
F17=3),K$5)+IF(OR(F17=4,F17=5,F17=6),L$5)+IF(OR(F1 7=7,F17=8,F17=9,F17=10),M$
5))+IF(AND(Q$2=7,H17=7,Q$3<"N"),IF(OR(F17=1,F17=2 ,F17=3),K$5)+IF(OR(F17=4,F
17=5,F17=6),L$5)+IF(OR(F17=7,F17=8,F17=9,F17=10),M $5))+IF(AND(R$2=8,H17=8,R$
3<"N"),IF(OR(F17=1,F17=2,F17=3),K$5)+IF(OR(F17=4, F17=5,F17=6),L$5)+IF(OR(F1
7=7,F17=8,F17=9,F17=10),M$5))
|