Why doesn't this work?

[yovation]

=CONCATENATE(IF(B3>0,B3,""),IF(C3>0,C3&", ",""),IF(D3>0,D3&",
",""),IF(E3>0,E3&", ",""))

When any of the logicals are false, the formula still displays the ,

Assistance please?

Thank you

 

[Niek Otten]

I can't reproduce that!

Please give the values of all cells involved and what result you get.

It may help to evaluate your formula step by step, using Tools>Formula Auditing>Evaluate Formula (not too old versions of Excel)


--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| =CONCATENATE(IF(B3>0,B3,""),IF(C3>0,C3&", ",""),IF(D3>0,D3&",
| ",""),IF(E3>0,E3&", ",""))
|
| When any of the logicals are false, the formula still displays the ,
|
| Assistance please?
|
| Thank you
|

 

[PCLIVE]

Your formula seems to work for me.
Are you referring to a comma displaying at the end or beginning or what? I
set all criteria to zero and there were no commas displayed.

 

[Harlan Grove]

=CONCATENATE(IF(B3>0,B3,""),IF(C3>0,C3&", ",""),
IF(D3>0,D3&", ",""),IF(E3>0,E3&", ",""))

When any of the logicals are false, the formula still displays the ,

To see what's happening, enter FALSE in, say, X99 and =X99>0 in Y99.
Unless you're running under the Transition Formula Evaluation setting,
the Y99 formula will return TRUE. Why? Because in Excel's wonderful
default semantics, any numeric value < any text value < FALSE < TRUE.

Next, if you can use & inside the IF calls, use it outside the IF
calls too. There's never a good reason to use CONCATENATE.

So could B3:E3 contain either numbers or booleans (TRUE/FALSE)? You
want only positive numbers displayed?

If you want only the positive numbers included, and commas separating
multiple ones, you need to use something like this.

=SUBSTITUTE(TRIM(IF(COUNTIF(B3,">0"),B3,"")&" "
&IF(COUNTIF(C3,">0"),C3,"")&" "&IF(COUNTIF(D3,">0"),D3,"")&" "
&IF(COUNTIF(E3,">0"),E3,""))," ",", ")

 

[Harlan Grove]

=CONCATENATE(IF(B3>0,B3,""),IF(C3>0,C3&", ",""),
IF(D3>0,D3&", ",""),IF(E3>0,E3&", ",""))

When any of the logicals are false, the formula still displays the ,

To see what's happening, enter FALSE in, say, X99 and =X99>0 in Y99.
Unless you're running under the Transition Formula Evaluation setting,
the Y99 formula will return TRUE. Why? Because in Excel's wonderful
default semantics, any numeric value < any text value < FALSE < TRUE.

Next, if you can use & inside the IF calls, use it outside the IF
calls too. There's never a good reason to use CONCATENATE.

So could B3:E3 contain either numbers or booleans (TRUE/FALSE)? You
want only positive numbers displayed?

If you want only the positive numbers included, and commas separating
multiple ones, you need to use something like this.

=SUBSTITUTE(TRIM(IF(COUNTIF(B3,">0"),B3,"")&" "
&IF(COUNTIF(C3,">0"),C3,"")&" "&IF(COUNTIF(D3,">0"),D3,"")&" "
&IF(COUNTIF(E3,">0"),E3,""))," ",", ")

 

[Guest]

=IF(COUNTA(B3:E3),SUBSTITUTE(IF(B3>0,B3&",","")&IF(C3>0,C3&",","")&IF(D3>0,D3&",","")&IF(E3>0,E3,""),",","",COUNTA(B3:E3)),"")

 

[yovation]

That is too strange. I created a new sheet and the formula works.
wow. I have no idea what happened.

Thank you for your help!!

David

 

[yovation]

hmmm... I decided to try the formula on a clean sheet, and it works
with new data.

Then I tried it again on my original data, and the problem continues
to occur. So I think Excel is seeing something from my data that is

0 in a cell that data isn't visible in. I don't have a clue on how

to go about figuring that out. I did a (TRIM(CLEAN( on the data, but
that doesn't make a difference. Very strange.

Thank you.
David

 

[Mark Lincoln]

I don't know how you produce the values in the four cells, but there
could be a rounding error, inherent in storing decimal numbers in a
binary format, that could cause calculations to produce a very small
number but display as zero.

If B3, C3, etc are supposed to be integers, you can use ROUND(B3,0)>0,
etc, to counteract this. If not, you could use ROUND in B3, etc, or
in formulas those cells depend upon, to eliminate the error.

Mark Lincoln