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]

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]

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)

 

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


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)