MAX IF is working, but my MIN IF is not...on the SAME data! Help!

[LCrompton]

I am really stumped on this one. I am trying to find the min and max piece of data for particular months.

To do that I am using MAX IF and MIN IF:

=MAX(IF(B:B=F,G:G)
=MIN(IF(B:B=F,G:G)

Literally the only difference is the MIN and MAX part. And to add insult to injury, I have just realized that it isn't working for my AVERAGE IF either now.

Any ideas why it isn't pulling through correctly for the MIN but the MAX is correct?

Thanks,

LCrompton

 

[Gandor]

I believe you would need to confirm with Ctrl - Shift - Enter those equations.

 

[XOR LX]

Hi.

What's F in that formula? It doesn't look like a standard cell reference. Perhaps a Defined Name?

Those formulas I assume are array formulas. Do you realise that, by referencing entire columns, you are forcing Excel to calculate more than a million cells? You cannot be so arbitrary with your choice of reference when using array formulas (and I include the likes of SUMPRODUCT in that definition, despite that formula not requiring CSE).

Unlike e.g. COUNTIF(S)/SUMIF(S), array formulas calculate over all cells passed to them, whether technically beyond the last-used cells in those ranges or not.

Regards

 

[LCrompton]

Hi Gandor, I considered that, but I did not do that with the MAX and it worked. If I do that with the Average, it gives me the same result as the MAX and I dont understand why it is doing that?

 

[LCrompton]

Hi XOR LX. They are technically array formulas. F is the name of the month. Im wondering if this is because my computer is a little slow and it is just taking a long itme to calculate as my MIN is now showing correctly?

 

[XOR LX]

If you don't commit them as array formulas (and, as I said, in that case I would strongly recommend not referencing entire columns), then, if you happen to obtain a correct result, you only do so by pure coincidence.

Why don't you try reducing the ranges being referenced, e.g. to B1:B10 and G1:G10, and post the data which occupies those ranges together with the results of those new formulas?

Regards