Formula returns text that I cannot sum and cannot convert to number.

[Patrickcabo]

Hello, I have a formula using CONCATENATE because I have more than 7 nested ifs. This formula returns the right answer but I cannot sum the result of the formula. The answer returns in text and I don't know how to convert it to a number. I tried replacing all the "" in the FALSE part of the if functions but that did not do it.

Thanks for any comments. Formula below.


=CONCATENATE(IF($BQ11=3,CB11*$BU11,""),IF($BQ11=4,CC11*$BU11,""),IF($BQ11=5,CD11*$BU11,""),IF($BQ11=6,CE11*$BU11,""),IF($BQ11=7,CF11*$BU11,""),IF($BQ11=8,CG11*$BU11,""),IF($BQ11=9,CH11*$BU11,""),IF($BQ11=10,CI11*$BU11,""),IF($BQ11=11,CJ11*$BU11,""),IF($BQ11=12,CK11*$BU11,""),IF($BQ11=13,CL11*$BU11,""),IF($BQ11=14,CM11*$BU11,""))

 

[WarPigl3t]

If you are going to sum the entire concatenate formula, then just replace all the "" with a 0.

 

[WarPigl3t]

Wait wait wait. You can't sum the concatenate. You don't even have a delimiter.

 

[WarPigl3t]

Unless you are talking about that concatenate formula being in one cell, then another concatenate formula being in another cell, then assuming those values.

 

[Marcelo Branco]

You can simply add 0 (zero) to your formula, i.e.,
=0+CONCATENATE (.....

But I suggest a simpler formula
=IFERROR(BU11*INDEX(CB11:CM11,MATCH(BQ11,{3,4,5,6,7, 8,9,10,11,12,13,14},0)),"")

Hope this helps

M.

 

[WarPigl3t]

Okay the easiest thing to do is surround your Concatenate formula in a Value formula.

=Value(Concatenate(.........))

Then you can sum it all. I just tried it. It works. Columns A and B are my dataset. The value(concatenate(...)) formula is in C1 and C2. Then my sum formula is in C3
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[TD]55
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[TD]55
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]110
[/TD]
[/TR]
</tbody>[/TABLE]

 

[dcardno]

Two ideas (maybe two-and-a-half):
1) Concatenate always returns a string. You can convert it to a numeric value by wrapping the formula you have in a VALUE(string) function
2) That is an ugly formula. You might find it easier to maintain (and other users will find it easier to understand) if you change it either to a lookup table or a choose function.
It appears that:

CHOOSE(BQ11-2, CB11:CN11) * BU11

would work

 

[Patrickcabo]

Thank you Marcelo. The 0+ worked perfectly.

 

[Patrickcabo]

Thank you all for your help. The 0+ worked. I am just no sophisticated enough with Excel to use lookup tables.

 

[Rick Rothstein]

But I suggest a simpler formula
=IFERROR(BU11*INDEX(CB11:CM11,MATCH(BQ11,{3,4,5,6,7, 8,9,10,11,12,13,14},0)),"")

If I am not mistaken, I believe this even simple formula will work...

=CU11*INDEX(CB11:CM11,1,BQ11-2)