Formula results in dreaded #VALUE!

  • Thread starter Thread starter JMay
  • Start date Start date
J

JMay

When my cell B7 = MLC/Harr 4138 Log CC
and D7 >>> =IF(FIND("CC",B7)>0,"VS",IF(FIND("Disc",B7)>0,"DC","RG"))

D7 is displaying CC << Fine OK.

When B7 = MLC/Harr 4138 Log Disc

D7 is displaying #VALUE! <<s/b DC

When B7 = MLC/Harr Log Jan04

D7 is displaying #VALUE! <<s/b RG

I'm missing something; any help certainly appreciated.
 
=IF(NOT(ISERROR(FIND("CC",B7)>0)),"VS",IF(NOT(ISERROR(FIND("Disc",B7)>0)),"D
C","RG"))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Find returns #VALUE! if the search text is not found. Try:


=IF(OR(COUNTIF(B7,"*CC*"), COUNTIF(B7,"*Disc*")),
IF(COUNTIF(B7,"*CC*"), "CC","Disc"), "Not found")
 
Hi

The FIND function returns #VALUE if there is no occurrence. So when it
checks B7 for CC (before it gets to the Disc option) it returns an error

Andy.
 
Thanks Andy, but I'm not getting what you are saying. In all cases where my
Desc (B7) cell includes CC, D7 IS displaying correctly as VS. << So it is
working only with the first option only.. ?? can you clarify?
 
Take a look at FIND() in Help. It has a good explanation of why you get
the #VALUE! error.
 
Thanks JE,
You and Bob hit the nail on the head, using two different brands of hammers
(LOL),
just wasn't clear on A's comment (which I certainly appreciated, just
wanting to
learn why,,) I'll check out Find() in Help as you suggest for the
specifics,
Thank to all,
J
 
Sorry, been out.

If you edit the formula in the formula bar, select the first
FIND("CC",B7)>0 and drag your cursor over it and hit F9, this will show you
the result of this bit, and you can do the same with the other bits.
It's just a simple way to quantify what's in your formula and what results
it is working with.
If, having pressed F9, you hit enter, rather than Esc, it will change your
formula to be the revised version. Always make sure you quit the F9 function
with Esc unless you want your formula changed.

Andy
 
Back
Top