AG wrote:
Harold Fuchs wrote:
2009/7/9 AG <[email protected]>
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
Actually, I think that I have just sorted this out - I converted the
data to a common exponent and in the columns formatted the data to
user-defined and excluded the "E+" part.
Now seems to work alright.
Thanks
AG
