Truncating excel numbers

[karlp]

Does anyone know how to truncate the numbers in excel.

If I have a number e.g. 1.665479 in a cell.

I want to truncate it to. 1.66. - and dump the remaining numbers.

I know how to display 1.66 (in format number) but this still retains all the other trailing numbers, which i do not want - so if I export into another program it exports the whole 9 decimal places when I only want to export 2 decimal places

Hope this makes sense

 

[Paul G (BHT)]

Right click the cell or cells you want to set to two decimal places and then click format. Then set the cell properties to number and two decimal places.

 

[sparkplug]

That only changes how the number is displayed Paul.

What karlp wants is the =ROUND command

=ROUND(number,num_digits)

so =ROUND(3.2946,2) will return 3.30 as the result

You can also use it for cells e.g. =ROUND(F3,2) and so on....

 

[karlp]

Right click the cell or cells you want to set to two decimal places and then click format. Then set the cell properties to number and two decimal places.

That only formats the display - the trailing numbers are still there

If I have a cell with. 1.667788

Then do as you indicate it displays 1.66

If i then copy that cell to another location it displays 1.66
But as soon as I change the format to 4 decimal places the cell displays 1.6677

I want it to display 1.66 then when i change format to 4 decimal places i would expect to see 1.6600

When I copy the cell I want it to copy 1.66. Not 1.667788

 

[karlp]

That only changes how the number is displayed Paul.

What karlp wants is the =ROUND command

=ROUND(number,num_digits)

so =ROUND(3.2946,2) will return 3.30 as the result

You can also use it for cells e.g. =ROUND(F3,2) and so on....

Thanks.

I will try that

 

[karlp]

That only changes how the number is displayed Paul.

What karlp wants is the =ROUND command

=ROUND(number,num_digits)

so =ROUND(3.2946,2) will return 3.30 as the result

You can also use it for cells e.g. =ROUND(F3,2) and so on....

Perfect - that does exactly what I want

Is there one for significant figures

E.g. 0.00333. Shows 0.00333

1000.666. Shows 1000

Thanks for the ab above

 

[stickywicket]

Just go to the "number"section on the top bar where you see a left arrow with some zeros beside it or a right arrow with some zeros beside it. Click until you get the desired figure

 

[SBD]

That only formats the display - the trailing numbers are still there

If I have a cell with. 1.667788

Then do as you indicate it displays 1.66

If i then copy that cell to another location it displays 1.66
But as soon as I change the format to 4 decimal places the cell displays 1.6677

I want it to display 1.66 then when i change format to 4 decimal places i would expect to see 1.6600

When I copy the cell I want it to copy 1.66. Not 1.667788

Do you really want it to copy 1.66, or 1.67? Ie truncate, or round?

If the latter, do as sparkplug suggested.

If you really want 1.66, you could use “round down” or you can multiply your number by 100, use the “trunc” or “int” function, and then divide by 100. You can do these in a single formula.

It may be possible to use “trunc” with a num_digits argument as well, but I haven’t tried it.

 

[SBD]

Just go to the "number"section on the top bar where you see a left arrow with some zeros beside it or a right arrow with some zeros beside it. Click until you get the desired figure

You're only changing the display, not the number. That’s not what he wants!

 

[leadfarmer]

You can also use roundup or round down

To truly truncate, use =TRUNC(A1,num_digits) where A1 is the cell with your number in it

 

[karlp]

Good points above - thanks for picking them up

Yes you are correct 1.667 would need to be converted to 1.67

I need to get a book on excel functions etc

I will try them and see which suits the best

 

[leadfarmer]

Good points above - thanks for picking them up

Yes you are correct 1.667 would need to be converted to 1.67

I need to get a book on excel functions etc

I will try them and see which suits the best

So you need the round function as sparkplug said

 

[karlp]

So you need the round function as sparkplug said

Yes the Round function is what I need for the question above - but I think knowing what other functions are available would be useful.

I am trying to setup a LIMS system

At present trying FileMaker which is going well but need to import some numeric data from a scientific instrument via excel.

I have tried Access but not so easy to import the data. (Probably me)

 

[sparkplug]

Honestly, there's so much free information online about Excel functions that I wouldn't personally spend money on a book (although for some people that's the best way to retain information, I'm not one of them!).

Everything I've learned has been through searching for a feature I didn't know how to achieve.

 

[leadfarmer]

Yes the Round function is what I need for the question above - but I think knowing what other functions are available would be useful.

I am trying to setup a LIMS system

At present trying FileMaker which is going well but need to import some numeric data from a scientific instrument via excel.

I have tried Access but not so easy to import the data. (Probably me)

What instrument just out of interest? I spent a lot of time on lab systems and online stuff in another life

 

[karlp]

What instrument just out of interest? I spent a lot of time on lab systems and online stuff in another life

Is is an Agilent LCMS mass spectrometer

The data is taken off the instrument and the results are produced in (Agilent quantitation program)

This can export the data in CS3 or excel

I am looking at getting this into a LIMS for quick reporting.

 

[Oldrat]

Can’t you just rub out the surplus numbers using an eraser?

 

[leadfarmer]

Is is an Agilent LCMS mass spectrometer

The data is taken off the instrument and the results are produced in (Agilent quantitation program)

This can export the data in CS3 or excel

I am looking at getting this into a LIMS for quick reporting.

I used to look after an Agilent Chemstore CDS system collecting data from a shed load of 1100 series HPLCs and 6890 GCs, we had one LC/MS and one GC/MS, oh and a CE

Luckily I changed roles and left the lab just as the company moved all (Agilent or Waters) chromatography systems to Waters Empower software and NuGenesis LIMS, a step backwards in my mind but no longer my problem at the hands on level

 

[karlp]

I used to look after an Agilent Chemstore CDS system collecting data from a shed load of 1100 series HPLCs and 6890 GCs, we had one LC/MS and one GC/MS, oh and a CE

Luckily I changed roles and left the lab just as the company moved all (Agilent or Waters) chromatography systems to Waters Empower software and NuGenesis LIMS, a step backwards in my mind but no longer my problem at the hands on level

ok

Obviously familiar with the stuff - so this is what we have (my son/daughter in law)

Waters APGC system with GC linked to an LCMS. TQ-XS
Agilent 7010GCMSMS with 8890GC - with CTC auto sampler
Agilent 5975 with 8890GC - with CTC auto sampler with Headspace, SPME
Agilent 6470 LCMSMS with 1290 HPLC systems
Thermo DFS high resolution magnetic sector MS

In the company I sold in 2020 had a lot more

QToF systems
5 magnetic sector

 

[leadfarmer]

Bejeesus, you’ve got a few quids worth there !

I was just an employee, one of the big names, API manufacturing site

I left in 2018 for pastures new