I'm tryinng to work on some new ideas today in Excel 2002.
Things look promising, but I just can't quite make these dang formulas
work.

The current problem is this: I want to know the minumum price from
a column (H) whose named range is "colPrice", but only when Column
C (named "colSymbol") matches my condition.

For example, there is the symbol ALTR in colSymbol. It appears
twice. There are two prices in colPrice for that symbol. They are
22.53 and 23.03. The formula should tell me the lowest one.

I tried this:

=MIN(SUMPRODUCT(colPrice*(colSymbol="altr")))

I get an answer of 45.56, which is nonsense! There is no value 45.56 in
the entire table! The table has 26 columns and 169 rows of data at present.
Can someone please give me a shove and tell me what I've done wrong? Would
be most appreciated.

Thanks!

--
dman

Modify this to suit your needs and ARRAY enter using ctrl+shift+enter

=MIN(IF(D2:E22="a",C2:C22))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dallman Ross" <dman@localhost. wrote in message
...
I'm tryinng to work on some new ideas today in Excel 2002.
Things look promising, but I just can't quite make these dang formulas
work.

The current problem is this: I want to know the minumum price from
a column (H) whose named range is "colPrice", but only when Column
C (named "colSymbol") matches my condition.

For example, there is the symbol ALTR in colSymbol. It appears
twice. There are two prices in colPrice for that symbol. They are
22.53 and 23.03. The formula should tell me the lowest one.

I tried this:

=MIN(SUMPRODUCT(colPrice*(colSymbol="altr")))

I get an answer of 45.56, which is nonsense! There is no value 45.56 in
the entire table! The table has 26 columns and 169 rows of data at
present.
Can someone please give me a shove and tell me what I've done wrong?
Would
be most appreciated.

Thanks!

--
dman

In , Don Guillett
spake thusly:

Modify this to suit your needs and ARRAY enter using
ctrl+shift+enter

=MIN(IF(D2:E22="a",C2:C22))

Great! Thanks, Don.

--
dman

Try something like this ARRAY FORMULA:

=MIN(IF(colSymbol="altr",colPrice))

Does that help?
***********
Regards,
Ron

XL2003, WinXP


"Dallman Ross" wrote:

I'm tryinng to work on some new ideas today in Excel 2002.
Things look promising, but I just can't quite make these dang formulas
work.

The current problem is this: I want to know the minumum price from
a column (H) whose named range is "colPrice", but only when Column
C (named "colSymbol") matches my condition.

For example, there is the symbol ALTR in colSymbol. It appears
twice. There are two prices in colPrice for that symbol. They are
22.53 and 23.03. The formula should tell me the lowest one.

I tried this:

=MIN(SUMPRODUCT(colPrice*(colSymbol="altr")))

I get an answer of 45.56, which is nonsense! There is no value 45.56 in
the entire table! The table has 26 columns and 169 rows of data at present.
Can someone please give me a shove and tell me what I've done wrong? Would
be most appreciated.

Thanks!

--
dman

As you have most probably noticed, 45.26 is the sum of 23.53 and 23.03, which
is what SUMPRODUCT does, the sum of the multiplication of the items of an
array.

What I propose is to use an array formula (that you validate with Alt-Ctrl-
Enter):
=MIN(IF(A1:A12="b",B1:B12,9E+99))

A set of curly brackets appears around the formula once you have validated it.


Replace A1:A12 and B1:B2 by your own ranges.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200707/1

In <75dbd82300206@uwe, squenson via OfficeKB.com <u36146@uwe
spake thusly:

As you have most probably noticed, 45.26 is the sum of 23.53
and 23.03, which is what SUMPRODUCT does, the sum of the
multiplication of the items of an array.

Yup, I ultimately did notice that. :-) Thanks.


What I propose is to use an array formula (that you validate with
Alt-Ctrl- Enter): =MIN(IF(A1:A12="b",B1:B12,9E+99))

Thank you. Similar to Don Guillet's offering, I see. I don't
follow you on the large number at the end, though. I know
it's some sort of a program max in Excel, but am not sure why
you're suggesting I use it in the formula. If you'd enlighten
me, I'd be grateful!

Thanks again,
Dallman

As a programmer, I do not like to leave IF options not formally answered. Do
you know how MIN behaves if it finds a FALSE (often equivalent to 0) in a
list? Do you know how MIN will behave in the next Excel release? I prefer to
have absolute control of my formulas and do not introduce potential bugs.

Dallman Ross wrote:
I don't
follow you on the large number at the end, though. I know
it's some sort of a program max in Excel, but am not sure why
you're suggesting I use it in the formula. If you'd enlighten
me, I'd be grateful!

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200707/1

In <75e175f2f92af@uwe, squenson via OfficeKB.com <u36146@uwe
spake thusly:

As a programmer, I do not like to leave IF options not formally
answered. Do you know how MIN behaves if it finds a FALSE (often
equivalent to 0) in a list? Do you know how MIN will behave in
the next Excel release? I prefer to have absolute control of my
formulas and do not introduce potential bugs.

Thank you, "squenson." Much appreciated.

===============
Dallman Ross wrote:

I don't follow you on the large number at the end, though.
I know it's some sort of a program max in Excel, but am not
sure why you're suggesting I use it in the formula. If you'd
enlighten me, I'd be grateful!

Do you know how MIN behaves if it finds a FALSE
(often equivalent to 0) in a list?

Yes. Logical values are ignored.

Do you know how MIN will behave in the next Excel release?

Yes. It will behave the same as it's always behaved.

--
Biff
Microsoft Excel MVP


"squenson via OfficeKB.com" <u36146@uwe wrote in message
news:75e175f2f92af@uwe...
As a programmer, I do not like to leave IF options not formally answered.
Do
you know how MIN behaves if it finds a FALSE (often equivalent to 0) in a
list? Do you know how MIN will behave in the next Excel release? I prefer
to
have absolute control of my formulas and do not introduce potential bugs.

Dallman Ross wrote:
I don't
follow you on the large number at the end, though. I know
it's some sort of a program max in Excel, but am not sure why
you're suggesting I use it in the formula. If you'd enlighten
me, I'd be grateful!

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200707/1

Next of my questions while I work through some brainstorming today
is this:

I have (for stocks' daily historical data) a column of highs and a
column of lows. I want to know the standard deviation of the day's
range.

I know I can create a new column, e.g., "X", that contains each
day's range (high minus low). Then I could just do STDEV(X:X).
But I'd rather not create the column if I don't need to. Is there
an easy way to do this?

Thanks for more good insight from a great group.

--
dman