Scientific Notation

[Jaimee001]

Hi Team,
I get a huge data dump of monthly volumes based on local currency. At this time (since there are so many different currencies) I don't do any duodecimalization. So when I add up the different months of revenue the result is in Scientific Notation. here's an example:
Monthly Amounts
29040820300
26389362000
26440023000
28854681000
30790307000
29691850000
26407167000
27262100000
34467064400
35438586000
2.94782E+11 TOTal

I have a few questions:
is there a way (using excel) to get to total in a general format?
in your opinion, would providing the duodecimalization upfront help?
and would there be any VB code to help?

Thank you in advance
Jaimee

 

[SirJB7]

Hi, Jaimee001!
Tried formatting the new range (or the whole column, or the whole worksheet) by default as number with 2 decimal digits? Doing so I get 2.947.819.607,00 (accordingly to regional configuration settings).
If what I'm saying doesn't apply, would you please explain what means duodecimalization?
Regards!

 

[Hui]

Jaimee,

Are you referring to: http://en.wikipedia.org/wiki/Duodecimal

 

[Jaimee001]

Hi All,
Sorry, I didn't explain it well. The data that I get is in different currencies without any decimal places. Different currencies can be 'decimalized' differently. For example, the above numbers are in Hungarian Forints which uses 2 decimal places, Japanese Yen has no decimal places and iraqui Dinar has 3 decimal places. When I add up the 10 mos of HUF currency the total is over 15 digits which excel turns into scientific notation. I'm looking for a way to keep the numbers that are over 15 digits as a number. Hopefully that makes more sense. thanks in advance,,,,,

 

[NARAYANK991]

Hi ,

What you want is not possible using Excel on its own ; check out these links :

http://superuser.com/questions/245109/add-more-than-20-digits-in-excel-without-an-exponential-sign

http://www.pcreview.co.uk/forums/improve-excel-accuracy-over-15-digits-t3877354.html

http://precisioncalc.com/What_is_xlPrecision.html

Narayan

 

[SirJB7]

Hi, Jaimee001!
Give a look at the uploaded file. It's an alternative storing the values as text and "duodecimalization" with this formula:
=IZQUIERDA(D2;LARGO(D2)-BUSCARV(E2;A:B;2;FALSO))&SI(BUSCARV(E2;A:B;2;FALSO)>0;","&DERECHA(D2;BUSCARV(E2;A:B;2;FALSO));"") -----> in english: =LEFT(D2,LEN(D2)-VLOOKUP(E2,A:B,2,FALSE))&IF(VLOOKUP(E2,A:B,2,FALSE)>0,","&RIGHT(D2,VLOOKUP(E2,A:B,2,FALSE)),"")
Regards!

 

[Jaimee001]

Thank you all for your responses....