0

I have a question about formulas, it´s not an easy one, I hope someone can solve it.

Table looks like this

Idx Val Rslt
1   1   3
1   2   3
1   3   3
2   11  13
2   12  13
2   13  13

Named range NR encapsulates columns Idx and Val (without headers).

I would like to have a formula for Rslt column cells that :

a) recognizes rows with same Idx value

b) returns max Val from that set of rows

I hope I was clear, if not - feel free to ask.

Thanks

MRB
  • 422
  • 1
  • 8
  • 16

1 Answers1

2

=MAX(IF($A$2:$A$7=A2,$B$2:$B$7))

Type this in to C2 and then press ctrl+shift+enter, it will then fill the formula down.

Sled
  • 18,541
  • 27
  • 119
  • 168
Nick
  • 36
  • 1
  • Ok, you are assuming that first row is empty. Unfortunately your formula gives 13 instead of 3 as first result. I did it other way, it's maybe complicated but it works here, formula is : =MAX(INDEX((INDEX(A2:B7,0, 1)=A2)*(INDEX(A2:B7,0, 2)),0)) --- Thanks! – MRB Dec 06 '12 at 22:07
  • @Milan It gives 13 if you don't create the aray by pressing ctrl+shift+enter when you get some time do some research on ctrl+shift+enter it may save you some time on future issues instead of using slightly more complexed index formula's. Also don't forget to use absolute values ($) so that you can easily copy your formula down. Good luck! – Nick Dec 07 '12 at 19:19
  • I though CSE will "fill the formula down" was used to copy the formula in the cells below. Sorry for misunderstanding. I just tried it and it works perfect. And I am reading about CSE formulas, great stuff, I will surely use them in the future. Thanks a million !!! – MRB Dec 08 '12 at 21:10