Need a Maxif sort of formula

I'll assume that A1:B9 houses your sample data to which I added labels:

{"Letters","Values";
"A",0.573;
"N",0.58;
"N",0.578;
"X",0.525;
"Y",0.581;
"A",0.612;
"Y",0.556;
"X",0.551}

In D1 enter: =MATCH(9.99999999999999E+307,B:B)

This formula requires that you have only the values of interest in B and nothing else.

Make a unique list of letters in D from D2 on (You can do this task with Advanced Filter, if you have many of them.).

Applied to the above sample, we get:

{"Letters";
"A";
"N";
"X";
"Y"}

in D2:D6.

In E2 enter: Max Value [ just a label ]

In E3 enter

[1] either:
=SUMPRODUCT(MAX((OFFSET($A$2,0,0,$D$1,1)=D3)*(OFFSET($B$2,0,0,$D$1,1))))

[2] or array-enter:

=MAX((OFFSET($A$2,0,0,$D$1,1)=D3)*(OFFSET($B$2,0,0,$D$1,1)))

If you go for [2], you need to hit control+shift+enter, instead of just enter.

You'll see the following appear in D:E from D1 on:

{9,"";
"Letters","Max";
"A",0.612;
"N",0.58;
"X",0.551;
"Y",0.581}

where "" stands for blank.

===============================

Assuming your data above is in the range A1:B8 and your variable (Y) is in cell C1, put this formula in cell D1 as an array entry.

=MAX((A1:A8=C1)*(B1:B8))

To make it an array formula, press CTRL+SHIFT+ENTER instead of ENTER.

Regards,
BarrieBarrie Davidson

Aladin?

Wouldn't my array formula be simpler (I think I'm just getting the hang of them and now I'm wondering if I'm overdoing it with array formula solutions)?

Best regards,
Barrie I'll assume that A1:B9 houses your sample data to which I added labels: {"Letters","Values";

Re: Aladin?

Wouldn't my array formula be simpler (I think I'm just getting the hang of them and now I'm wondering if I'm overdoing it with array formula solutions)? Best regards,

You must have noticed that the formula #2 that I suggested is exactly the same thing that you suggested. The only difference is that my version can be copied down and can cope with a dynamically changing data range.

I guess you're asking about what would be better the one with SUMPRODUCT (Formula #1) or the array one. I don't think there would be any significant performance difference between them. The former avoids the hassle with control+shift+enter, when you copy and paste somewhere else or edit.

Regards,

Aladin

========= : I'll assume that A1:B9 houses your sample data to which I added labels

Re: Aladin?

Wouldn't my array formula be simpler (I think I'm just getting the hang of them and now I'm wondering if I'm overdoing it with array formula solutions)? Best regards, : I'll assume that A1:B9 houses your sample data to which I added labels

Hi Guys--

Arrays, i tend to avoid them under pain of death as im rubbish at them, ive said many times Aladin is the formula guru with many regular postrs just as good. I try arrays in VBA painful, Dave down on Ozgrid tend to avoid them too as not the best way to go on many issues, but i follow you guys on most im not sure about, so im intrested, are formula or CSE the best and when.

I sit for hours on some UDF and Aladin pops up a line of formula that distroys my codes good parts.

I do know this CES Array are slower buy nature, my UDF are even slower, but straight formula are FAST, but some VBA can beat this, i can input a formul in 60,000 rows and convert in a second! or a blink longe if inset col conver + [aste as value and delete col.

So im lost... some guidence i would love, when is what best?

Cheers guys after a yellow brick road i guess, i copy many of the big hitters work and play, so thanks top so many ive learnt from

Rdgs
JACK

One or the other?

Jack, I also tend to shy away from CSE formulas. Two reasons I do this:
• They can really tie up the CPU while it evaluates the entire array.
• I'm still pretty new at creating them
and they are very complex to comprehend (at least for me!)

I will use them if I want to evaluate an array and have the results in one cell.

Regards,
BarrieBarrie Davidson

Thanks Aladin (nt)

Thanks,
You guys are great!
Why won't the formulas work if I apply the opposite of MAX and use MIN instead?

Debate on which is best:
I understand Barrie's formula the best.
I don't think I understand the MATCH portion and then why SUMPRODUCT works in Alladin's
But, they all three work.

What a great message board, Ive told lots of people about it.
Randy

Thanks,

It won't work using MIN because the minimum of that array formula is 0 (any row where the value in column A does not equal the value entered in C1. This results in a FALSE value, or zero). I'll see if I can come up with a solution for your MIN problem.

Regards,
BarrieBarrie Davidson

Randy --

MIN requires a modification. I'll assume the layout in my first reply. Use either

In F3 array-enter (which uses OFFSET to automatically determine the range on which the formula applies):
=MIN(IF((OFFSET($A$2,0,0,$D$1,1)=D3)*(OFFSET($B$2,0,0,$D$1,1)),(OFFSET($B$2,0,0,$D$1,1))))

or array-enter

=MIN(IF(($A$2:$A$9=D3)*($B$2:$B$9),($B$2:$B$9)))

Copy down the formula you choose to use till last letter in D.

Note. The match formula (in my first reply) calculates automatically how many rows of data you have. The OFFSET function uses that number to tell the MAX function what the actual range is. Such a setup allows you to add news rows of data or delete some rows without requiring a change to the formulas that you use. If all this seems terible to you, use for MAX the array formula without OFFSET and the supporting MATCH:

=MAX(($A$2:$A$9=D3)*($B$2:$B$9))

and copy down till the last row in D.

Aladin

========= Thanks,

My 2 cents

Jack & Barrie --

The issue with array formulas (mutatis mutandis with SUMPRODUCT formulas) comes up very often because of performance costs.

I believe that if you don't need to copy such formulas to other cells (as when you want to compute how many different/unique items are in a range), such a formula would be better than a non-array solution (if exists) that requires copying down an ordinary formula to many cells, at least doubling memory requirements and incurring speed costs.

It would be better trade memory against speed in cases where you need to copy an array or SUMPRODUCT formula say to 1000 or more cells. A particular illustration of this trade off occurred when Richard Larocque asked formulas for "LargeIf" and especially for "Rankif". I gave him first (inherently) array formulas he had to copy to thousands of cells. They were of course choking his system and asked for non-array versions. Fortunately, I could produce non-array versions trading memory against speed. See for the exchanges the archives -- search using "Larocque" as keyword.

Regards,

Aladin

========

Re: Thanks Aladin (nt)

Guys you 100 years in front of me, but i thank you for allowing my input and helping me.

Hear i have leant what i never would else where.

Aladin / Barrie thank you.

Rdgs
Jack