Excel: Significant Figures/Digits 1

[Eytch]

Is a formatting feature, command, or property in excel that allows you to assign the number of significant figures a result will have. I am writing a macro where I want all results generated to have two significant digits. The only thing I can find that comes close is the "round" function.

Thanks,
Tripoli

 

[ETID]

Have you tired...

format>custom, then typing the display you that you want

I.E.


0.00

or 000.000 ...what ever( to a certain degree )

 

[mmtraining]

Let's say you want to always have the three figures after a decimal point without having it rounded up or down, then you need to do three things: multiply by 1000, create the integer of the figure and divide by 1000. Eg. 123.4567 x 1000 = 123456.7. The integer of that is 123456. Divide that by 1000, you get 123,456.

To do that to a figure in A3, type the following into B3

=int(A3*1000)/1000

All the best

Carol, Berlin

 

[Loomah]

To simplify Carol's suggestion you could use the TRUNC function if you don't want the figure rounded.

EG
=trunc(123.4567,3) gives 123.456
However you will still have to change the formatting as ETID suggested to 'see' trailing zeros etc.

Happy Friday
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[mmtraining]

Hey, Loomah, that's cheating, using a built-in function that I had forgotten about. A star is yours.

All the best from Berlin

Carol

 

[Eytch]

Loomah,

I have wanted to contact you for some time. I want to show you some of the programs I have written utilizing some of your ideas and suggestions. Is there a way to get your e-mail address?

As far as the significant figures go I want to be able to round a number to two significant figures to the left of the decimal point. I want to end up with a whole number. I'll try some of the suggestions offered in the responses I received.

Thanks,
Tripoli

 

[Loomah]

Tripoli
I generally won't post my email address but if you post yours I will contact you. Alternatively (and I don't know if this works) there is an option to send comments on FAQs. You could try that for the one I've done in the VBA forum.

If all else fails I'll post it but only as a last resort!

As for the rounding problem I think the CEILING function is what you are looking for.

Happy Friday (still, just!)
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[Eytch]

Loomah,
The Round function worked along with Carol's suggestion. I used the Round function with a negative number as the digit to get it to work to the left of the decimal point. trunc and ceiling would not work in VBA, only as worksheet functions. I'm writing a macro.

Thanks,
Tripoli

 

[Loomah]

Tripoli
CEILING is available to VBA (at least in xl2k) although TRUNC isn't! Try the following:-

MsgBox WorksheetFunction.Ceiling(123456.7891, 1000)

One thing I have just realised is that it always rounds UP so in this case the result would be 124000!! Funny what you say about using round with a negative. I've never tried it but was just wondering after my last post if it would work - though too late to try it.

Anyway, I would be interested to see the files you want to show me so rather than the pratting on I suggested before here's my email bellm@globalnet.co.uk

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244