max/if array formula question

[Mparekh]

(it's the index/match problem, but with one more twist in it)..


I have some cells in column A which are blank(has no value in it) an
would like to use the existing value of column B. How do I modify th
array formula(=MAX(IF($A$2:$A$8=A2,$B$2:$B$8))
Currently it just gives me #NA for such cells.

Col A Col B
1 1%
Blank 1.6%
1 2%
blank 2.1%
2 2.2%
2 2.4% and so on

for the cells in col A which are blank, I would like to use th
existing value in Col B. While for col A where there is value I wan
this formula to work and update the corresponding cells. How do
modify the formula?

Thanks in advance
parekh.


Quote:
Originally Posted by NH
AGAINST EACH VALUE TYPE THIS IN COL C AND ARRAY ENTER THEM
(CTRL+SHIFT+ENTER)
=MAX(IF($A$2:$A$8=A2,$B$2:$B$8))

I see lot of excel guru's in here and am sure someone would be abl to
help me. Here's how my data looks like:

Column A Column B
1 2%
2 2.1%
3 3%
1 3.5%
1 3.6%
1 3.7%
2 4% so on and so forth...

What I need to do is, for all the 1's in column A, replace value in
correspoding cell in column B with highest value. So for all 1's in
column A, Column B should have 3.7%. Same way for all 2's in colum A,
column B should change to 4%...How do I do that with a formula an not
macros? Struggled a lot with index and match but still not getting
anywhere.

Thank you all in advance.

Parekh

http://www.excelforum.com/showthrea...threadid=26619

 

[Aladin Akyurek]

Do you mean...

{=MAX(IF(($A$2:$A$8=A2)+($A$2:$A$8=""),$B$2:$B$8))}