IF function anomaly

[BBS]

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]

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]

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]

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.

[BBS]

Hopefully the sheet should be attached.

Thanks for your time!

[mikerickson]

Using relative referenced would help.
But if you are dragging that formula along a row,

=(N4="overdue","overdue","OK") is another formulation.

[oldchippy]

Hopefully the sheet should be attached.

Thanks for your time!

Shouldn't the query be something like this in N7, then drag along to S7

=IF(N4>$N$2,"ok","overdue")

[BBS]

Sorry, think I may have confused the issue by not removing the test cells.

The formula I'm having issues with is working correctly in O2. When I try to move this formula into cell A4 (which is where it's req'd) it returns #value!
(The formula only returns the expected answer when placed in columns N:S)

I'll attempt to explain what I'm trying to achieve, without draining you of the will to live:

We have a delivery date, which we need to reference to highlight when information is required (ie. 3 weeks before delivery).
The result needs to show either the date the information is requierd by, or "overdue" depending on todays date.
We then need to check the result of this series of cells to determine whether any of them are overdue, which is the problem formula.

[NBVC]

The formula you are using only looks at N4, not the whole range unless you confirm it with CTRL+SHIFT+ENTER....and it still won't give the correct result.

=IF(OR(N4:S4="overdue"),"overdue","ok") confirmed with CSE keys will give correct result or...

Try:

=IF(ISNUMBER(SEARCH("overdue",$N$4:$S$4)),"Overdue","ok") instead (doesn't need CSE key confirmation)

[Richard Schollar]

Try:

=IF(ISNUMBER(SEARCH("overdue",$N$4:$S$4)),"Overdue","ok") instead (doesn't need CSE key confirmation)

I think you meant MATCH there Vittorio


=IF(ISNUMBER(MATCH("overdue",$N$4:$S$4,0)),"Overdue","ok")

Richard

[BBS]

Hi

Thanks for the answers

I've just tried both, the =IF(OR(N4:S4="overdue"),"overdue","ok") formula seems to work perfectly
The =IF(ISNUMBER(SEARCH("overdue",$N$4:$S$4)),"Overdue ","ok") seems to be returning false unless all queried cells are true, whereas I need it to return true unless all are false.

Thanks again, I can finally remove my head from the desk and try to get something done!!!

Just seen "Match" post, works a treat!

Thanks to all!

[NBVC]

...oops... sorry about that.... Richard kindly found my fault and suggested a better alternative (thanks Richard)....

You could use the Isnumber(search combo too, but you would need to add an Or() around it and confirm it with CSE keys.

Please Login or Register  to view this content.

confirmed with CTRL+Shift+Enter would work.... but Richard's version needs no special confirmation...just Enter....