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
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