Having trouble with my formula ideas today

[Dallman Ross]

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!

 

[Don Guillett]

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

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

 

[Guest]

Try something like this ARRAY FORMULA:

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

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

XL2003, WinXP

 

[squenson via OfficeKB.com]

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.

 

[Dallman Ross]

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

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

Great! Thanks, Don.

 

[Dallman Ross]

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

 

[Dallman Ross]

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.

 

[squenson via OfficeKB.com]

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]

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.

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

 

[T. Valko]

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.