+ Reply to Thread
Results 1 to 12 of 12

help with IFS statement

  1. 03-21-2018, 11:38 AM #1
    rayrickson
    rayrickson is offline
    Registered User
    Join Date
    03-21-2018
    Location
    san francisco, California
    MS-Off Ver
    2016
    Posts
    4

    help with IFS statement

    hi all!

    any chance someone could tell me what might be wrong with formula, for which i keep getting the "#NAME?" error?

    thanks in advance if anyone has a free moment to help a newb...

    =IFs(AND(C31=2,C30=1,C32=1),31,AND(C31=1,C30=2,C32=2),1,AND(C31=2,C30=2,C29=1,C32=1),32,AND(C31=1,C30=1,C29=2,C32=2),2,AND(C31=2,C30=2,C29=2,C28=1,C32=1),33,AND(C31=1,C30=1,C29=1,C28=2,C32=2),3,AND(C31=2,C30=2,C29=2,c28=2,C27=1,C32=1),34,AND(C31=1,C30=1,C29=1,c28=1,C27=2,C32=2),4,AND(C31=2,C30=2,C29=2,c28=2,c27=2,C26=1,C32=1),35,AND(C31=1,C30=1,C29=1,c28=1,c27=1,C26=2,C32=2),5,AND(C31=2,C30=2,C29=2,c28=2,c27=2,c26=2,C25=1,C32=1),36,AND(C31=1,C30=1,C29=1,c28=1,c27=1,c26=1,C25=2,C32=2),6,AND(C31=2,C30=2,C29=2,c28=2,c27=2,c26=2,c25=2,C24=1,C32=1),37,AND(C31=1,C30=1,C29=1,c28=1,c27=1,c26=1,c25=1,C24=2,C32=2),7,AND(C31=2,C30=2,C29=2,c28=2,c27=2,c26=2,c25=2,c24=2,C23=1,C32=1),38,AND(C31=1,C30=1,C29=1,c28=1,c27=1,c26=1,c25=1,c24=1,C23=2,C32=2),8,AND(C31=2,C30=2,C29=2,c28=2,c27=2,c26=2,c25=2,c24=2,c23=2,C22=1,C32=1),39,AND(C31=1,C30=1,C29=1,c28=1,c27=1,c26=1,c25=1,c24=1,c23=1,C22=2,C32=2),9,AND(C31=2,C30=2,C29=2,c28=2,c27=2,c26=2,c25=2,c24=2,c23=2,c22=2,C21=1,C32=1),40,AND(C31=1,C30=1,C29=1,c28=1,c27=1,c26=1,c25=1,c24=1,c23=1,c22=1,C21=2,C32=2),10,AND(C31=2,C30=2,C29=2,c28=2,c27=2,c26=2,c25=2,c24=2,c23=2,c22=2,c21=2,C20=1,C32=1),41,AND(C31=1,30=1,C29=1,c28=1,c27=1,c26=1,c25=1,c24=1,c23=1,c22=1,c21=1,C20=2,C32=2),11,AND(C31=2,C30=2,29=2,c28=2,c27=2,c26=2,c25=2,c24=2,c23=2,c22=2,c21=2,c20=2,C19=1,C32=1),42,AND(C31=1,30=1,C29=1,c28=1,c27=1,c26=1,c25=1,c24=1,c23=1,c22=1,c21=1,c20=1,C19=2,C32=2),12,AND(C31=2,C30=2,29=2,c28=2,c27=2,c26=2,c25=2,c24=2,c23=2,c22=2,c21=2,c20=2,C19=2,C32=1),43,AND(C31=1,30=1,C29=1,c28=1,c27=1,c26=1,c25=1,c24=1,c23=1,c22=1,c21=1,c20=1,C19=1,C32=2),13)
    Reply With QuoteRegister To Reply
  2. 03-21-2018, 11:41 AM #2
    Special-K
    Special-K is offline
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,064

    Re: help with IFS statement

    #NAME indicates a formula does not exist.

    Have you used IFS before, ie can we be sure it's part of your version of Excel?

    Maybe formula is too long?
    Last edited by Special-K; 03-21-2018 at 11:45 AM.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
    Reply With QuoteRegister To Reply
  3. 03-21-2018, 11:43 AM #3
    PaulM100
    PaulM100 is offline
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: help with IFS statement

    See here examples on nested IF. There is no formula named IFs

    https://exceljet.net/formula/nested-if-function-example
    Reply With QuoteRegister To Reply
  4. 03-21-2018, 11:49 AM #4
    Special-K
    Special-K is offline
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,064

    Re: help with IFS statement

    A LOOKUP table would be better
    Reply With QuoteRegister To Reply
  5. 03-21-2018, 11:51 AM #5
    Special-K
    Special-K is offline
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,064

    Re: help with IFS statement

    Quote Originally Posted by PaulM100 View Post
    See here examples on nested IF. There is no formula named IFs

    https://exceljet.net/formula/nested-if-function-example
    I think you'll find there is

    https://www.google.co.uk/search?safe...85._l34Dd80PIs
    Reply With QuoteRegister To Reply
  6. 03-21-2018, 11:52 AM #6
    rayrickson
    rayrickson is offline
    Registered User
    Join Date
    03-21-2018
    Location
    san francisco, California
    MS-Off Ver
    2016
    Posts
    4

    Re: help with IFS statement

    thanks for the responses!

    um I am using office 2016 and supposedly there is a new IFS formula for 2016? at least thats what google said. what is a lookup table? thats sounds cool
    Reply With QuoteRegister To Reply
  7. 03-21-2018, 11:53 AM #7
    PaulM100
    PaulM100 is offline
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: help with IFS statement

    Quote Originally Posted by Special-K View Post
    I think you'll find there is

    https://www.google.co.uk/search?safe...85._l34Dd80PIs
    Oh, I had no idea that there was one. My bad.
    Reply With QuoteRegister To Reply
  8. 03-21-2018, 12:01 PM #8
    Special-K
    Special-K is offline
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,064

    Re: help with IFS statement

    I agree with 2016, unfortunately Im using 2013 so am getting a #NAME.

    Instead of all the IFS and ANDs you could use a VLOOKUP but not sure if this would work as your AND conditions dont use all of the cells, e.g.

    AND(C31=1,C30=2,C32=2),1,
    but you also have
    AND(C31=1,30=1,C29=1,C28=1,C27=1,C26=1,C25=1,C24=1,C23=1,C22=1,C21=1,C20=1,C19=1,C32=2),13

    I'll have a think
    Reply With QuoteRegister To Reply
  9. 03-21-2018, 12:03 PM #9
    rayrickson
    rayrickson is offline
    Registered User
    Join Date
    03-21-2018
    Location
    san francisco, California
    MS-Off Ver
    2016
    Posts
    4

    Re: help with IFS statement

    before i was using regular type if and statements like this.... which i kind of got to work.. ish.

    =IF(or(AND(C31=2,C30=1,C32=1),31,IF(AND(C31=1,C30=2,C32=2),1,IF(AND(C31=2,C30=2,C29=1,C32=1),32,IF(AND(C31=1,C30=1,C29=2,C32=2),2,IF(AND(C31=2,C30=2,C29=2,C28=1,C32=1),33,IF(AND(C31=1,C30=1,C29=1,C28=2,C32=2),3,IF(AND(C31=2,C30=2,C29=2,c28=2,C27=1,C32=1),34,IF(AND(C31=1,C30=1,C29=1,c28=1,C27=2,C32=2),4,IF(AND(C31=2,C30=2,C29=2,c28=2,c27=2,C26=1,C32=1),35,IF(AND(C31=1,C30=1,C29=1,c28=1,c27=1,C26=2,C32=2),5,IF(AND(C31=2,C30=2,C29=2,c28=2,c27=2,c26=2,C25=1,C32=1),36,IF(AND(C31=1,C30=1,C29=1,c28=1,c27=1,c26=1,C25=2,C32=2),6,IF(AND(C31=2,C30=2,C29=2,c28=2,c27=2,c26=2,c25=2,C24=1,C32=1),37,IF(AND(C31=1,C30=1,C29=1,c28=1,c27=1,c26=1,c25=1,C24=2,C32=2),7,IF(AND(C31=2,C30=2,C29=2,c28=2,c27=2,c26=2,c25=2,c24=2,C23=1,C32=1),38,IF(AND(C31=1,C30=1,C29=1,c28=1,c27=1,c26=1,c25=1,c24=1,C23=2,C32=2),8,IF(AND(C31=2,C30=2,C29=2,c28=2,c27=2,c26=2,c25=2,c24=2,c23=2,C22=1,C32=1),39,IF(AND(C31=1,C30=1,C29=1,c28=1,c27=1,c26=1,c25=1,c24=1,c23=1,C22=2,C32=2),9,IF(AND(C31=2,C30=2,C29=2,c28=2,c27=2,c26=2,c25=2,c24=2,c23=2,c22=2,C21=1,C32=1),40,IF(AND(C31=1,C30=1,C29=1,c28=1,c27=1,c26=1,c25=1,c24=1,c23=1,c22=1,C21=2,C32=2),10,IF(AND(C31=2,C30=2,C29=2,c28=2,c27=2,c26=2,c25=2,c24=2,c23=2,c22=2,c21=2,C20=1,C32=1),41,IF(AND(C31=1,30=1,C29=1,c28=1,c27=1,c26=1,c25=1,c24=1,c23=1,c22=1,c21=1,C20=2,C32=2),11,IF(AND(C31=2,C30=2,29=2,c28=2,c27=2,c26=2,c25=2,c24=2,c23=2,c22=2,c21=2,c20=2,C19=1,C32=1),42,IF(AND(C31=1,30=1,C29=1,c28=1,c27=1,c26=1,c25=1,c24=1,c23=1,c22=1,c21=1,c20=1,C19=2,C32=2),12,IF(AND(C31=2,C30=2,29=2,c28=2,c27=2,c26=2,c25=2,c24=2,c23=2,c22=2,c21=2,c20=2,C19=2,C32=1),43,IF(AND(C31=1,30=1,C29=1,c28=1,c27=1,c26=1,c25=1,c24=1,c23=1,c22=1,c21=1,c20=1,C19=1,C32=2),13,””))))))))))))))))
    Reply With QuoteRegister To Reply
  10. 03-21-2018, 12:07 PM #10
    63falcondude
    63falcondude is offline
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: help with IFS statement

    Note that IFS is only available with an Office 365 subscription.
    Reply With QuoteRegister To Reply
  11. 03-21-2018, 12:09 PM #11
    rayrickson
    rayrickson is offline
    Registered User
    Join Date
    03-21-2018
    Location
    san francisco, California
    MS-Off Ver
    2016
    Posts
    4

    Re: help with IFS statement

    ok thank you 63. it it true you can only have 7 nested "if" statements?
    Last edited by rayrickson; 03-21-2018 at 12:12 PM.
    Reply With QuoteRegister To Reply
  12. 03-21-2018, 12:21 PM #12
    Special-K
    Special-K is offline
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,064

    Re: help with IFS statement

    If the values in those cells are only gonna be 0 or 1 or 2 this should work.

    On a blank sheet create a two column table

    Please Login or Register  to view this content.
    Then use

    =VLOOKUP(C19&C20&C21&C22&C23&C24&C25&C26&C27&C28&C29&C30&C31&C32,Sheet1!A$1:B$26,2,0)
    to get the result
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] i have an if then statement that works, how to make it work as a with or case statement
    By dmcgov in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 06-03-2016, 03:34 PM
  2. [SOLVED] If statement to select data - nested statement - assistance
    By petitesouris in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-29-2015, 09:55 PM
  3. compile error expected line number statement end statement
    By mattress58 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-18-2014, 10:12 AM
  4. VBA Compile Error : line number or label or statement or end of statement
    By excellearner121 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-04-2013, 06:41 PM
  5. [SOLVED] Convert If statement with cell row reference calculations to vlookup statement
    By excyclist in forum Excel General
    Replies: 4
    Last Post: 06-01-2012, 10:05 AM
  6. VBA- Compile Error, Expected: line number or label or statement or end of statement
    By RLK7 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-16-2012, 05:33 PM
  7. [SOLVED] Utilize a Select Case Statement in Target Intersect Statement
    By max57 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-29-2009, 08:55 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1