=CONCATENATE(IF(B30,B3,""),IF(C30,C3&", ",""),IF(D30,D3&",
",""),IF(E30,E3&", ",""))

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

Assistance please?

Thank you

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 ToolsFormula AuditingEvaluate Formula (not too old versions of Excel)


--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"yovation" wrote in message ups.com...
| =CONCATENATE(IF(B30,B3,""),IF(C30,C3&", ",""),IF(D30,D3&",
| ",""),IF(E30,E3&", ",""))
|
| When any of the logicals are false, the formula still displays the ,
|
| Assistance please?
|
| Thank you
|

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

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.

--

"yovation" wrote in message
ups.com...
=CONCATENATE(IF(B30,B3,""),IF(C30,C3&", ",""),IF(D30,D3&",
",""),IF(E30,E3&", ",""))

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

Assistance please?

Thank you

yovation wrote...
=CONCATENATE(IF(B30,B3,""),IF(C30,C3&", ",""),
IF(D30,D3&", ",""),IF(E30,E3&", ",""))

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

To see what's happening, enter FALSE in, say, X99 and =X990 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,""))," ",", ")

yovation wrote...
=CONCATENATE(IF(B30,B3,""),IF(C30,C3&", ",""),
IF(D30,D3&", ",""),IF(E30,E3&", ",""))

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

To see what's happening, enter FALSE in, say, X99 and =X990 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,""))," ",", ")

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

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

On Oct 18, 2:13 pm, yovation wrote:
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 is0 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

=IF(COUNTA(B3:E3),SUBSTITUTE(IF(B30,B3&",","")&IF (C30,C3&",","")&IF(D30,D3&",","")&IF(E30,E3,"") ,",","",COUNTA(B3:E3)),"")


"yovation" wrote:

=CONCATENATE(IF(B30,B3,""),IF(C30,C3&", ",""),IF(D30,D3&",
",""),IF(E30,E3&", ",""))

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

Assistance please?

Thank you