Problem with IF function

[Seamus Conlon]

I have a puzzling problem when using the IF function. My spreadsheet
looks like this:

Date Value 1 2 3 4
1/2006 100
4/2006 450
3/2006 235

If the month in the date in column A matches the month number in the top
row I want to put the value from column B under the matching month
column otherwise I want to insert a zero. So, in the above example, 100
would go in cell C2, 450 in cell F3 etc. Some date cells will be blank
and I have found that if I use a simple formula like:

=IF(MONTH($A2)=C$1,$B2,"0")

in the cells, the rows with blank dates will always have the value inserted
in column C, i.e it seems like the MONTH function returns a 1 if the date
is blank. So, I figured that I needed a formula like

=IF(ISBLANK($A2) = FALSE AND MONTH($A2)=C$1,$B2,"0")

but this gives an error. I have tried a few variations of this with
brackets etc
but I always get an error.

I can't see what is wrong with it!

Thanks for any help.

Seamus

[Bob Phillips]

=IF(AND(NOT(ISBLANK(A2)),MONTH($A2)=C$1),$B2,"0")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Seamus Conlon" <[email protected]> wrote in message
news:[email protected]...
> I have a puzzling problem when using the IF function. My spreadsheet
> looks like this:
>
> Date Value 1 2 3 4
> 1/2006 100
> 4/2006 450
> 3/2006 235
>
> If the month in the date in column A matches the month number in the top
> row I want to put the value from column B under the matching month
> column otherwise I want to insert a zero. So, in the above example, 100
> would go in cell C2, 450 in cell F3 etc. Some date cells will be blank
> and I have found that if I use a simple formula like:
>
> =IF(MONTH($A2)=C$1,$B2,"0")
>
> in the cells, the rows with blank dates will always have the value
inserted
> in column C, i.e it seems like the MONTH function returns a 1 if the date
> is blank. So, I figured that I needed a formula like
>
> =IF(ISBLANK($A2) = FALSE AND MONTH($A2)=C$1,$B2,"0")
>
> but this gives an error. I have tried a few variations of this with
> brackets etc
> but I always get an error.
>
> I can't see what is wrong with it!
>
> Thanks for any help.
>
> Seamus
>
>

[Seamus Conlon]

Thanks Bob, that did the trick.

As usual it was my fault for not looking up the correct use of AND, I had
never used it before in any formula.

Seamus


"Bob Phillips" <[email protected]> wrote in message
news:%[email protected]...
> =IF(AND(NOT(ISBLANK(A2)),MONTH($A2)=C$1),$B2,"0")
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Seamus Conlon" <[email protected]> wrote in message
> news:[email protected]...
>> I have a puzzling problem when using the IF function. My spreadsheet
>> looks like this:
>>
>> Date Value 1 2 3 4
>> 1/2006 100
>> 4/2006 450
>> 3/2006 235
>>
>> If the month in the date in column A matches the month number in the top
>> row I want to put the value from column B under the matching month
>> column otherwise I want to insert a zero. So, in the above example, 100
>> would go in cell C2, 450 in cell F3 etc. Some date cells will be blank
>> and I have found that if I use a simple formula like:
>>
>> =IF(MONTH($A2)=C$1,$B2,"0")
>>
>> in the cells, the rows with blank dates will always have the value
> inserted
>> in column C, i.e it seems like the MONTH function returns a 1 if the date
>> is blank. So, I figured that I needed a formula like
>>
>> =IF(ISBLANK($A2) = FALSE AND MONTH($A2)=C$1,$B2,"0")
>>
>> but this gives an error. I have tried a few variations of this with
>> brackets etc
>> but I always get an error.
>>
>> I can't see what is wrong with it!
>>
>> Thanks for any help.
>>
>> Seamus
>>
>>
>
>

[Bob Phillips]

That one was a bit unusual in that you had to add the NOT to get the result.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Seamus Conlon" <[email protected]> wrote in message
news:[email protected]...
> Thanks Bob, that did the trick.
>
> As usual it was my fault for not looking up the correct use of AND, I had
> never used it before in any formula.
>
> Seamus
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:%[email protected]...
> > =IF(AND(NOT(ISBLANK(A2)),MONTH($A2)=C$1),$B2,"0")
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Seamus Conlon" <[email protected]> wrote in message
> > news:[email protected]...
> >> I have a puzzling problem when using the IF function. My spreadsheet
> >> looks like this:
> >>
> >> Date Value 1 2 3 4
> >> 1/2006 100
> >> 4/2006 450
> >> 3/2006 235
> >>
> >> If the month in the date in column A matches the month number in the
top
> >> row I want to put the value from column B under the matching month
> >> column otherwise I want to insert a zero. So, in the above example, 100
> >> would go in cell C2, 450 in cell F3 etc. Some date cells will be blank
> >> and I have found that if I use a simple formula like:
> >>
> >> =IF(MONTH($A2)=C$1,$B2,"0")
> >>
> >> in the cells, the rows with blank dates will always have the value
> > inserted
> >> in column C, i.e it seems like the MONTH function returns a 1 if the
date
> >> is blank. So, I figured that I needed a formula like
> >>
> >> =IF(ISBLANK($A2) = FALSE AND MONTH($A2)=C$1,$B2,"0")
> >>
> >> but this gives an error. I have tried a few variations of this with
> >> brackets etc
> >> but I always get an error.
> >>
> >> I can't see what is wrong with it!
> >>
> >> Thanks for any help.
> >>
> >> Seamus
> >>
> >>
> >
> >
>
>

[RagDyeR]

OR ... how about simply using:

=IF(AND($A2<>"",MONTH($A2)=C$1),$B2,"0")

?

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------


"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
That one was a bit unusual in that you had to add the NOT to get the result.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Seamus Conlon" <[email protected]> wrote in message
news:[email protected]...
> Thanks Bob, that did the trick.
>
> As usual it was my fault for not looking up the correct use of AND, I had
> never used it before in any formula.
>
> Seamus
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:%[email protected]...
> > =IF(AND(NOT(ISBLANK(A2)),MONTH($A2)=C$1),$B2,"0")
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Seamus Conlon" <[email protected]> wrote in message
> > news:[email protected]...
> >> I have a puzzling problem when using the IF function. My spreadsheet
> >> looks like this:
> >>
> >> Date Value 1 2 3 4
> >> 1/2006 100
> >> 4/2006 450
> >> 3/2006 235
> >>
> >> If the month in the date in column A matches the month number in the
top
> >> row I want to put the value from column B under the matching month
> >> column otherwise I want to insert a zero. So, in the above example, 100
> >> would go in cell C2, 450 in cell F3 etc. Some date cells will be blank
> >> and I have found that if I use a simple formula like:
> >>
> >> =IF(MONTH($A2)=C$1,$B2,"0")
> >>
> >> in the cells, the rows with blank dates will always have the value
> > inserted
> >> in column C, i.e it seems like the MONTH function returns a 1 if the
date
> >> is blank. So, I figured that I needed a formula like
> >>
> >> =IF(ISBLANK($A2) = FALSE AND MONTH($A2)=C$1,$B2,"0")
> >>
> >> but this gives an error. I have tried a few variations of this with
> >> brackets etc
> >> but I always get an error.
> >>
> >> I can't see what is wrong with it!
> >>
> >> Thanks for any help.
> >>
> >> Seamus
> >>
> >>
> >
> >
>
>