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

Patrickcabo

New Member
Joined
Aug 17, 2014
Messages
3
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,""))
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
If you are going to sum the entire concatenate formula, then just replace all the "" with a 0.
 
Upvote 0
Unless you are talking about that concatenate formula being in one cell, then another concatenate formula being in another cell, then assuming those values.
 
Upvote 0
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.
 
Last edited:
Upvote 0
Okay the easiest thing to do is surround your Concatenate formula in a Value formula.
Code:
=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
A
B
C
1
5
5
55
2
5
5
55
110

<tbody>
</tbody>
 
Last edited:
Upvote 0
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:
Code:
CHOOSE(BQ11-2, CB11:CN11) * BU11
would work
 
Upvote 0
Thank you all for your help. The 0+ worked. I am just no sophisticated enough with Excel to use lookup tables.
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top