News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • Need Help w/Excel 2003 Formula

    Posted on rjstorms Comment on the AskWoody Lounge
    Topic Resolution: Resolved

    This topic contains 9 replies, has 4 voices, and was last updated by

     rjstorms 1 month ago.

    • Author
      Posts
    • #1847010 Reply

      rjstorms
      AskWoody Plus

      I need help with a formula that will tell me the date of when the most recent entry in column N was equal to 0.

      Col N41:N1000 is formatted as number/0 decimal places. Numbers between 0 and 75 are then entered in the next row of column N on a daily basis. The corresponding date is automatically entered in column D. Also, cell AD11 = Max(N42:N1000), which varies over time, and cell AD12 = Min(N42:N1000), which always equals 0.

      The formula I am using that I found on the internet is:

      =LOOKUP(2,1/(N42:N1000=”n”),D42:D1000), where “n” equals AD11 (i.e., variable max). This works fine. But my problem is when “n” equals AD12 (i.e., constant min = 0), the return is 0-Jan-00, not the correct date.

      I’ve found that when AD12 = 0, if I limit my formula to include N41:N-only to the last row with a entry in column N, instead of ending at row N1000, the formula works fine. But I want to keep my formula ending at row N1000.

      How can I fix this?

    • #1847136 Reply

      Kirsty
      Da Boss

      I can see that using blank cells within the LOOKUP formula is giving you an unintended result.

      I suspect you may need to embed an IF function to block out the blank cells. This isn’t a 2003-only issue.

      1 user thanked author for this post.
      • #1847807 Reply

        rjstorms
        AskWoody Plus

        Thanks for your reply, Kirsty. I tried that as follows, but is still does not produce the desired result. It returns a #VALUE! error.
        =IF(D42:D1000=””,””,IF(N42:N1000=””,””,LOOKUP(2,1/(N42:N1000=AE12),D42:D1000)))

    • #1847395 Reply

      zeddy
      AskWoody_MVP

      Hi

      rz-sample-1

      You could use a ‘helper column’, say, column Z

      see formula in cell [Z42]

      ..and copy formula down to cell Z1000

      Then you can use a MAX formula to return the date of when the most recent entry in column N was equal to 0..

      =max(z42:z1000)

      zeddy

      Attachments:
      2 users thanked author for this post.
      • #1847808 Reply

        rjstorms
        AskWoody Plus

        Thank you for your reply, Zeddy. This solution appears that it will return the desired result. However, my spreadsheet is already so large and complicated that I prefer not to have to rely on “helper columns” unless I absolutely have to. Ultimately I may have to. But in the meantime I will continue to try to find another formula solution as I also have additional same-scenario-columns that also require a similar formula.
        Thanks again, Zeddy.

    • #1847504 Reply

      debaser
      AskWoody_MVP

      You can also ignore blanks cells like this:

      =LOOKUP(2,1/((N42:N1000&””)+0=AE12),D42:D1000)

      1 user thanked author for this post.
      • #1847809 Reply

        rjstorms
        AskWoody Plus

        Thanks for your reply, debaser. I am unable to enter your formula in my spreadsheet because I get a notice that there is an error in my formula and the double quotation marks “” following the & are highlighted to indicate where the error is. Can’t figure out what the problem is. I originally thought that your formula was the most promising (other than zeddy’s solution). Perhaps it just needs some tweaking.

        • #1848277 Reply

          Kirsty
          Da Boss

          On my quick test (not using your dataset, of course), I was able to get a desired result using @debaser‘s formula – the blank cells were ignored, and the last date was returned.

          If you are copying and pasting the provided formula into your spreadsheet, try removing the pasted quotation marks, and just type in two quotation marks in their place. That worked in my spreadsheet 😉
          Good luck!

          The differences…
          As pasted:

          =LOOKUP(2,1/((N42:N1000&””)+0=AE12),D42:D1000)

          Result = #N/A

          As edited:

          =LOOKUP(2,1/((N42:N1000&"")+0=AE12),D42:D1000)
          2 users thanked author for this post.
      • #1848972 Reply

        rjstorms
        AskWoody Plus

        [SOLVED]
        Debaser – your formula does work great after all. It just needed to be tweaked a little as explained in Kirsty’s recent follow-up reply.

    • #1848971 Reply

      rjstorms
      AskWoody Plus

      [SOLVED]
      Thank you, Kirsty, for your follow-up reply and suggestion on how to tweak the formula that debaser suggested in his earlier reply. It works great and solves my problem!

      1 user thanked author for this post.

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: Need Help w/Excel 2003 Formula

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.