if statement

[kraway]

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
JG

[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)


"kraway" <kraway@discussions.microsoft.com> wrote in message
news:C612214D-4DAB-4120-AC64-BAD43FDDC374@microsoft.com...
> 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!

[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)


"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:u3rtPJb9FHA.2716@TK2MSFTNGP11.phx.gbl...
> =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)
>
>
> "kraway" <kraway@discussions.microsoft.com> wrote in message
> news:C612214D-4DAB-4120-AC64-BAD43FDDC374@microsoft.com...
> > 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!
>
>

[kraway]

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.

"pinmaster" wrote:

>
> HI,
> Try:
> =IF(MONTH(A1)=11,your formula,"")
>
> HTH
> JG
>
>
> --
> pinmaster
> ------------------------------------------------------------------------
> pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
> View this thread: http://www.excelforum.com/showthread...hreadid=489397
>
>

[kraway]

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

"Bob Phillips" wrote:

> 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)
>
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:u3rtPJb9FHA.2716@TK2MSFTNGP11.phx.gbl...
> > =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)
> >
> >
> > "kraway" <kraway@discussions.microsoft.com> wrote in message
> > news:C612214D-4DAB-4120-AC64-BAD43FDDC374@microsoft.com...
> > > 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!
> >
> >
>
>
>

[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)


"kraway" <kraway@discussions.microsoft.com> wrote in message
news:EFFB5FB2-A107-47F4-A67D-E87B62BF6095@microsoft.com...
> Thank you Bob, this looks like it will work. What is meant by "commit
with
> Ctrl-Shift-Enter"
>
> "Bob Phillips" wrote:
>
> > 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)
> >
> >
> > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> > news:u3rtPJb9FHA.2716@TK2MSFTNGP11.phx.gbl...
> > > =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)
> > >
> > >
> > > "kraway" <kraway@discussions.microsoft.com> wrote in message
> > > news:C612214D-4DAB-4120-AC64-BAD43FDDC374@microsoft.com...
> > > > 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!
> > >
> > >
> >
> >
> >