Removing a cell from a selected range

[kippers2]

Hi,
I have a range of value which i use stddev on
I would like to exclude any max value from this range
I can create the range selection for the stddev
I can also find the cell address with the max value in it
How do i create a take that cell.address away from the range?
i.e. i would like to create a range without the amx value cell in it

Kind of like a union operation in reverse

I have a round about way by checking each cell value and if it is not the max then union it to a range then loop
But there must be a quicker way

Anybody got any ideas

[Bob Phillips]

=STDEV(IF(E1:E10<>MAX(E1:E10),E1:E10))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter.Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually.When editing the formula, it must again be array-entered.

[kippers2]

Brilliant - did not think off array formula
2 more questions
1. Is there a good site that I can read up on array formulas?
2. How do i put an array formula in vba i.e msgbox {STDEV(IF(E1:E10<>MAX(E1:E10),E1:E10))} does not work
Yes i know this is probable the dumdest question of the year! Just having a mental blank!

[Bob Phillips]

1. No, I think that is a big whole. Chip Pearson has a page on array formulae, http://www.cpearson.com/Excel/ArrayFormulas.aspx, but this barely gets beyond the basics IMO

2. You don't. Array formulae are Excel, MsgBox is VBA

[kippers2]

So how can i use an array formula in vba? Or something similar?

[Bob Phillips]

Why would you? You do things differently in VBA, that is why you use VBA to do things that you cannot do in Excel, or are more efficienbt in VBA, not to emulate Excel.

[rory]

FWIW, this works for me:
msgbox application.Evaluate("STDEV(IF(E1:E10<>MAX(E1:E10),E1:E10))")
Regards,
Rory

[Bob Phillips]

Emulating Excel in VBA as I said!

[rory]

I'm not disputing that - I was merely answering the question asked. I can't offhand think of a good reason to do this, but that doesn't mean there isn't one...