Harold Fuchs wrote:
2009/7/9 AG <computing.acco...@googlemail.com>
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
