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
• ## Need Help w/Excel 2003 Formula

Posted on Comment on the AskWoody Lounge
Topic Resolution: Resolved
• Author
Posts

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?

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. rjstorms

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)))

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

###### 2 users thanked author for this post. rjstorms, woody

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.

You can also ignore blanks cells like this:

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

###### 1 user thanked author for this post. rjstorms

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.

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. debaser, rjstorms

[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. Kirsty