if statement

[Guest]

I have 2 columns in a spreadsheet. Column A contains a date in format
11/30/2005. Column B contains a number. I need an If statement that will
return the median value of column B where the date in column A begins with 11.

Any thoughts appreciated!

 

[pinmaster]

HI,
Try:
=IF(MONTH(A1)=11,your formula,"")

HTH
J

 

[Bob Phillips]

=MEDIAN(IF(DAY(A1:A20)=11,B1:B20))

as an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

I need to find all values in column A that begin with 11 not just row 1.
There will be several rows selected and I need the median value of column B
for all rows selected.

 

[Bob Phillips]

Oops, seeing the other guys post I see you are using US dates. My formula
should thus be

=MEDIAN(IF(MONTH(A1:A20)=11,B1:B20))

still an array formula.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thank you Bob, this looks like it will work. What is meant by "commit with
Ctrl-Shift-Enter"

 

[Bob Phillips]

It means that after you type in the formula, don't hit the Enter key, use
the Ctrl-Shift-Enter keys all together. The formula will then appear in the
formula bar like so

{=MEDIAN(IF(MONTH(A1:A20)=11,B1:B20))}

Excel puts in the braces.

--

HTH

RP
(remove nothere from the email address if mailing direct)