+ Reply to Thread
Results 1 to 9 of 9

Iserror works on one column but not two in small nested formula

  1. #1
    Valued Forum Contributor
    Join Date
    08-22-2011
    Location
    Auckland
    MS-Off Ver
    Excel 2019
    Posts
    716

    Iserror works on one column but not two in small nested formula

    Hello People..
    I'm trying to get Iserror to ignore #N/A errors from a chart data area. I'm wanting to count the number of times in a row something happens, that bit I can do. I'm trying to get Iserror to ignore the errors in columns KE6:KE51 & KF6:KF51. I can only get it to ignore one column or the other, not both at the same time. I need to combine the occurrences of "1" in both columns & have them appear in column KB if they are in the other columns. If not leave the cell blank. I'm having to do this as counting a run of entries doesn't work with errors in the column. Hoping this explanation is clear enough
    I've attached an example
    Any ideas please.
    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,447

    Re: Iserror works on one column but not two in small nested formula

    Is
    Please Login or Register  to view this content.
    sthg you can live with?

  3. #3
    Valued Forum Contributor
    Join Date
    08-22-2011
    Location
    Auckland
    MS-Off Ver
    Excel 2019
    Posts
    716

    Re: Iserror works on one column but not two in small nested formula

    Hi Pepe.. I've tried various ways of doing it & can only get it to do 1/2 a job. I was trying to get it to ignore other numbers in the KE column. I could get it to do all the numbers, or get a partial result. Both no good for what I need. I've done it with a helper column & ISNUMBER
    If you can think of a better way, I'm all ears
    I've attached what I've done so you can see what I mean
    Cheers
    Phil
    Attached Files Attached Files

  4. #4
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Iserror works on one column but not two in small nested formula

    I'm not sure what it is that you want.

    If you want a count of N/A's in KE7:KK7 you can use:

    =COUNTIF(KE7:KH7,#N/A)

    and if you want the opposite it would be:

    =4-COUNTIF(KE7:KH7,#N/A)
    or
    =COUNTIF(KE7:KH7,">0")

    COUNTIF() ignores #N/A's
    Last edited by Cutter; 07-06-2012 at 10:52 PM. Reason: Added 2nd formula

  5. #5
    Valued Forum Contributor
    Join Date
    08-22-2011
    Location
    Auckland
    MS-Off Ver
    Excel 2019
    Posts
    716

    Re: Iserror works on one column but not two in small nested formula

    I want to count the latest run on columns KK KL &KM. At the moment that would be 1, as there's no 1's 2's or 3's grouped together. I can do that ok though. I was trying to get the 1's from columns KE & KF to run one after the other in another column (JX or JY), but gave up after trying everything I could think of. I did it with the helper column as I couldn't the other way.
    Probably clear as mud
    Cheers

  6. #6
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Iserror works on one column but not two in small nested formula

    Yep, pretty muddy. Probably my brain, though. I'm about to sign off for the night so I'll have another look at it in the morning.

    Maybe you could upload a file showing the results you want and a clear explanation as to the logic in arriving at those results.

  7. #7
    Valued Forum Contributor
    Join Date
    08-22-2011
    Location
    Auckland
    MS-Off Ver
    Excel 2019
    Posts
    716

    Re: Iserror works on one column but not two in small nested formula

    I set it out a bit better. KO, KP & KQ6 count the runs of 1's 2's & 3's from KK, KL, & KM6:51
    The failed attempts are attempting to replicate the column of 1's in the countable data.
    Hopefully a bit clearer
    Attached Files Attached Files

  8. #8
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Iserror works on one column but not two in small nested formula

    ???????
    =if(countif(ke6:kf6,"<2")=0,"",countif(ke6:kf6,"<2"))
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  9. #9
    Valued Forum Contributor
    Join Date
    08-22-2011
    Location
    Auckland
    MS-Off Ver
    Excel 2019
    Posts
    716

    Re: Iserror works on one column but not two in small nested formula

    Thank you vlady, that's exactly what I've been trying to do. I can see how it works & it's nice & simple
    Cheers
    Phil

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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