Getting the min value that matches multiple conditions?

[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

 

[Ken Johnson]

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

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

 

[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.

 

[jg70124]

Roger: Excellent! Thanks

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.

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

 

[Roger Govier]

Hi

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

 

[Ken Johnson]

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)...

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

 

[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

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.

 

[Ken Johnson]

Hi Roger,

Thanks for clearing that up for me.

Ken Johnson

 

[Roger Govier]

You're more than welcome Ken.