+ Reply to Thread
Results 1 to 11 of 11

iferror to be used twise as part of an if statement

  1. #1
    Registered User
    Join Date
    06-13-2023
    Location
    London
    MS-Off Ver
    365
    Posts
    18

    iferror to be used twise as part of an if statement

    Hi all

    difficult question as im not able to post the work excel document

    Basically i am trying to use a second iferror statement inserted in the "value if false") part of the if statement (i have separated out below by using spaces)

    The reason being is that i get a False error

    =IFERROR(IF($G$2="Company",(IF(AND(P$6>=$V$2,P$6<=$Y$2),(INDEX('Finance Calculation tab'!$S$13:$CP$52436,MATCH('Detailed Man Accounts (SAP)'!$B160,'Finance Calculation tab'!$C$13:$C$52436,0),MATCH('Detailed Man Accounts (SAP)'!P$5,'Finance Calculation tab'!$S$2:$CP$2,0))*1000/P66))) ,(IFERROR((P178*1000)/(P126-P81),""))),"")

    i know this is a crap framed question but im getting frustrated with this and thought id ask

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,472

    Re: iferror to be used twise as part of an if statement

    We don't need to see the actual live file, just a sample/simulation of it so we can see the sheets, data layout, and expected results.

    Without that, there's a lot of guesswork and a lot of effort building a test environment to check and correct your formula.

    In the absence of anything to work with, I’d say remove the IFERROR functions and check the different parts of the formula separately. For example, put each of the MATCH functions in separate cells and see if they return what you expect, value or error. Then check the INDEX with the values returned from the MATCH functions, again, value or error. Then you know what's going wrong and where. Once you've done that, you can build a composite formula.

    Happy to do that but not build the test rig for it.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,626

    Re: iferror to be used twise as part of an if statement

    Your formula is equivalent to:
    =IFERROR(IF($G$2="Company",IF(AND(P$6>=$V$2,P$6<=$Y$2),(INDEX(Sheet1!$S$13:$CP$52436,MATCH(Sheet1!$B160,Sheet1!$C$13:$C$52436,0),MATCH(Sheet1!P$5,Sheet1!$S$2:$CP$2,0))*1000/P66),"Here you got false"),IFERROR((P178*1000)/(P126-P81),"")),"")


    In other words in IF(AND(P$6>=$V$2,P$6<=$Y$2) you have only true part and no false part. But if AND(P$6>=$V$2,P$6<=$Y$2) is not met then IF returns FALSE. And FALSE is not an error, so is ignored by IFERROR and final result is returned as FALSE
    Last edited by Kaper; 01-04-2024 at 07:22 AM.
    Best Regards,

    Kaper

  4. #4
    Registered User
    Join Date
    06-13-2023
    Location
    London
    MS-Off Ver
    365
    Posts
    18

    Re: iferror to be used twise as part of an if statement

    HI

    its just the last element of the formula which is the issue

    =IFERROR(IF($G$2="Company",(IF(AND(P$6>=$V$2,P$6<=$Y$2),(INDEX('Finance Calculation tab'!$S$13:$CP$52436,MATCH('Detailed Man Accounts (SAP)'!$B160,'Finance Calculation tab'!$C$13:$C$52436,0),MATCH('Detailed Man Accounts (SAP)'!P$5,'Finance Calculation tab'!$S$2:$CP$2,0))*1000/P66))) ,(IFERROR((P178*1000)/(P126-P81),""))),"")

    (IFERROR((P178*1000)/(P126-P81)

    This part as it contains a division by zero which returns a ref error, i just wanted to put another iferror in to negate this but i cant seem to do it

  5. #5
    Registered User
    Join Date
    06-13-2023
    Location
    London
    MS-Off Ver
    365
    Posts
    18

    Re: iferror to be used twise as part of an if statement

    previously it was


    ,((P178*1000)/(P126-P81),""))),"") but this returns a false

    i tried to put in

    ,(IFERROR((P178*1000)/(P126-P81),""))),"")

    but it doesnt work

  6. #6
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,626

    Re: iferror to be used twise as part of an if statement

    Im'not sure if you noted my answer above. I think FALSE comes from other part of formula than you think.
    If yes, you have seen - then I'll support TMS suggestion : spend some 10 minutes preparing a workbook free of any confidentional data (names converted to BRown, Black, Blue, companies to alpha Co, Beta Co etc. and show expected results

  7. #7
    Registered User
    Join Date
    06-13-2023
    Location
    London
    MS-Off Ver
    365
    Posts
    18

    Re: iferror to be used twise as part of an if statement

    this is exactly what im getting is FALSE

  8. #8
    Registered User
    Join Date
    06-13-2023
    Location
    London
    MS-Off Ver
    365
    Posts
    18

    Re: iferror to be used twise as part of an if statement

    how would i get the false to turn into "", is this possible?

  9. #9
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,626

    Re: iferror to be used twise as part of an if statement

    just:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    06-13-2023
    Location
    London
    MS-Off Ver
    365
    Posts
    18

    Re: iferror to be used twise as part of an if statement

    @kaper...

    Seriosuly mate...does it hurt?? having such a big brain!!!!!!!!!!!!!!!!!!!!!

    You are a legend, thank you muchly

    worked a treat, i cant get my head around multiple nests it confuses me

    Thank you sir

  11. #11
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,626

    Re: iferror to be used twise as part of an if statement

    Thanks for kind words :D and for a reputation point as well as for marking thread solved.
    Have a good day.
    If I fall into problems with deeply nested nested formulas I usually try to copy the formula (editing a formula bar) into another cell (or cells if outermost function has a number of arguments and remove this outermost function . Then repeat this approach with next level nested .

+ 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. IF statement, IFERROR or both?
    By Plugin in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-15-2020, 11:39 AM
  2. [SOLVED] IFERROR match part of text value with the use of COUNTIFS AND SUMIFS
    By soreno in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-26-2019, 05:11 PM
  3. How to create a if statement with a iferror statement
    By ronald.burke in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-09-2019, 07:00 AM
  4. Iferror + if statement
    By eggselent in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-23-2019, 11:56 AM
  5. Nested IF statement validating part 1 but not part 2 of conditional
    By methuselah90 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-08-2014, 02:39 PM
  6. Replies: 1
    Last Post: 01-26-2014, 05:37 PM
  7. 3 part IFERROR formula
    By rwmeis in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-16-2013, 11:33 AM

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