MAXIF and MINIF problem

[Fishboy]

Hi All,

Long time reader, first time poster here. Basically I am having an issue with reporting some of the data on one of my massive spreadsheets.

Ultimately what I am looking to do is have one cell report the maximum time taken to achieve a goal (read from column AJ3), but based on the specific location (read from column A). So far I have trawled through the web looking at various different solutions but none of them seem to be working, i.e they either give an error in a popup because it is broken, or it just returns a value of zero even though I know the answer is an actual number.

The kind of code I have been toying with is as follows (NB: The formula is on sheet3, and the data is on sheet1 labelled "Vetting Breakdown" btw):

=MAX(IF('Vetting Breakdown'!$A$3:$A$1048576="Luton",$AJ$3:$AJ$1048576))

My understanding of the above means I expected the formula to:
- Check everything between A3 to the end of Column A on the sheet named "Vetting Breakdown"
- Only count if the cell in column A contains "Luton"
- Then check everything between AJ3 to the end of column AJ and report the highest value

As I say, I already know the maximum value for this should come back as 43, but it is coming back with 0.

Does anyone have any ideas what I am doing wrong, or whether or not I am trying to achieve the impossible? I am having the exact same issue with the MIN version of the above formula as well, so anyhelp would be greatly appreciated.

Many thanks in advance

 

[XOR LX]

Hi.

Firstly, have you ensured that you are committing that formula as an array formula**?

Secondly, I strongly recommend not using entire columns as the range reference, since this will force Excel to calculate over all one million-plus of those cells, whether beyond your last used cell or not. Do you really have data down to the very last row?

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[Fishboy]

Hi Xor,

Firstly, I can at least confirm I have been entering this as an array formula rather than a standard one. With regards to the length of the spreadsheet, no I dont have data all the way down. I suppose I thought I was being clever and future proofing for when more data is added, but I can easily trim the range down to something more sensible.

Do you think this excessive range may be causing the problem?

 

[XOR LX]

Do you think this excessive range may be causing the problem?

Try reducing it and report back!

Cheers

 

[Fishboy]

Right, so I have tried this out on some test data that only has 14 rows, amending the formula as follows:

[COLOR=#333333]=MAX(IF('Vetting Breakdown'!$A$3:$A$14="Luton",$AJ$3:$AJ$4))[/COLOR]

Still not receiving any hard Excel errors on this, but the output value is still showing as 0 when I know it should be 43.

 

[XOR LX]

[COLOR=#333333]=MAX(IF('Vetting Breakdown'!$A$3:$A$14="Luton",$AJ$3:$AJ$[/COLOR][COLOR=#ff0000][B]4[/B][/COLOR][COLOR=#333333]))[/COLOR]

Is that a typo? Shouldn't it be 14?

Also, have you manually verified that the string which is supposed to be "Luton" in that range doesn't e.g. contain any unwanted extra spacing?

Regards

 

[Fishboy]

Ah, fortunately that was just a forum typo rather than a formula typo, but well spotted! I can confirm that the search string of "Luton" is accurate and there is no extra data in the A column. I have even tried adding it as a wildcard "*Luton*" just in case, but still no joy.

 

[Fishboy]

I've just had a thought.....

The cell in the AJ column which I am trying to get the MAX value from is actually a formula itself:

=IF(OR(ISBLANK(AI3),ISBLANK(R3)),"",AI3-R3)

It is basically ignoring if cell AI3 and / or R3 are blank, otherwise it is subtracting the start date from the end date and giving the number of days. It is this number of days I am trying to MAXIF count. Will the MAXIF array formula not work if technically the contents of the source is a formula?

 

[XOR LX]

Will the MAXIF array formula not work if technically the contents of the source is a formula?

It makes no difference whatsoever. Providing there is at least one entry in the searched range which contains the precise string "Luton", then the maximum of those numbers will be returned.

Regards

 

[Fishboy]

Thanks Xor, at least I know this should be working

All I need to do now is figure out why it isn't!