2009/7/9 AG <[email protected]>
> 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
>
I think you could do it using a macro but it's beyond my capabilities. For
example, your 1.15E+21 can be formatted as 115.00E+19 using the format
"###.00E+00". So the trick is to calculate the number of "#" characters you
need for each value based on the size of its exponent. Lets say the minimum
[1] exponent is 15. Then the number of "#" marks for cell An (where "n" is
the row number) is int(log10(An)) -15 + 1. You can generate this text value
in cell Bn using the formula
=rept("#";int(log10(An)) -15 + 1)
- the "rept" function repeats its first (text) argument according to the
value of its second argument, so
=rept("abc",3) produces "abcabcabc".
The "+ 1" in the formula is because that you need at least 1 "#" mark; I
could have written the formula as
"...- 16..." but using "...- 15 + 1 ...." makes explicit that you need to
subtract the minimum exponent and then add 1.
You can extend this to produce the format you need by using
=REPT("#";INT(LOG10(An)) - 15 + 1) & ".00E+00"
because the "&" operator concatenates two strings.
So, you can generate a format code dynamically. But now I'm stuck. I can't
find a way to use that dynamic format. I think the only way is via a macro
but, as I said, I don't know enough about writing macros.
[1] Note that the number of "#" marks has to be at least 1, so you have to
base the format on the *minimum* exponent and not on some arbitrary "median"
value. So this method doesn't quite meet your spec. I don't know how to
*increase* the exponent for values below your chosen median so that your
example
5.13E+17 => 0.0513E+19
can't be handled using this method
Thinking more about this I'm not sure that doing what you are trying to do
is useful. Yes, you have "standardised" the exponent but you still have a
huge range of mantissae. In your example
6.80E+15 => 0.00068E+19
1.15E+21 => 115E+19
5.13E+17 => 0.0513E+19
all the exponents are 19 but the mantissae range from 0.0007 to 115 which
would still be quite hard to represent sensibly in a graph. Or ???
Perhaps someone else here can help. Please!
--
Harold Fuchs
London, England
Please reply *only* to [email protected]
