Using these forums (which are absolutely brilliant!) I have built the
following formula:

=IF(MID($M6,1,1)="x","",IF(--(COUNTIF($B$17:$B$108,N$4)0)=1,"#",""))

I now have the problem of trying to explain what this does, but that my
question, could someone give me an explanation of what this formula is
saying, in particular the -- section?

Many thanks
--
J

First, the =countif()0 will return a true/false.
the -- stuff in front of it will change the true/false to 1/0.

So instead of checking for 1 = 1, you could just check for true = true.

If fact, you could just drop the = true portion:

=IF(MID($M6,1,1)="x","",IF(COUNTIF($B$17:$B$108,N$ 4)0,"#",""))

If the first character in M6 = "x" then
show nothing ("")
else
if there's at least one value equal to N4 in B17:B108 then
show "#"
else
show nothing ("")
end if
end if

I would have used:
=IF(left($M6,1)="x","",IF(COUNTIF($B$17:$B$108,N$4 )0,"#",""))

(just because =left() is easier to see that I'm testing the first character.)

James wrote:

Using these forums (which are absolutely brilliant!) I have built the
following formula:

=IF(MID($M6,1,1)="x","",IF(--(COUNTIF($B$17:$B$108,N$4)0)=1,"#",""))

I now have the problem of trying to explain what this does, but that my
question, could someone give me an explanation of what this formula is
saying, in particular the -- section?

Many thanks
--
J

--

Dave Peterson

Thanks Dave,

I will give your formula version a go. It also makes it easier to re-enter
should anything go wrong.

Thanks
--
J


"Dave Peterson" wrote:

First, the =countif()0 will return a true/false.
the -- stuff in front of it will change the true/false to 1/0.

So instead of checking for 1 = 1, you could just check for true = true.

If fact, you could just drop the = true portion:

=IF(MID($M6,1,1)="x","",IF(COUNTIF($B$17:$B$108,N$ 4)0,"#",""))

If the first character in M6 = "x" then
show nothing ("")
else
if there's at least one value equal to N4 in B17:B108 then
show "#"
else
show nothing ("")
end if
end if

I would have used:
=IF(left($M6,1)="x","",IF(COUNTIF($B$17:$B$108,N$4 )0,"#",""))

(just because =left() is easier to see that I'm testing the first character.)

James wrote:

Using these forums (which are absolutely brilliant!) I have built the
following formula:

=IF(MID($M6,1,1)="x","",IF(--(COUNTIF($B$17:$B$108,N$4)0)=1,"#",""))

I now have the problem of trying to explain what this does, but that my
question, could someone give me an explanation of what this formula is
saying, in particular the -- section?

Many thanks
--
J

--

Dave Peterson