+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Registered User
    Join Date
    09-26-2016
    Location
    Down, Under
    MS-Off Ver
    2010
    Posts
    2

    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. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    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.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    09-26-2016
    Location
    Down, Under
    MS-Off Ver
    2010
    Posts
    2

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

    Nice work. Works like a charm

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

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

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 05-04-2016, 01:11 AM
  2. [SOLVED] Help With IFAND Formula on Conditional Formatting Range
    By BDBJ1 in forum Excel General
    Replies: 2
    Last Post: 12-16-2015, 11:29 AM
  3. use of ifand And function
    By rajuj in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 08-16-2013, 10:23 PM
  4. I need some help with an IFAnd formula; real confussed.
    By susiesc in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-25-2013, 11:19 AM
  5. Question on IFAND formula in Excel - & is it the best method
    By galvinpaddy in forum Excel General
    Replies: 2
    Last Post: 12-14-2011, 11:47 AM
  6. IF Formula working with work shifts
    By DGutterud in forum Excel General
    Replies: 5
    Last Post: 05-19-2011, 08:23 PM
  7. Gnatt chart - Need help with Ifand formula
    By Kristina1976 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-10-2011, 02:05 PM

Bookmarks

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