Previous Topic Next Topic

Finding MAX value with criteria list

Solved/Closed
RayH Posts 122 Registration date Tuesday 31 August 2010 Status Contributor Last seen 20 June 2016 - 17 Jun 2016 à 21:31
fdibbins Posts 33 Registration date Sunday 19 June 2016 Status Contributor Last seen 20 November 2016 - 20 Jun 2016 à 23:14
Hello Chaps

I don't often ask for help here but this one has stumped me.

For a dashboard of sorts...
I have a list of values in column S.
Each of these has a status value in column C.

I need to find the maximum value in column S where the status matches one in a list of statuses.
The table of valid status values is in A2:A6

So something like "=(MAX(IF(C2:C111=ValueOnStatusTable,S2:S200)))
This would work if the StatusTable was only 1 cell and matched only 1 value 


A	C	S
X	M	5
M	M	8
1	1	10
2	L	13
A	A	8
	A	5
	X	3
	K	1


So, in this case the value return would 10 as its status of M is in the list
The value of 13 is not returned as its status value of L is NOT in the list

Likewise for finding the MIN value.
In that case 3 would be returned and not 1 as X is valid and K is not.

Thanks
Ray
Related:

5 responses

Réponse 1 / 5
vcoolio Posts 1411 Registration date Thursday 24 July 2014 Status Contributor Last seen 6 September 2024 262
19 Jun 2016 à 04:00
Hey Ray,

I'm just about to head off into the sunset but I was thinking that perhaps you could use the INDEX/MATCH function wrapped in the MAX function. Something like:-

=MAX(INDEX($C$1:$C$8,MATCH(A6,$B$1:B$8,0)))

assuming the values are in Columns A, B, & C.

If there isn't a matching value, it should return N/A in the column in which you place the formula. But I'm not sure if it could handle and give the right return if there is a value in the first column with two matches in the second column (such as the value "M").

Have a fiddle with it. You never know.

Formulae are not a strong point with me so where is Mazzaropi when you need him?!!?

I'm off. See ya.

Cheerio,
vcoolio.
RayH Posts 122 Registration date Tuesday 31 August 2010 Status Contributor Last seen 20 June 2016 26
19 Jun 2016 à 10:52
Hey VC,
Thanks for the reply.
I think the solution you gave was one of the many variants I tried already.
It does return a value but doesn't take the whole valid status table into account and returns just the first instance of the status.

Bear in mind that it should return the MAX value of all of those statuses not individually.

I'd prefer a Maserati but I'd settle for Mazzaropi right now.
Réponse 2 / 5
fdibbins Posts 33 Registration date Sunday 19 June 2016 Status Contributor Last seen 20 November 2016 1
19 Jun 2016 à 23:39
=(MAX(IF(C2:C111=ValueOnStatusTable,S2:S200)))
that is an ARRAY formula and needs to be entered using CTRL SHIFT ENTER, not just enter

=MAX(INDEX($C$1:$C$8,MATCH(A6,$B$1:B$8,0)))
Dont think this will work. INDEX/MATCH will return 1 value, so there will be nothing for MAX to work on.

The MAX/IF should work though
RayH Posts 122 Registration date Tuesday 31 August 2010 Status Contributor Last seen 20 June 2016 26
19 Jun 2016 à 23:48
Hi fdibbins,

Yeah, I tried that too.
What I would I put in place of ValueOnStatusTable though? Using the range of the table I get #N/A indicating that it cant find a value??

Both the Status column (C) and the ValidStatus range are formatted as Text. Sometimes this causes a problem when they are different.
Réponse 3 / 5
fdibbins Posts 33 Registration date Sunday 19 June 2016 Status Contributor Last seen 20 November 2016 1
20 Jun 2016 à 02:10
If the range you are trying to find the max of, is formatted as text, that is what is causing the problem - you cannot find the max of text...which is greater, blue or green?

You will need to convert those text "values" back to numbers 1st

hmm or maybe try using SUMPRODUCT()
RayH Posts 122 Registration date Tuesday 31 August 2010 Status Contributor Last seen 20 June 2016 26
20 Jun 2016 à 11:01
"Both the Status column (C) and the ValidStatus range are formatted as Text. "

The value I am trying to MAX is formatted as "General"

Given this scenario, how do I pick out those values to SUMPRODUCT with?
Réponse 4 / 5
vcoolio Posts 1411 Registration date Thursday 24 July 2014 Status Contributor Last seen 6 September 2024 262
20 Jun 2016 à 03:18
Hi Ford,

Welcome to CCM. Glad to see you here.

I see your logic as I'm sure that Ray can. It is a tricky one though!

Over to Ray...........

Thanks Ford.

Cheerio,
vcoolio.
fdibbins Posts 33 Registration date Sunday 19 June 2016 Status Contributor Last seen 20 November 2016 1
20 Jun 2016 à 23:14
Thanks for the invite, hope I can be of help here :)
Réponse 5 / 5
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 555
20 Jun 2016 à 11:20
Hey guys,

Try reserving an extra column for this formula:
=IF(ISERROR(VLOOKUP(C2,$A$2:$A$6,1,0)),0,1)

I placed this in column T, but you can use any column, which you can then hide or place out of sight.

Then use this formula to get your answer:
=MAX((T2:T9=1)*S2:S9)
This is an array formula; confirm with Ctrl+Shift+Enter.

Best regards,
Trowa
RayH Posts 122 Registration date Tuesday 31 August 2010 Status Contributor Last seen 20 June 2016 26
20 Jun 2016 à 11:31
Thanks TrowaD

That works. I was hoping not to use a helper column though but I'll take what I can get at this stage.
RayH Posts 122 Registration date Tuesday 31 August 2010 Status Contributor Last seen 20 June 2016 26
20 Jun 2016 à 13:23
Those wanting to use this for MIN you have to wrap this in an IF to check for ZERO 

=MIN(IF(($T2:$T9=1)*$S2:$S9>0,$S2:$S9))

Similar discussions

max payne 2/ daemon tools
dave -
nikki -

9 responses
Convert number to spelling
Sukumar -
mrwagh85 -

28 responses