formula too long, not sure how to shorten

[Jane]

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,F17=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,F17=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,F17=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,F17=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,F17=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(F17=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,F17=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(F17=7,F17=8,F17=9,F17=10),M$5))

 

[Jane]

CORRECTION - THIS is the formula that is too long and I'm not sure how to
shorten it.... thanks in advance!! jane

'=IF(AND(K$2=1,H17=1,K$3<>"n"),IF(OR(F17=1,F17=2,F17=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,F17=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,F17=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,F17=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,F17=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(F17=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,F17=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(F17=7,F17=8,F17=9,F17=10),M$5))+IF(AND(s$2=9,H17=9,s$3<>"N"),IF(OR(F17=1,F17=2,F17=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(t$2=10,H17=10,t$3<>"N"),IF(OR(F17=1,F17=2,F17=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))

 

[Tim C]

I haven't tested it, but try this:

=(OFFSET(J2,H17,0)=H17)*(OFFSET(J3,H17,0)<>"N")*OFFSET(K5,0,(F17>3)+(F17>6))

Tim C

 

[Jane]

Tim,
Thank you for your quick response!
I'm not familiar with OFFSET function but I will look it up in my book and
then try it..... could I send you my spreadsheet so you see how my long
formula is being used?
jane

 

[Bob Phillips]

Jane,

The way I would do would be to break it down with intermediate calculations,
such as =OR(F17=1,F17=2,F17=3) and then refer to them, or to create some
workbook names (Insert>Name>define Name ...), such as

F17_123 with a value of =OR(F17=1,F17=2,F17=3)
F17_456 with a value of =OR(F17=4,F17=5,F17=6) and
F17_78910 with a value of =OR(F17=7,F17=8,F17=9,F17=10)

make sure the formula cell is selected when you create these, and then
substitute each occurrence of that value with the workbook name.

By very cursory testing suggests that you will still have a problem though,
as it threw out an error.

But there must be a better way than this formula. What is it trying to do?

--

HTH

RP
(remove nothere from the email address if mailing direct)

CORRECTION - THIS is the formula that is too long and I'm not sure how to
shorten it.... thanks in advance!! jane

"N"),IF(OR(F17=1,F17=2,F17=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(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,F17=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(F17=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))+IF(AND(s$2=9,H17=9,s$3<>"N"),IF(OR(F17=1,F17=2
,F17=3),K$5)+IF(OR(F17=4,F17=5,F17=6),L$5)+IF(OR(F17=7,F17=8,F17=9,F17=10),M

 

[Tim C]

Sure. Send away. I suspect that a much simpler formula would suffice in
context.

Tim C

 

[Jane]

could I send my spreadsheet so you can see what I anm trying to accomplish?

 

[Bob Phillips]

I think it would be better to describe it here for two reasons

1) it would make you think about what is actually going on so as to be able
to put it into words (and looking at the formula, I think this would be a
good idea)
2) other NG watchers will see it and you will get a greater potential input

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Jane]

I will also try to include a small print screen if that will help....give me
a few minutes... and I do appreciate the help!

 

[Jane]

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,F17=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]

I should add that I have the first part of the formula "repeated" 9 times for
a total of ten... I shortened it to a total of 8 and it works...it's adding
the 2 "repeats" that gets me into trouble. the following section of the
formula remains the same throughou
IF(OR(F17=1,F17=2,F17=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 there a way to consolidate this piece of the formula?
jane

 

[Bob Phillips]

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(F17>0,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")*J1+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]

Good morning Bob,
I will try your suggestion a little later this morning and will post result.
In regards to what I am trying to do:

I want to pre-plan allocation levels to retail stores that are in any of our
10 regions and have a grade (sales volume) level of 1 - 10.

If the goods are not to be allocated to the Region because of seasonal
conditions (ie no short sleeve shirts to Idaho in January), that Region will
be marked with an "N" in row 3. Otherwise, row 3 wil remain blank. At the
start of the allocation process, the "N" will be placed manually according to
changing conditions.

Additionally, if a store's Grade level is either 1, 2, or 3, the allocation
will be 18 units. If the Grade is 4 - 6, then 12, and so on. The unit level
is in row 5 and will also be changed manually according to the total units
received.

Is this more along the lines of what you needed? Do you need more
information?

I will check the posts at aout 10 am PST. Again, your help is appreciated.
jane

 

[Jane]

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]

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]

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,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 stores Grade using:
=IF(F17>0,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

 

[Jane]

I thought I solved the issue but upon looking back, I see that the issue
ISN"T if the RECEVIED units are different.... the ALLOCATED units are
calculating differently. I'm suspecting there is a ROUNDING issue..... I
will refer to that section to check that out.