If formula based on digits?

H

Hamed parhizkar

Is there anyway to pull out certain digits based on rounding?

In A1...143.265 So my if would be =if(A1<>0,A1,"")

However I only want the 2 digits after the decimal to pull. I know I can do
a middle, or right formula but how would this work if I had several rows some
with 1.199 and others with 145.258?

Please help
 
S

Sandy Mann

Do you want them as whole numbers like 19 and 25 or as rounded numbers like
20 & 26 or even as decimale like 0.19 & 0.25 or rounded like 0.2 & 0.26?

For whole unrounded numbers use:

=INT(MOD(A1,1)*100)

For rounded whole numbers use:

=ROUND(MOD(A1,1)*100,0)

For unrounded decimals use:

=INT(MOD(A1,1)*100)/100

For rounded decimal numbers use:

=ROUND(MOD(A1,1),2)


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
R

Rick Rothstein \(MVP - VB\)

The two digits you want "pulled"... do you want them as Text or as Numbers.
I ask because there is a question of what to give you for something like
123.02 and 123.20, or 12.00, if you want them to be numbers after they are
"pulled".

Rick
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top