Excel, Complicated Question

[sbrylski]

I'm trying to write a formula...

 

I want cell E3 to display "YES" when...

...cell AX contains the max number in the A colum when also BX=B3, C3=CX, and D3=DX

else, E3 displays "NO"

 

It seems like it be some type of LOOKUP statement with multiple criteria (which I'm struggling to accomplish) set to equal the MAX in colum A as my "test" equation in my IF statement. But I am struggling.

 

Let me know if you might be able to help. And let me know if you think you could help yet what I wrote seems to be incoherent, maybe I can clarify somehow.

[rluzinski]

It seems pretty easy, which means I probably don't understand the question completely. This is how I would do it: =IF(AND((AX=MAX(A?:A?)),BX=B3,C3=CX,D3=DX),"y","n")

[sbrylski]

It seems pretty easy, which means I probably don't understand the question completely. This is how I would do it: =IF(AND((AX=MAX(A?:A?)),BX=B3,C3=CX,D3=DX),"y","n")

When I say AX, for example, I mean any cell in A3:A100.

 

So in cell E3, I want it to look at all rows where the B, C, and D equals the contents of B3, C3, and D3. And if in any of those rows, the number in cell A is the max in that colum, then output "YES" in E3. I'm assuming your forumla doesn't do that, by looking at it.

 

[sbrylski]

Actually, I'm dumb, I don't need all that criteria.

 

I just need cell E3 to... find all cells in colum D that equal D3, and if in any of those rows the value in cell A is the max in colum A, then cell E3 should say "YES".

[rluzinski]

Make a new column and check each row using my equation, except have it return 1 or 0. Then in E3, have:

 

=if(sum(new column)>0,"yes","no")

 

I assume that more than 1 row could pass your test, since multiple cells in the A column could contain the max value?

[sbrylski]

Make a new column and check each row using my equation, except have it return 1 or 0. Then in E3, have:

 

=if(sum(new column)>0,"yes","no")

 

I assume that more than 1 row could pass your test, since multiple cells in the A column could contain the max value?

Correct. But the problem is colum D has duplicate values as well.

 

So if A3 contains the max value in the colum, and D3="Jeff"

And A4 doesn't contain the max value in the colum, but D4="Jeff"

I want both E3 and E4 to display "yes".

 

[rluzinski]

Without seeing the actual spreadsheet, I can't seem to sort out exactly what you are aiming for. Email it to me if you like. (rluzinski, hotmail).

[sbrylski]

Without seeing the actual spreadsheet, I can't seem to sort out exactly what you are aiming for. Email it to me if you like. (rluzinski, hotmail).

I might later today.

[sbrylski]

Without seeing the actual spreadsheet, I can't seem to sort out exactly what you are aiming for. Email it to me if you like. (rluzinski, hotmail).

I might later today.

Let's try this, this is what I want:

 

 

 A B C
1 1935 Jeff YES
2 1935 Mike YES
3 1935 Kyle YES
4 1934 Jeff YES
5 1934 Mike YES
6 1934 Bill NO
7 1933 Jeff YES
8 1933 Kyle YES
9 1933 Bill NO 

Obviously the real list is much longer (~2000 rows currently, and will be updated every few weeks), but that's it in general, a name and a year. It'd be possible to do it manually with auto sort, but I'd much prefer a quick forumula.

 

So if a name is listed next to the max year, I want the cell next to that name to display yes everytime that name appears. Is that a little clearer?

[rluzinski]

This is pretty crude but it will work.

 

In C1, put this equation: =IF(MAX(A$1:A$9)=A1,B1,"")

 

Copy it down the colum and hide it.

 

In D1, put this: =IF(B1=VLOOKUP(B1,C$1:C$9,1,FALSE),"yes","no") and copy it down.

 

The problem is that the cells in column D could never return "no", since if VLOOKUP can't find an exact match in column C, it returns the error, #N/A. I assume that it doesn't really matter anyway (just check if it returns "yes") but if it does, you could make yet another column and use: =IF(ISERROR(D1),"no","yes").

 

Of course, there's probably some excel wizard out there pointing at his computer screen and cackling at my solution but hey, it works! http://forum.brewerfan.net/images/smilies/smile.gif I'm sure there's a better way, however.

[sheetswannabe]

Wow, I have no idea what this is. Glad I dont deal with computers all day!

[fondybrewfan]

I cant seem to figure out the differences between the yes's and the no's. I feel like a friggin idiot. Maybe Bill just doesnt want to me a Yes man?

[sbrylski]

That'll work good enough Russ. I want to say I got a single formula to spit out YES correctly, but I deleted it because of the LOOKUP returning an error problem. But screw it, that's plenty good.

 

Thanks.

[rluzinski]

I wouldn't use the iserror function anyway, as it may catch other misc. errors.

 

There has to be a better function than vlookup to look for a match within a column but I have no idea what it would be.