MinIFS() for older versions of excel help.

[MarvinP]

After 2 hours this morning trying to solve this, I turn to those with a clearer mind.

I'm looking for a formula that would work like the new 2016 MinIFS function.

I have groups of 4 golfers who are on two different teams. I need to give each golfer the Min score for their team in each group of 4 players. I need this formula in cell A2 so I can pull it down for all players.

I worked on an array solution this morning and kept getting zeros, not being able to make zero go to blank.

See the attached and the yellow cells (pulled down to the bottom) need the formula.

MinIFS function for older versions Help.xlsx

[sandy666]

Are you talking about sth like this ?

Please Login or Register  to view this content.

and CSE

[Pete_UK]

Hi Marvin,

you can use this array* formula in D2:

=MIN(IF(($A$2:$A$48=A2)*($B$2:$B$48=B2)*($C$2:$C$48<>0),$C$2:$C$48))

*Use CSE to commit, as I'm sure you know.

The term in red isn't strictly needed if all the data in column C is above zero, but I extended the ranges (shown in blue) to include the null data at the bottom just to check it works okay with larger ranges.

Hope this helps.

Pete

[sandy666]

Right Pete, I forgot about 0 values

but: kept getting zeros, not being able to make zero go to blank

If I understand Marivn want blank not zero
so If(formula=0,"",formula) I think.

But maybe I'm wrong (English isn't my native lang)

[AlKey]

Ok, this one should kill all zeros
Array formula must be entered with CSE

Please Login or Register  to view this content.

[MarvinP]

Great answers. After searching the net for more here are possible answers.
CSE type answers are:

=MIN(IF(($A$2:$A$45=A2)*($B$2:$B$45=B2)*($C$2:$C$45<>0),$C$2:$C$45)) - Pete

=IFERROR(1/(1/MIN(IF((B$2:B$48=B2)*(A$2:A$48=A2),C$2:C$48))),"") - AlKey

=MIN(IF($A$2:$A$45=A2,IF($B$2:$B$45=B2,$C$2:$C$45,""),"")) - net search

Non CSE answer is:

=SUMPRODUCT(MIN($C$2:$C$45*(($A$2:$A$45=A2)*($B$2:$B$45=B2)*-10+11))) - net search

Thanks so much for the replies. I like how AlKey creates blanks using the division error and the IfError( ,"") combo.

I think I'll go with the nested if CSE answer because my brain works like that. Why couldn't I see that a few hours ago??

Thanks again to all who replied. Any better answers than the above????

MinIFS function for older versions answers.xlsx

[AlKey]

Glad to be of help Marvin. Thanks for the feedback

[MarvinP]

Hi Guys,

I spoke too soon and couldn't make any of the possible answers work when I tried to pull them across columns. See the revised problem that is what I really need. Sorry for the first question. I must be on an off day as most of my day has been spent on this problem....

See the attached for the sample. I need to put a formula in the yellow cell and pull it down and across to fill in 18 columns. I can do it if I can get the first 3.

MinIFS function for older versions Now Across.xlsx

[sandy666]

using AlKey's formula :

Please Login or Register  to view this content.

and CSE oc

if I understand well

[Pete_UK]

Just a matter of setting the $ signs correctly - put this in G2:

=MIN(IF(($A$2:$A$45=$A2)*($B$2:$B$45=$B2)*(C$2:C$45<>0),C$2:C$45))

using CSE. Then copy across and down as required.

Hope this helps.

Pete

[MarvinP]

Hi Guys,

I spoke too soon and couldn't make any of the possible answers work when I tried to pull them across columns. See the revised problem that is what I really need. Sorry for the first question. I must be on an off day as most of my day has been spent on this problem....

See the attached for the sample. I need to put a formula in the yellow cell and pull it down and across to fill in 18 columns. I can do it if I can get the first 3.

Attachment 460488

[Jonmo1]

Since the title of the thread is "MinIfs for older versions of Excel"
Can you attach a book that shows the actual Minifs working as you desire?
That would make it much easier to work out an alternative.

[sandy666]

Post #11 is exactly the same as #8 so I think it's something wrong with server or forum scripts.

[hoangtan1312]

hi Marvin,

i would to ask, in Non-CSE answer: what mean "-10+11" ?

thank you

[bebo021999]

i would to ask, in Non-CSE answer: what mean "-10+11" ?

Hello, welcome!
the whole pic is
($A$2:$A$45=A2)*($B$2:$B$45=B2)*-10+11
generate list of 1(where column A=A2 and column B=B2) and 11.
Then multiply to C2:C45.
As a result, which value in C2:C45 with column A and B those do not match A2 and B2, will be 11 time greater than the others, and kicked out of the results by MIN function.

[bebo021999]

Because of Sumproduct goes wrong if:
Score is small enough that *11 is still smallest.
In attachment: C5=0.01 then F2=0.11 (expected to be: C4=4)
That's why the other helper (hoangtan 1312) 'd like to dig it up