Formula Funtions

[Garyp1961]

Hi, I am working in excell 2000 and I would like to write a formula that
would divide a numer in one cell and times the decimal portion of the answer
by the same amount I was divding it by. Example cell A1 would countain the
amount I entered 5000 I would like cell B1 to divide the amount in A1 by 560
and then times the reaining amount that would be in decilmal by 560. Don't
know if this is possible or not?
if you divide 5000 by 560 you get 8.928 if you set your decimal point to 3
I would like to take the decimal part and times that by 560 which is 519.680
I would like to round up the 519.680 to the next whole if the first number
on the right of the decimal is 5 or higher.
then my answer in cell B1 would be 8.520 Thanks if anyone can help with
this. Hope my question makes sense.

 

[Mike H]

Try this

=INT(A1/560)+(((A1/560)-INT(A1/560))*560)/1000

a1= 5000

Mike

 

[Garyp1961]

Thanks Mike it worked perfect.

Try this

=INT(A1/560)+(((A1/560)-INT(A1/560))*560)/1000

a1= 5000

Mike

 

[Garyp1961]

Thanks for your help.. I work in a bakery and it is a formula to figure out
rack counts.

 

[Rick Rothstein \(MVP - VB\)]

What is the reason you are rounding to 3 decimal places after the first
division? I ask because the ultimate calculation you are doing (taking the
decimal part produced by dividing some whole number value by a given whole
number and multiplying it by that given whole number) will **always**
produce a whole number result if no intermediate rounding is applied. In
other words, this formula will **always** return a whole number result...

=560*MOD(A1/560,1)

The reason this works is because in the Excel's formula world,
MOD(AnyNumber,1) returns the decimal portion of the AnyNumber. Now, in its
longer form, the above formula is equivalent to this one....

=560*(A1/560-INT(A1/560))

(the part in parentheses is the original number divided by 560 minus the
whole number part of the original number divided by 560, which leaves over
the decimal part of the original number divided by 560). So, if you multiply
the above formula through, you get this equivalent...

=A1-560*INT(A1/560)

Now, A1 is assumed to be a given whole number. Let's look inside the value
of A1 inside the INT function. Remember back to your elementary school days
when you were taught division. You didn't produce a floating point value
back then; rather, you produced a whole number value (the number of times
the divisor evenly divided the original number) plus a whole number
remainder. Lets break A1/560 up that way. There is some whole number W of
times 560 evenly divides A1 (that whole number W could be zero by the way)
and there is a whole number remainder R that is left over. In other words,
the above formula can be shown as...

=A1-560*INT((W+R)/560)

Or, dividing through each term...

=A1-560*INT((W/560)+(R/560))

Now remember, W is evenly divisible by 560 and R is not; hence, the value of
the INT function will be W/560 (the whole number part) and, when finally
multiplied by 560, will evaluate to W... a whole number. That means the
above formula is equivalent to this...

=A1-W

which, since A1 and W are both whole numbers, will result in a whole number
answer...**always**.

So if you go back to the original equation I posted...

=560*MOD(A1/560,1)

it will **always** produce a whole number result... no intermediary rounding
necessary.

Rick

 

[Rick Rothstein \(MVP - VB\)]

I had the benefit of seeing the OP's response to you in which me mentioned
he worked in a bakery and was trying to figure out rack counts (which I
presumed came in whole number units).

Rick

 

[Rick Rothstein \(MVP - VB\)]

I had the benefit of seeing the OP's response to you in which me mentioned

he worked in a bakery and was trying to figure out rack counts (which I
presumed came in whole number units).

I guess I should have said.. what they are making to put on those racks
comes in whole number of units and what each rack can hold also is in whole
number of units as well.

Rick

 

[Garyp1961]

Hi Rick, Thanks for your help. I guess I should have given a little more
info for the question I was asking. The way we figure are count is always in
whole nubers and it is done by units per tray, per rack. The spredsheet I
have set up is for oders to be produced and is very lengthy to explain why I
needed that formula the way it was. Thanks Gary