Largest Value with Duplicates

[rtr1811]

Hi,
My Excel version is Excel 2007. I have a list of Numbers in a column which include zero and non zero values and also contain duplicate values (repeating numbers). My problem is I've to add a number in a particular cell say for example in cell No. H54 to the cell which contains the Maximum Number. Since my data contain duplicate values, the value in H54 is added to both the cells when I use the following formula:
I have entered the following array formula:

=MAX(IF(H26=$H$18:$H$26,H52+$H$54,H52))

and I have also tried the following formula:

=IF(H18=LARGE($H$18:$H$26,1),H44+$H$54,H44)
To further explain my problem, I have attached an image. Please refer Column H. Both the above formula consider row number 25 and 26 as Maximum values. But I want to return only row number 25. What modifications have to be done to effect the change I want? Thanks in advance.
Regards,
Rathakrishnan.T

 

[Fluff]

Hi & welcome to MrExcel.
How about
=IF(AND(H18=MAX($H$18:$H$26),COUNTIF(H$18:H18,H18)=1),H44+$H$54,H44)

 

[rtr1811]

Many Thanks for your Reply. It almost solved my problem. It returns row number 26. What I have to do if I want to return row number 25?

 

[Fluff]

It does that.

+Fluff New.xlsm
	H	I
18	0	10
19	0	11
20	0	12
21	0	13
22	0	14
23	0	15
24	0	16
25	1	117
26	1	18
43
44	10
45	11
46	12
47	13
48	14
49	15
50	16
51	17
52	18
53
54	100
Main

Cell Formulas
Range	Formula
I18:I26	I18	=IF(AND(H18=MAX($H$18:$H$26),COUNTIF(H$18:H18,H18)=1),H44+$H$54,H44)

 

[rtr1811]

Very Sorry. It is working correct. I have misunderstood the output.

Thank you.

 

[Fluff]

You're welcome & thanks for the feedback.