if(and( Getting past the seven IF's

[MattHavoc]

Hello guys,

I have the following problem:
I have a 3 page workbook, in which i need to sum two cells based on tw
different criteria. One of those criteria maintains its value to bein
either true or false, however the other can vary between one of fiv
different states. Each state then represents a number by which th
data will be summed by.

I have placed both values to appear (as chosen by the user) in separat
cells (so the formula has to check to see which of the criteria i
located in the cell, for each option)

Now if Excel would allow more than seven IF statements I would have n
problem, but because it does, I do.

Here is my current non-working formula:

=IF(AND(D57=1,Quick!G55=TRUE),SUM(Quick!K4),IF(AND(D57=2,Quick!G55=TRUE),Quick!J4*L63,IF(AND(D57=3,Quick!G55=TRUE),Quick!J4*L64,iF(AND(D57=1,Quick!G55=FALSE),SUM(Standard!B4:C7),IF(AND(D57=2,Quick!G55=FALSE),Standard!B3*L63,IF(AND(D57=3,Quick!G55=FALSE),Standard!B3*L64,IF(AND(D57=4,Quick!G55=TRUE),Quick!J4*L67,IF(and(D57=5,Quick!G55=TRUE),Quick!J4*L68,IF(AND(D57=4,Quick!G55=FALSE),Standard!B3*L67,IF(AND(D57=5,Quick!G55=FALSE),Standard!B3*L68,0))))))))))

D57 is the cell in which 1 of 5 different values will appear based o
user option (drop-down menu)

Quick!G55 is the third page of the book in which the user clicks a bo
to indicate that data from that page, rather than page 2, should b
used in the formula.
L63 is the cell in which the number that represents the users choic
resides.

Please offer your opinions on a work around for this. I am at a loss.

Thanks,
Mat

 

[Frank Kabel]

Hi Matt
hope I got all your conditions right. Try (not tested):
=IF(D57=1,IF(Quick!G55,Quick!K4,SUM(Standard!B4:C7)),IF(AND(Quick!G55,O
R(D57>=2,D57<=5)),Quick!J4*OFFSET(L63,D57-2+2*(D57>=4),0),IF(AND(Quick!
G55=false,OR(D57>=2,D57<=5)),Standard!B3*OFFSET(L63,D57-2+2*(D57>=4),0)
,0)))


--
Regards
Frank Kabel
Frankfurt, Germany

[...]

 

[hgrove]

MattHavoc wrote...
...

Here is my current non-working formula:
[reformatted]

=IF(AND(D57=1,Quick!G55=TRUE),SUM(Quick!K4),
IF(AND(D57=2,Quick!G55=TRUE),Quick!J4*L63,
IF(AND(D57=3,Quick!G55=TRUE),Quick!J4*L64,
iF(AND(D57=1,Quick!G55=FALSE),SUM(Standard!B4:C7),
IF(AND(D57=2,Quick!G55=FALSE),Standard!B3*L63,
IF(AND(D57=3,Quick!G55=FALSE),Standard!B3*L64,
IF(AND(D57=4,Quick!G55=TRUE),Quick!J4*L67,
IF(and(D57=5,Quick!G55=TRUE),Quick!J4*L68,
IF(AND(D57=4,Quick!G55=FALSE),Standard!B3*L67,
IF(AND(D57=5,Quick!G55=FALSE),Standard!B3*L68,
0))))))))))

...

First, reorder the terms.

=IF(AND(D57=1,Quick!G55=TRUE),SUM(Quick!K4),
IF(AND(D57=2,Quick!G55=TRUE),Quick!J4*L63,
IF(AND(D57=3,Quick!G55=TRUE),Quick!J4*L64,
IF(AND(D57=4,Quick!G55=TRUE),Quick!J4*L67,
IF(AND(D57=5,Quick!G55=TRUE),Quick!J4*L68,
iF(AND(D57=1,Quick!G55=FALSE),SUM(Standard!B4:C7),
IF(AND(D57=2,Quick!G55=FALSE),Standard!B3*L63,
IF(AND(D57=3,Quick!G55=FALSE),Standard!B3*L64,
IF(AND(D57=4,Quick!G55=FALSE),Standard!B3*L67,
IF(AND(D57=5,Quick!G55=FALSE),Standard!B3*L68,
0))))))))))

Next, notice that for D57 == 2..5, all that differs between Quick!G5
True or False is wether you use Quick!J4 or Standard!B3, respectively
Only D57 == 1 presents special cases. Take advantage of these.

=IF(D57=1,IF(Quick!G55,Quick!K4,SUM(Standard!B4:C7)),
IF(Quick!G55,Quick!J4,Standard!B3)*((D57=2)*L63+(D57=3)*L64
+(D57=4)*L67+(D57=5)*L68)

 

[MattHavoc]

WOW!

I am a self taught Excel user. I always think I know a lot until
visit this forum...lol

I think both will work, tho i don't truely understand either in full.
will have to study it, try it out, and let you know.

I sure appriciate your time and assistence. Thanks a lot guys

 

[MattHavoc]

ok, I had a chance to go over them.

Mr. hgrove hit on the head. It's working perfectly!

Thanks so much. It's always fun to learn new things, too bad it'
typically a problem that inspires us.

(I need more time to understand Mr Kabel's, its interesting, and no
something I am too familiar with.

 

[Frank Kabel]

Hi
maybe my solution is interesting but Harlan's is shorter
What you an do, is combine both approaches. So try:


=IF(D57=1,IF(Quick!G55,Quick!K4,SUM(Standard!B4:C7)),
IF(Quick!G55,Quick!J4,Standard!B3)*OFFSET(L63,D57-2+2*(D57>3))*(OR(D57=
{2,3,4,5})))