Excel formula question? Is this possible.

paulgalway

Don't know if this is possible in MS Excel 2007:-

Example:

A1: 7.38
B1: 5

C1: (A1-7)* B1


Is there a a formula to put in C1 that will look at a decimal number,
subtract the whole number and then multiply the remainder (0.38)
by the contents of another cell.

Thanks in advance.

JIdontknow

You could use thd round down comnand of indeed the floor command to get the whole number. There are special functions but floor / round down would be the simplest I reckon. Just give it to what significance ie =floor(A1,1)

A1 equals 27.23
Using above formula new cell will equal 27.

C1=(floor(A1,1))*B1

To multiply the .38 by a value you can say. =(A1-(floor(A1,1))) *xxxx (in this case B1)

You could then subtract this from original and do what you need to with this value. I believe there's special functions you can set up that take parts of a value but it depends how complex you want to get. With floor it doesn't matter how many digits are after the decimal place it rounds it down to the whole number.
Not at excel so can't test this now...

johnb25

paulgalway wrote: »

Don't know if this is possible in MS Excel 2007:-

Example:

A1: 7.38
B1: 5

C1: (A1-7)* B1


Is there a a formula to put in C1 that will look at a decimal number,
subtract the whole number and then multiply the remainder (0.38)
by the contents of another cell.

Thanks in advance.

Paul,

This should do it:
=(A1-ROUNDDOWN(A1,0))*B1


John

Thoie

I'd suggest that MOD is the correct formula to use here, rather than getting the whole number and subtracting (which eventually does the same thing, but complicates the formula).

MOD(number, divisor) gives you the "remainder" after a number is divided by the divisor. So to get just the decimal, you use "1" as the divisor.

MOD(34.2342,1) = 0.2342

Using your example:

A1: 7.38
B1: 5

C1: (A1-7)* B1

So in C1 you'd put:

=MOD(A1,1)*B1 (= 1.9 in this example)