Using Max, and looking up adjacent variables

[johnburgin]

Hi,

I have become quite proficient using sumproduct recently, but now I have a new challenge.

I have a spreadsheet with the following column headers:

CLIENT GROUP; CHARGE; INCREASE COST; ORIGINAL CHARGE

What I need to do is find the maximum increase by CLIENT GROUP. I have done this using an array formula (in spite of my irrational fear of them) as follows:

{=MAX(IF(Data!D8:D914="Dementia",Data!Y8:Y914))}

And this works. What I need to be able to do is find values from columns either side of this specific answer. That is to say, the maximum value for this client may be the same for other client groups, so I need to find the ORIGINAL CHARGE and CHARGE relating to the answer of this search.

This I have no idea how to do. I have been reading up on using INDEX, but do not know how to build INDEX into my above formula. Remember, I have an irrational fear of array formulas, and get easily confused by syntax when a single formula has many, many functions

Please, can someone help me?

Many Thanks,

John

 

[Andrew Poulsom]

To return the value in column E try:

=INDEX(Data!E8:E914,MATCH(MAX(INDEX((Data!D8:D914="Dementia")*Data!Y8:Y914,)),INDEX((Data!D8:D914="Dementia")*Data!Y8:Y914,),FALSE))

It's not an array formula.

 

[johnburgin]

Excellent - many thanks! Now I need to get my head around how that works

 

[Snakehips]

johnburgin,

Perhaps try this. I have assumed that your formula is not in Data sheet?

Data....

Excel 2007
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
7	Client																				Chrg	Inc	Orig
8	n																				1	8	14
9	Dementia																				2	5	13
10	n																				3	4	12
11	n																				4	7	11
12	Dementia																				5	26	10
13	n																				6	7	9
14	Dementia																				7	22	8
15
Data

Results......

Excel 2007
	A	B	C
1	Charge	Max Increase	Orig Charge
2	5	26	10
Sheet1

Cell Formulas
Range	Formula
A2	=INDEX(Data!X8:X914,SUMPRODUCT((Data!D8:D914="Dementia")*(Data!Y8:Y914=B2)*(ROW(Data!D8:D914)-7)),0)
C2	=INDEX(Data!Z8:Z914,SUMPRODUCT((Data!D8:D914="Dementia")*(Data!Y8:Y914=B2)*(ROW(Data!D8:D914)-7)),0)
B2	{=MAX(IF(Data!D8:D14="Dementia",Data!Y8:Y14))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Hope that helps.

 

[johnburgin]

Hi Tony,

That's interesting. I've not learnt much about how ROW works, but how is it working within the context of this sumproduct function here?

many Thanks,

John

 

[johnburgin]

Ah, I get it! Using sumproduct to find the row number, for the index! Brilliant. Thanks!

JB