Excel formula question

October 19, 2011 at 09:39:40
Specs: Windows 7
Hi, I have two columns in my worksheet one with Y's and N's to indicate yes's and no's and the other with corresponding dates. I need a formula that will look in to the the Y/N column and extract the earliest date associated with Y. I thought combining a sumproduct and an if statement in some way might solve the issue but that hasn't worked. Am I going about this all wrong or do I need to do something different?

Thanks!


See More: Excel formula question

Report •


#1
October 19, 2011 at 10:02:05
Provided your data looks like this:

      A         B
1) 10/3/2011	y
2) 10/4/2011	n
3) 10/5/2011	y
4) 10/2/2011	n
5) 10/7/2011	y
6) 10/8/2011	n

Then in cell C1 enter the formula:

=VLOOKUP(MIN(A1:A6),A1:B6,2,FALSE)

EDIT

After re-reading your post, not sure this is what you want.

MIKE

http://www.skeptic.com/


Report •

#2
October 19, 2011 at 10:26:05
Thanks Mike,

So this vlookup returns the the earliest date in column A regardless of whether or not the corresponding cell in column B has the "y" or "n" value. I am looking to return the earliest date from column A related to any cell in column B with only "y" values and not "n" values. Sorry if this was confusing earlier.

Thanks!


Report •

#3
October 19, 2011 at 10:36:08
OK, I kinda thought I got it wrong.
Try this, same setup as above:

=SUMPRODUCT(--(A1:A6=MIN(A1:A6)),--(B1:B6="Y"),A1:A6)

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
October 19, 2011 at 14:42:39
Thanks Mike,

The sumproduct is pulling in 0 for the value instead of the earliest date for "y". Anything i could be doing wrong?

Thanks


Report •

#5
October 19, 2011 at 15:04:30
Sorry, still not right.

It's finding the lowest date,
but if the adjacent cell is not a Y then it returns zero.

I'll work on it.

Again, sorry.

MIKE

http://www.skeptic.com/


Report •

#6
October 19, 2011 at 15:41:28
I don't think your problem can be solved using a Formula, as you need to step thru each date until you find the lowest date with a Y and I can't think of any way to accomplish this using just a formula.

You may need a VBA solution and unfortunately my VBA skills are nil.

Hopefully someone else will be able to assist.

Sorry.

MIKE

http://www.skeptic.com/


Report •

#7
October 19, 2011 at 15:57:56
Not a problem at all, thanks for all of your help on this. I really appreciate it.

Report •

#8
October 20, 2011 at 06:13:19
Try this array entered formula:

=MIN(IF($B$1:$B$6="y",$A$1:$A$6))

Enter the formula using Ctrl-Shift-Enter which will put brackets around the formula. Typing the brackets will not work.

{=MIN(IF($B$1:$B$6="y",$A$1:$A$6))}

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
October 20, 2011 at 12:21:41
DerbyDad03, nice solution, sorry I didn't think of it myself.
but my computer crashed yesterday and I've been a bit distracted trying to get it back on-line.
Working of a small laptop can be annoying.

But, still a great answer.

MIKE

http://www.skeptic.com/


Report •

#10
October 20, 2011 at 12:42:18
re: "sorry I didn't think of it myself"

Google is our friend. ;-)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#11
October 20, 2011 at 15:45:27
DerbyDad thank you. very much appreciated.

Report •

Ask Question