Finding What Remains

C

caveman.savant

If I multiple a whole number by a percentage that is precise to 3
decimal places, how can I get get just the portion of the product that
is to the right of the decimal place.

=sum(48*2.128%) returns 1.0213

I want just .0213
 
S

Sandy Mann

Try:

=MOD(48*2.128%,1)

Although I get 0.02144

--
HTH

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
S

Sandy Mann

The SUM() is not needed, the formula works without it.

--
Regards,

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk


try this

=mod(sum(48*2.128%),1)
 
S

ShaneDevenshire

Hi,

The MOD(Number,Divisor) function returns the remainder after division, so if
you divide by 1 you are taking the remainder after division by 1 which is the
decimal portion of the number.

It is better Excel practice to reference cells than to hard code numbers
into formulas - so in the original example, sum(48*2.128%), it would have
been better to enter 48 into cell A1 and 2.128% into A2 and then calculate
the results in A3.

As already pointed out SUM is not necessary regardless of how you choose to
solve the problem. This is bad formula design to use things such as
=SUM(A1*B1)
=SUM(A1/B1)
=SUM(A1-B1)
=SUM(A1+B1)

These should be written:
=A1*B1
=A1/B1
=A1-B1
=A1+B1

respectively. Once the internal calculation has been made, for example
A1*B1 Excel has a single number, the result of that calculation. So what is
the sum of a single number? The number itself.

Do these longer versions return the correct results? Yes! But they require
more typing, they occupy more memory, and slow down calculations.
 
S

Sandy Mann

Square Peg said:
For the example given, MOD is better, but only because the numbers are
positive.

INT or TRUNC provide better control with negative numbers, but require
the value twice
Click to expand...

Very true.

--
Regards,

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
Top