eliminating scientific notation in concatenated cell

[Weagle]

Hi experts,

I've searched high and low for a solution to this issue, I'm hoping that someone can help.

I have a polynomial equation that I've calculated using a LINEST array. I am trying to output this equation in a specific format for a 3rd party program to process.

In order to output the equation in a usable format, I am taking the results of the LINEST array (cells B2 to F2 below) one named variable ($A$1 below, value= variable), and one formula spelled out (cell E1 below, value = pow)

The LINEST array outputs these 5 values, which I must then concatenate into a usable formula. The values are shown exactly as below in the cells, and I have the cells formatted to 22 decimal places, more than enough as you can see by the 0's at the end of each string.

-0.00000053208961977968600 (cell B2)
0.00009929033179823260000 (cell C2)
-0.01207609335165210000000 (cell D2)
3.13274743658612000000000 (cell E2)
-0.00302958687091182000000 (cell F2)

The concatenation equation that I am using:
="("&B2&"*"&E1&"("&$A$1&",4))+(" &C2&"*"&E1&"("&$A$1&",3))+(" &D2&"*"&E1&"("&$A$1&",2))+(" &E2&"*"&$A$1&")+" &F2

The output concatenated formula from the above equation then is:
(-5.32089619779686E-07* pow( variable ,4))+(9.92903317982326E-05* pow( variable ,3))+(-0.0120760933516521* pow( variable ,2))+(3.13274743658612* variable )+-0.00302958687091182

No matter what I try, I'm unable to get Excel to output the concatenated formula without scientific notation (shown in red and bold above). My 3rd party program can't accept scientific notation, and I have thousands of equations to transfer, so converting by hand is not really an option.

Is there a solution?

Thanks for your help!

 

[mikerickson]

One way around it would be to replace E1 with TEXT(E1,"0.###############") throughout your formula.

That would be a heck of a long formula, so a Named value could be used
Select a cell in Row 1 and define
Name: A1Full RefersTo: =TEXT(Sheet1!$A$1, "0.###############")
Name: ECellFull RefersTo: =TEXT(Sheet1!$E1, "0.###############")
Note the relative referencing in ECellFull.
Then the formula becomes
="("&B2&"*"&ECellFull&"("&$A$1&",4))+(" &C2&"*"&ECellFull&"("&A1Full&",3))+(" &D2&"*"&ECellFull&"("&A1Full&",2))+(" &E2&"*"&A1Full&")+" &F2

 

[Weagle]

One way around it would be to replace E1 with TEXT(E1,"0.###############") throughout your formula.

Thanks Mike! My variable that had the notation issue was [B2-F2], but I understood the gist of what you were saying, applied, and it worked great.

Thanks a lot!

Dave