# Crazy formula just won't work when add another working formula to it-----IFAND function

1. ## Crazy formula just won't work when add another working formula to it-----IFAND function

This works:

=(M2*N2*3.14)+(O2*D2*3.14)+IF((C2="P"),LOOKUP(D2,{0,0;31,"40";42,"46";54,"50";68,"56";86,"60";106,"66"}),
IF((C2="PT"),LOOKUP(D2,{0,0;31,"40";42,"46";54,"50";68,"56";86,"60";106,"66"})+LOOKUP(G2,{0,0;31,"20";42,"23";54,"25";68,"28";86,"30";106,"33"})+
IF(AND(C2="PT",F2="S"),(G2*E2*3.14*0.15)+(G2*E2*3.14),0)+
IF(AND(C2="PT",F2="L"),(G2*E2*3.14*0.25)+(G2*E2*3.14),0)+
IF(AND(C2="PT",F2="C"),(G2*E2*3.14*0.5)+(G2*E2*3.14*2),0)+
IF(AND(C2="PT",F2="SH"),(G2*E2*3.14*0.75)+(G2*E2*3.14*2),0),
IF((C2="ELB"),(D2*H2*3.14)+LOOKUP(D2,{0,0;31,"40";42,"46";54,"50";68,"56";86,"60";106,"66"}),
IF((C2="RED"),(D2*3.14)+(I2*3.14)+LOOKUP(D2,{0,0;31,"20";42,"23";54,"25";68,"28";86,"30";106,"33"})+LOOKUP(I2,{0,0;31,"20";42,"23";54,"25";68,"28";86,"30";106,"33"}),
IF((C2="OFF"),(D2*2*3.14)+LOOKUP(D2,{0,0;31,"40";42,"46";54,"50";68,"56";86,"60";106,"66"}),0)))))

Might not be perfect but it works and does what I want it to do.

If I try to add this, which works spectacularly alone, I get error codes every which way I turn.

=IF(AND(C3="DMP",D3>3,D3<15),8.75,0)

Like this (second row):

=(M2*N2*3.14)+(O2*D2*3.14)+IF((C2="P"),LOOKUP(D2,{0,0;31,"40";42,"46";54,"50";68,"56";86,"60";106,"66"}),
IF(AND(C2="DMP",D2>3,D2<15),8.75,0),
IF((C2="PT"),LOOKUP(D2,{0,0;31,"40";42,"46";54,"50";68,"56";86,"60";106,"66"})+LOOKUP(G2,{0,0;31,"20";42,"23";54,"25";68,"28";86,"30";106,"33"})+
IF(AND(C2="PT",F2="S"),(G2*E2*3.14*0.15)+(G2*E2*3.14),0)+
IF(AND(C2="PT",F2="L"),(G2*E2*3.14*0.25)+(G2*E2*3.14),0)+
IF(AND(C2="PT",F2="C"),(G2*E2*3.14*0.5)+(G2*E2*3.14*2),0)+
IF(AND(C2="PT",F2="SH"),(G2*E2*3.14*0.75)+(G2*E2*3.14*2),0),
IF((C2="ELB"),(D2*H2*3.14)+LOOKUP(D2,{0,0;31,"40";42,"46";54,"50";68,"56";86,"60";106,"66"}),
IF((C2="RED"),(D2*3.14)+(I2*3.14)+LOOKUP(D2,{0,0;31,"20";42,"23";54,"25";68,"28";86,"30";106,"33"})+LOOKUP(I2,{0,0;31,"20";42,"23";54,"25";68,"28";86,"30";106,"33"}),
IF((C2="OFF"),(D2*2*3.14)+LOOKUP(D2,{0,0;31,"40";42,"46";54,"50";68,"56";86,"60";106,"66"}),0)))))

I keep getting either "too many arguments" or #value errors. It's either this formula I invented is just too intricate for excel to handle and I'm a genius (joke)/excel is dumb or it's something basic and silly that I'm overlooking and I'm a bonehead. I know I'm missing some "0"'s for false returns but I've tried throwing a bunch in and get the same errors and it still doesn't explain why it works one way and not the other. I'd like to throw in about 3-5 more IF functions into this, hopefully I'm not at a limit and therefor it's the previously explained scenario where excel is dumb.

2. ## Re: Crazy formula just won't work when add another working formula to it-----IFAND functio

Welcome to the board.

I would rewrite the first formula like this and adapt from there:

``Please Login or Register  to view this content.``

3. ## Re: Crazy formula just won't work when add another working formula to it-----IFAND functio

Nice work. Works like a charm

4. ## Re: Crazy formula just won't work when add another working formula to it-----IFAND functio

You're welcome.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1