Conversion Question

[braddy60]

Hi I trying to convert kilos to pounds and then to stones & pounds, but I get too many digits on the end of the pounds, I have tried to use the rounddown but I can't get it to work.

any help would be appreciated

Regards

Braddy

[prasad]

[quote name='braddy60' post='791258' date='29-Aug-2009 13:36']Hi I trying to convert kilos to pounds and then to stones & pounds, but I get too many digits on the end of the pounds, I have tried to use the rounddown but I can't get it to work.

any help would be appreciated

Regards

Braddy[/quote]
Looking for something like this?

[braddy60]

[quote name='prasad' post='791262' date='29-Aug-2009 09:20']Looking for something like this?[/quote]


Thanks for the prompt reply much appreciated.

Regards

Braddy

[wdwells]

Since the measured value (67.9 kilos) has only 3 significant digits (sd), the results should be limited to 3 sd i.e. 149 pounds and 10 stones, 9 pounds. My challenge is to develop a technique to achieve this through formulae. Any ideas?

[HansV]

One could also argue that the kg value is accurate to one decimal place. Since 0.1 kg ~ 0.2 lb, one could round to the nearest multiple of 0.2 lb using the MROUND function from the Analysis ToolPak.

[wdwells]

[quote name='HansV' post='791283' date='29-Aug-2009 10:32']One could also argue that the kg value is accurate to one decimal place. Since 0.1 kg ~ 0.2 lb, one could round to the nearest multiple of 0.2 lb using the MROUND function from the Analysis ToolPak.[/quote]
I believe that the scientific world would take this view.

[HansV]

But surely someone who wants to display a weight in stones and pounds is not interested in the view of the scientific world?

[wdwells]

[quote name='HansV' post='791287' date='29-Aug-2009 10:48']But surely someone who wants to display a weight in stones and pounds is not interested in the view of the scientific world? [/quote]

Good gracious Hans; EVERYONE should be interested in the view of the scientific world. But the discussion in my post was a segue into the challenge of presenting the results of a calculation with the appropriate number of significant digits (in a scientists view), without the intervention of the user.

[HansV]

... the challenge of presenting the results of a calculation with the appropriate number of significant digits ...

That'd be more suitable for a separate topic. You could set number format to Scientific with a number of decimal places one less than the number of significant digits, but then you always get an exponent with it. Otherwise you'd need VBA code in the Worksheet_Change or Worksheet_Calculate event.

If you're willing to use a formula: with a number in A1, the following formula will display the value with 3 significant digits:

=LEFT(TEXT(A1,"0.00E+00"),4)*10^RIGHT(TEXT(A1,"0.0 0E+00"),3)

[sdckapr]

[quote name='wdwells' post='791291' date='29-Aug-2009 11:10']But the discussion in my post was a segue into the challenge of presenting the results of a calculation with the appropriate number of significant digits (in a scientists view), without the intervention of the user.[/quote]

I expressed some of my thoughts on using Excel for Sig Figs a number of years back...

Steve

[wdwells]

[quote name='sdckapr' post='791310' date='29-Aug-2009 14:21']I expressed some of my thoughts on using Excel for Sig Figs a number of years back...

Steve[/quote]

Bullseye!!! Thank you Steve.

[braddy60]

[quote name='HansV' post='791294' date='29-Aug-2009 16:25']I could comment on that but it would violate the Lounge rules...


That'd be more suitable for a separate topic. You could set number format to Scientific with a number of decimal places one less than the number of significant digits, but then you always get an exponent with it. Otherwise you'd need VBA code in the Worksheet_Change or Worksheet_Calculate event.

If you're willing to use a formula: with a number in A1, the following formula will display the value with 3 significant digits:

=LEFT(TEXT(A1,"0.00E+00"),4)*10^RIGHT(TEXT(A1,"0.0 0E+00"),3)[/quote]

Thanks to all who replied to this post, suffice it to say, Prasad's reply was more than adequate for my requirements.

Best Regards

Braddy