Calc: Using scientific numbers

[AG-21]

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

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Harold Fuchs-2]

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


--
Harold Fuchs
London, England
Please reply *only* to users@...

[AG-21]

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

[AG-21]

AG wrote:

> 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

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

[Harold Fuchs-2]

2009/7/9 AG <computing.account@...>

> 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
>

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 users@...

[Robin Laing]

AG wrote:

> 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
>

This is related to bug/RFE 5930 and could be related to but/RFE 67518.
Please read and vote on the issues.

Glad to see you found a way to work around the limitations.

--
Robin Laing

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Michael Adams-2]

On Thu, 09 Jul 2009 10:20:37 +0100
Came this utterance formulated by AG to my mailbox:

> AG wrote:
> > 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?
> >>>

My method would have been to divide all the results by 1E19 into
another column, force this column to use standard notation, then mark
the graph as(E+19) in the Axis Label. Using E18 as a multiple of three
may be a more acceptable alternative to some.

1E18 = 1,000,000,000,000,000,000 = 1 quintillion or 1 exa(unit) using
SI. Example: the age of the universe is 0.4 exaseconds give or take a
lifetime or two million.


--
Michael

All shall be well, and all shall be well, and all manner of things shall
be well

 - Julian of Norwich 1342 - 1416

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...