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

LCrompton

New Member
Joined
Jun 18, 2015
Messages
3
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top