[SOLVED] Re: [users] Calc: Using scientific numbers

> Harold Fuchs wrote:
>> 2009/7/9 AG <computing.account@...>
>>
>>  
>>> Hello
>>>
>>> I am using scientific notation in a spreadsheet, but want to round the data
>>> off to a common exponent.
>>>
>>> At present I have data such as:
>>>
>>> 6.8E+15
>>> 1.15E+21
>>> 5.09E+19
>>>
>>> and so on.
>>>
>>> I want to do a graph, but at present the columns are very out of proportion
>>> because of the data with 21 as an exponent.  I therefore wanted to change
>>> this to ^19 which about the most frequently occurring exponent in my data
>>> set.  How do I do this?  At the moment, whenever I try, Cacl very helpfully
>>> (or not!!) automatically changes 115E+19 back to 1.15E+21 and so on.  I
>>> obviously cannot change this to text in terms of the formatting otherwise it
>>> seems as if the chart won't pick it up for display.  So how do I get around
>>> this?
>>>
>>> Thanks
>>>
>>> AG
>>>    
>>
>>
>> One possible kludge:
>>
>>    - Assume, for this discussion, that your data are in column A and that
>>    column B is spare
>>    - in cell B1 enter the formula =log10(a1)
>>    - copy B1 down the column to cover all the values in column A. The result
>>    of this will be that B2 will be set to =log10(a2), B3 will have =log10(a3)
>>    and so on. In your short example, column B will end up containing (top to
>>    bottom): 15.83, 21.06 and19.71
>>    - draw your chart using column B
>>
>>
>>  
> Thanks for the quick response Harold
>
> Have just done what you suggested and the data chart shows relative
> values (using log10). However, the values that are now on the chart
> are clearly not what is listed in the text, but their relative
> proportions seem accurate enough.
>
> Does this mean that there is no way for me to fix the exponent using
> the scientific notation?  It would be a lot more straightforward if I
> could set it up thus:
>
> 6.80E+15 => 0.00068E+19
> 1.15E+21 => 115E+19
> 5.13E+17 => 0.0513E+19
>
> etc.
>
> Perhaps there isn't a workaround, but I was curious as to whether or
> not changing the number formatting for a cell might help?
>
> Cheers
>
> AG