Null values issues. Alternative for my Max(if) array?

[kbrownk]

I'm using {=MAX(IF(K$2:K$6000=K2,O$2:O$6000,""))}
for each cell in column P (P2 in the example above, where each cell is it's own array). It appears to take issue with null cells I have in both columns K and O. I don't get an error or crashing, but odd things happen.

As I can't find any simple way to ignore null values in cells, I assume none exists, at least for Excel 2007 (maybe 2010?). So, anyone have ideas for an alternative Max formula?

Specifically, I am asking to return the max value from the set of all cells in Col O that have the value in K2 in the same row. As mentioned, this would be the formula in cell P2. At P3, I'd be asking for the max value in Col O that has the value K3 in the same row, etc.

One more note: If I manually Ctrl-Shift-Enter in each cell in column P it gives the correct answer! I don't know why it can't do this automatically when I update (driving me crazy).

I'd really appreciate any help! I get a lot of advice from this site, so much so that I've never had to ask my own questions!

Thanks,
kbrownk

 

[Aladin Akyurek]

=MAX(IF(K$2:K$6000=K2,O$2:O$6000))

should suffice. The formula must be confirmed with control+shift+enter (CSE), not just enter, and then copied down.

It looks like you are going to copy down this formula to 6000 cells. This would be inefficient (take to much time). A better approach would be:

Select K1:K6000 (including the header.
Fire up Advanced Filter with the option Copy to another location and Uniqe records only checked. Copy the filter result in column Q. Now in R2, you can invoke:

=MAX(IF(K$2:K$6000=Q2,O$2:O$6000))

to be confirmed with CSE and copied down.

 

[kbrownk]

I've never used advanced filter. What specifically am I putting in the List Range and Criteria Range? Am I selecting Q2:Q6000 for the copy range? How do I invoke in R2?

I should mention that, even though inefficiency may be the problem, Excel doesn't crash and even seems to complete calculations when I copy the array (and I do use Ctrl-Shift-Enter when I initially type the formula in). It just doesn't seem to update the values, unless I do so myself for each individual cell. This is true for Automatic calculation and for manual using F9 to initialize.

I'll read up on advanced filter. Thanks for the help.

kbrownk

 

[kbrownk]

I figured out the solution. If I stare very hard at the cells I want to update while the calculations run they come out right. Seriously though I did find the solution but not sure what the underlying issue was yet. I started writing my guess, but I'm going to explore a little more and reply once I figure it out. In short, I believe it was not fully updating due to getting circular reference warnings, though I've yet to find an actual circular reference for any of the cells the warning points me to.

Thanks,
kbrownk

 

[kbrownk]

Here's my take on what the issue was for ayone interested. I bolded the essential part if you want to skip over the details.

The issue appears to have been related to one of a few columns I had that were causing the calculations to break w/o informing me (though I guess the circular ref was a hint). The formulas all had something similar to below:

=IF(ISNUMBER(J3077),J3077,IF(AND(H3077>2,ISNUMBER(VLOOKUP(G3077,A$1:K$6000,11,FALSE))),VLOOKUP(G3077,A$1:K$6000,11,FALSE),IF(AND(H3077>2,ISNUMBER(VLOOKUP(G3077,A$1:J$6000,10,FALSE))),VLOOKUP(G3077,A$1:J$6000,10,FALSE),"")))

Note that fist criteria says to put a null value if there is no number in cell J3077. The J col has a similar isnumber but for col I. Same thing for I which has an isnumber to col H. H's criteria for not being null is for G to be >2. Col G didn't have an 'else null' option but instead just had the following:

IF(OR(B3077=2,B3077=13,B3077=4,B3077=14,B3077=7,B3077=16,B3077=6,B3=077=15),2,B3077)

For some reason this didn't put any values in cells where no numbers existed in Col B so I assumed they were null like the others. Not sure how you get a circular ref out of this, but so be it.

So I think that was the problem.


Thanks for the help. I'm very glad to be registered here now.

kbrownk

 

[Aladin Akyurek]

=IF(ISNUMBER(J3077),J3077,
IF(AND(H3077>2,ISNUMBER(VLOOKUP(G3077,A$1:K$6000,11,FALSE))),
VLOOKUP(G3077,A$1:K$6000,11,FALSE),
IF(AND(H3077>2,ISNUMBER(VLOOKUP(G3077,A$1:J$6000,10,FALSE))),
VLOOKUP(G3077,A$1:J$6000,10,FALSE),"")))

can be shortened to:

=IF(ISNUMBER(J3077),J3077,
     IF(H3077>2, LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3},0,
       VLOOKUP(G3077,A$1:K$6000,11,0),VLOOKUP(G3077,A$1:J$6000,10,0))),0))

My earlier remarks regarding the MAX/IF formula still holds.

Note on Advanced Filter.

Check Copy to another location.
List range: K$1:K$6000 (K1 must have a header)
Criteria range must be left empty.
For Copy to, select Q2.
Check Unique records only.
Click OK.

 

[kbrownk]

Always good to have new functions in my toolbox. I'll definitely gives these a shot.

Thanks!
kbrownk

 

[Aladin Akyurek]

Always good to have new functions in my toolbox. I'll definitely gives these a shot.

Thanks!
kbrownk

You are welcome.