I colums a I have values from 1 to 20 repeating seveal times. In column B I have many different values. I need a formula that with for example search for the maximum value in column B while column A = 7. Any Ideas? Thanks.
Karl
I colums a I have values from 1 to 20 repeating seveal times. In column B I have many different values. I need a formula that with for example search for the maximum value in column B while column A = 7. Any Ideas? Thanks.
Karl
Hi Karl,
This worked for me, however it is an array formula so you must use Ctrl
+ Shift + Enter to enter it...
=MAX(IF(A1:A44=7,B1:B44))
Adjust addresses to suit
Ken Johnson
Ken,
That formula only seems to work if the max value in column b is highest on the list for coresponding values in column a.
kwrohde wrote:
> Ken,
>
> That formula only seems to work if the max value in column b is highest
> on the list for coresponding values in column a.
>
Hi Karl,
Either I misunderstand you problem or the formula doesn't work (as you
say).
Is the value you are after the same as if you filtered column A to only
show the 7s then it's the maximum value that you can see in column B?
Unless I'm missing something (often the case!) that is what the array
formula returns.
Ken Johnson
Hi Karl,
as per usual, there was something I missed. You were right, it doesn't
return the required maximum.
Try this one, also an array formula...
=MAX(--(A1:A124=7)*B1:B124)
I'm fairly confident this one works (unless I've missed something
again, which is sometimes the case:-))
Ken Johnson
That does not work entirely.
here is my example
A B C D
2 1123 1 2000
4 5000 2 4566
5 455456 3 1000000
6 450000 4 45645
8 500 5 456
9 1000 6 643
1 550 7 956
3 4566 8 12
7 956 9 10000
1 2000
2 4566
3 1000000
4 45645
5 456
6 643
7 545
8 12
9 10000
the formula in cell d1 is {=MAXA(IF($A1:$A18=$C1,$B1:$B18))}
If you try this example you will see the answers in cell d5, d6, and d8 are in correct.
the formula {=MAX(--($A1:$A18=$C1)*$B1:$B18)} returns the same result.
I found a different solution in another forum that yields correct results
the formula is:
{=INDEX($B$1:$B$18,MATCH($C1&" "&MAX(IF($A$1:$A$18=$C1,$B$1:$B$18,0)),$A$1:$A$18&" "&$B$1:$B$18,0),0)}
this formula works fine but I also need to find for example "the minimum value in column B while column A = 7"
I assumed that i could substitute max in the formula above with min but that returns #NA.
Any other ideas?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks