array issue

[ahimanawa elliott]

Hi

i can do the following

=MEDIAN(IF(W$17:W$500="Brown",T$17:T$500))

but what about the opposite? eg

=MEDIAN(IF(W$17:W$500<>"Brown",T$17:T$500)) - why won't this work

 

[Jim Cone]

Because... If(W$17:W$500="Brown") is an improper construct.

The following formulas determine if "Brown" exists in the range and then does/does not calculate the
median...
=IF(ISNUMBER(MATCH("Brown",W$17:W$500,0)),MEDIAN(T$17:T$500),"not found")
=IF(ISNUMBER(MATCH("Brown",W$17:W$500,0))=FALSE,MEDIAN(T$17:T$500),"found")
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Data Options add-in: Color or Delete or Insert: specific rows/dates/random data)




"ahimanawa elliott" <[email protected]>
wrote in message
news:[email protected]...

 

[ahimanawa elliott]

thanks Jim

now i know that my constructs are improper. I need to explain better my problem

I'm wanting to work out the median for a list of numbers that correlate with the colour brown (done this and it works as follows

=MEDIAN(IF(V$17:V$500="Male",S$17:S$500))

i also want the median for the numbers in the same list which are NOT brown.

i hope this makes sense and i certainly appreciate any assistance

 

[Jim Cone]

In Excel 2003, I get the message "Formula is too long" when the range is > ~200 cells.
Try this; highlight... V$17:V$500="Male" ...and press F9 (press escape to exit).

 

[Jim Cone]

That should be...
highlight... V$17:V$500<>"Brown"
'---
Jim Cone

 

[joeu2004]

i can do the following
=MEDIAN(IF(W$17:W$500="Brown",T$17:T$500))
but what about the opposite? eg
=MEDIAN(IF(W$17:W$500<>"Brown",T$17:T$500))
- why won't this work

If the first works, the second should work. That is, it should determine
the median of all values in T17:T500 that corresponding W17:W500 that does
not equal the text "Brown".

However, both formulas must be array-entered. That is, press
ctrl+shift+Enter instead of just Enter. Are you doing that in both cases?

You can confirm by looking at the formula in the Formula Bar. It should be
surrounded by curly braces, i.e. {=formula}. You cannot type the curly
braces yourself. That is just the way that Excel displays an array-entered
formula.

If you do not see the curly braces in the Formula Bar, select the cell,
press F2 to "edit" the cell, then press ctrl+shift+Enter.

I need to explain better my problem
I'm wanting to work out the median for a list of numbers
that correlate with the colour brown

That confuses the matter, not "explains it better".

Generally, you cannot test font or "pattern" (fill) color of an Excel
formula.

You could write a VBA function that tests the cell font or pattern color and
returns some indication of what it is.

=MEDIAN(IF(V$17:V$500="Male",S$17:S$500))
i also want the median for the numbers in the same list
which are NOT brown.

(Why did you switch from T17:T500 to S17:S500?!)

I wonder if you really want to compute the median of S17:S500 of the numbers
where the corresponding V17:V500 is the text "Male" and W17:W500 is not the
text "Brown".

Ostensibly, you might want to write:

=MEDIAN(IF(AND(V$17:V$500="Male",W$17:W$500<>"Brown"),S$17:S$500))

But you probably discovered that does not do what you intended.

Two ways to effect the same logic:

=MEDIAN(IF(V$17:V$500="Male",IF(W$17:W$500<>"Brown",S$17:S$500)))

or:

=MEDIAN(IF((V$17:V$500="Male")*(W$17:W$500<>"Brown"),S$17:S$500))

In the latter case, multiplication (*) is used to effect an "AND" operation.
The expression is 1 only when both comparisons are TRUE (treated as 1). The
IF() function treats any non-zero condition as TRUE in this context.