+ Reply to Thread
Results 1 to 11 of 11

IF BUT Formula Attempt - Nested IF Help?

  1. #1
    Registered User
    Join Date
    10-21-2016
    Location
    Kansas City, MO
    MS-Off Ver
    2010, 2013
    Posts
    69

    Question IF BUT Formula Attempt - Nested IF Help?

    In my spreadsheet, I am evaluating inventory counts from 2 data files and determining which value to keep in a “Status” column. My “Main Data” data is most likely to be up-to-date, but I need to pull from a “Backup Data” file when “Main Data” data is missing. This seems like a fairly straightforward IF statement to me. BUT if both my “Main Data” and “Backup Data” files list data and that data is different (see row 10 of attached), I need the word “CHECK” to appear in the Status column for someone to investigate. Intuitively, this sounds like an IF BUT formula, but I do not believe there is such a thing. The attached file includes my attempt to create the IF BUT I want, but displays “FALSE” when of the data files is blank instead of pulling from the other column.

    Any help would be greatly appreciated!
    Attached Files Attached Files

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,880

    Re: IF BUT Formula Attempt - Nested IF Help?

    Try this in A2:

    =IF(COUNTA(C2:D2)=1,MAX(C2:D2),IF(C2=D2,D2,"CHECK"))

  3. #3
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    6,746

    Re: IF BUT Formula Attempt - Nested IF Help?

    what happens if both C and D are blank

    otherwise I would do the check first

    =IF(AND(C2<>D2,ISNUMBER(C2),ISNUMBER(D2)),"CHECK", IF(ISNUMBER(C2),C2,D2))
    Wayne

    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  4. #4
    Registered User
    Join Date
    10-21-2016
    Location
    Kansas City, MO
    MS-Off Ver
    2010, 2013
    Posts
    69

    Re: IF BUT Formula Attempt - Nested IF Help?

    Thanks for your guys' help!

    Etaf, your formula works great - I hadn't thought of both C + D being blank but ran into several records with this and rec'd a "0" output. I tried adding an additional IF(AND with ISBLANKS for C + D but was told I had entered too many arguments. Do you know a workaround? If both C + D are blank, I would want my status blank, as well (though I know I could technically replace the 0s in a 2nd formula but was trying to keep it to 1).

    63falcondude, thanks for your help, too - yours would work except I need the Main Data count even if it is lower than the Backup Data count (though this formula will come in handy with other inventory sheets).

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    52,219

    Re: IF BUT Formula Attempt - Nested IF Help?

    Maybe this?

    =IF(AND(C2="",D2=""),"",IF(AND(C2<>D2,ISNUMBER(C2),ISNUMBER(D2)),"CHECK", IF(ISNUMBER(C2),C2,D2)))
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  6. #6
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,880

    Re: IF BUT Formula Attempt - Nested IF Help?

    Quote Originally Posted by eNinjaInTraining View Post
    63falcondude, thanks for your help, too - yours would work except I need the Main Data count even if it is lower than the Backup Data count.
    Did you try it? The MAX part only comes into effect when there is only one entry.

  7. #7
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    6,746

    Re: IF BUT Formula Attempt - Nested IF Help?

    =if( and( c2="",d2=""),"",if(and(c2<>d2,isnumber(c2),isnumber(d2)),"check", if(isnumber(c2),c2,d2))))

  8. #8
    Registered User
    Join Date
    10-21-2016
    Location
    Kansas City, MO
    MS-Off Ver
    2010, 2013
    Posts
    69

    Re: IF BUT Formula Attempt - Nested IF Help?

    Sorry for the late reply - I didn't realize I hadn't written back. You were right - your suggestion works. I got caught in my own understanding of the formula and thought the MAX may pull the larger number from the two programs if both records were populated, even if from the backup data. But that is what the "CHECK" is for, so it wouldn't.

  9. #9
    Registered User
    Join Date
    10-21-2016
    Location
    Kansas City, MO
    MS-Off Ver
    2010, 2013
    Posts
    69

    Re: IF BUT Formula Attempt - Nested IF Help?

    The above is for 63falcondude. Etaf and AliGW, your solutions work as well! Thank you all for your quick feedback - it's especially great that there are several methods I can lean to if I forget one. Apologies on the late feedback from my end.

  10. #10
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,880

    Re: IF BUT Formula Attempt - Nested IF Help?

    You're welcome. Glad we could help.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    52,219

    Re: IF BUT Formula Attempt - Nested IF Help?

    Glad to have helped!

+ 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. [SOLVED] Help with attempt at array formula
    By trolle in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-18-2017, 03:03 AM
  2. Fetch first attempt date and last attempt date
    By Liju144 in forum Access Tables & Databases
    Replies: 1
    Last Post: 02-25-2017, 04:17 AM
  3. Formula issue in spreadsheet every attempt causes wrong value
    By danelliott in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-12-2016, 02:35 PM
  4. Apply formula to a certain column ( attempt made )
    By Tmc2159 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-17-2013, 05:08 PM
  5. Replies: 4
    Last Post: 03-17-2011, 09:24 AM
  6. [SOLVED] 2nd attempt ~ Faster/Shorter formula
    By Luke in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 11-15-2005, 12:35 PM
  7. [SOLVED] 2nd attempt ~ complicated formula
    By Luke in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-08-2005, 11:40 PM

Tags for this Thread

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