Getting the min value that matches multiple conditions?

J

jg70124

My data table looks like this:

A B C
---------------------------------------------
1 Region Gas Sales Price
2 US Air $35,000
3 Asia Nitrogen $50,000
4 US Air $38,000
5 US Nitrogen $39,000
6 US Air $40,000
7 US Air $55,000
8 Asia Air $51,000
9 Asia Nitrogen $52,000
etc...

There are 1300 rows, not sorted.

What formula can I use to get the minimum sales price for a particular
combination of region and gas?

Thanks
 
K

Ken Johnson

jg70124 said:
My data table looks like this:

A B C
---------------------------------------------
1 Region Gas Sales Price
2 US Air $35,000
3 Asia Nitrogen $50,000
4 US Air $38,000
5 US Nitrogen $39,000
6 US Air $40,000
7 US Air $55,000
8 Asia Air $51,000
9 Asia Nitrogen $52,000
etc...

There are 1300 rows, not sorted.

What formula can I use to get the minimum sales price for a particular
combination of region and gas?

Thanks
Click to expand...

Hi,

This seems to work with the chosen Region entered into D1 and the
chosen Gas into E1...

=LARGE(--($A$2:$A$1301=$D$1)*--($B$2:$B$1301=$E$1)*($C$2:$C$1301),SUMPRODUCT(--($A$2:$A$1301=$D$1)*--($B$2:$B$1301=$E$1)))

Hopefully someone can come up with an improvement.

After you type or paste in the formula you need to press Ctrl + Shift +
Enter so that it is entered as an array formula, otherwise it will not
work.

Ken Johnson
 
R

Roger Govier

Hi

With Required Region in F2 and required gas type in G2
the following array entered formula
{=MIN(IF(($A$2:$A$9=F2)*($B$2:$B$9=G2)>0,$C$2:$C$9,""))}

Use Ctrl+Shift+Enter (CSE) to commit or Edit the formula, rather than
just Enter.
Excel will insert the curly braces { } around the formula when you
use CSE, do not type them yourself.
Amend ranges to suit.
 
J

jg70124

Roger: Excellent! Thanks

Roger said:
Hi

With Required Region in F2 and required gas type in G2
the following array entered formula
{=MIN(IF(($A$2:$A$9=F2)*($B$2:$B$9=G2)>0,$C$2:$C$9,""))}

Use Ctrl+Shift+Enter (CSE) to commit or Edit the formula, rather than
just Enter.
Excel will insert the curly braces { } around the formula when you
use CSE, do not type them yourself.
Amend ranges to suit.
Click to expand...

Ken: Thanks for your suggestion, too. What does the double negative
mean in your formula? - eg ...--($A$2:$A$1301=$D$1)...
 
R

Roger Govier

Hi

You're very welcome.Thanks for the feedback. Glad to know it worked for
you.
 
K

Ken Johnson

jg70124 said:
Roger: Excellent! Thanks



Ken: Thanks for your suggestion, too. What does the double negative
mean in your formula? - eg ...--($A$2:$A$1301=$D$1)...
Click to expand...

Hi,

"--" is called "double unary" and it converts TRUE to 1 and FALSE to 0
so that boolean results can then be used in calculations. A single "-"
converts TRUE to -1 and FALSE to 0, so the extra one then produces the
desired result.

The TRUE/FALSE values are produced by Excel when it is testing each
array element's logical condition eg $A$2:$A$1301=$D$1 produces the
equivalent of a column of TRUE/FALSE results, --($A$2:$A$1301=$D$1)
produces the equivalent of a column of corresponding 1/0 results.

Ignoring other deficiencies in my suggested solution :-/ it turns out
they aren't needed there, I removed them and got the same result, so I
need to do a bit of research to determine exactly when they are needed.
I'm sure Roger could clear that up for us.

Ken Johnson
 
R

Roger Govier

Hi Ken

I have been working away for most of the past week so only just picked
up your posting.
it turns out they ( double unaries) aren't needed there
need to do a bit of research to determine exactly when they are needed
Click to expand...

Because you are using the "*" multiplication factor, that alone will
coerce each of the tests from True/False to 1/0 hence the double unary
is superfluous in this context.
 
Top