IF function anomaly

**BBS** · 12-07-2007, 07:57 AM

Hi

I have an IF statement which shows the expected answer in the formula wizard, but returns "#VALUE!" in the cell, depending on location. The formula displays correctly if it's in the same column as one of the queried cells, but doesn't if placed anywhere else.
I have checked the cell formatting and this is the same throughout the sheet.

The spreadsheet has been written in Excel 2000.

For info, the problem formula is:
=IF(N4:S4="overdue","Overdue","ok")

Where the queried cells (N4:S4) contain variations of the following:
=IF(AG4<N2+22,"overdue",AG4-21)

AG4 is a specified date, and N2 is today's date.

Many thanks for your time.

**oldchippy** · 12-07-2007, 08:07 AM

An extract from the help menu, this may be the cause

Microsoft Excel cannot translate the text into the correct data type. Make sure the formula or function is correct for the required operand or argument, and that the cells that are referenced by the formula contain valid values. For example, if cell A5 contains a number and cell A6 contains the text "Not available", the formula =A5+A6 will return the error #VALUE!.

**BBS** · 12-07-2007, 09:17 AM

Thanks for the reply

The formula works fine if it's in one of the queried columns (ie. N:S), but if I move it to a different column it returns the #VALUE!
I have made sure that the correct cell ref's have been carried over when the problem occurs, and also that the cell format is the same, which is why I can't understand why it only works in certain columns.

**oldchippy** · 12-07-2007, 09:24 AM

Is it possible to drop a sample with the problem, so that we can try and find it?

To post a zipped example, go to “My Documents” or wherever you have your file stored, right-click and “Send to > Compressed file”, then attach this zipped file to your post.