Formula Funtions
 

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.

Formula Funtions
 

=INT(A1/C1)+ROUND(ROUND(MOD(A1/C1,1),3)*C1,0)/1000
assuming that your 560 is in C1

I'm not brave enough to ask why you would want to do such a strange sequence
of operations!
--
David Biddulph

"Garyp1961" wrote in message
...
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.

Formula Funtions
 

Try this

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

a1= 5000

Mike

"Garyp1961" wrote:

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.

Formula Funtions
 

Thanks Mike it worked perfect.

"Mike H" wrote:

Try this

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

a1= 5000

Mike

"Garyp1961" wrote:

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.

Formula Funtions
 

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

"David Biddulph" wrote:

=INT(A1/C1)+ROUND(ROUND(MOD(A1/C1,1),3)*C1,0)/1000
assuming that your 560 is in C1

I'm not brave enough to ask why you would want to do such a strange sequence
of operations!
--
David Biddulph

"Garyp1961" wrote in message
...
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.

Formula Funtions
 

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



"Garyp1961" wrote in message
...
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.

Formula Funtions
 

Yes, given the big assumption that A1 is an integer. If it isn't, the
rounding makes a difference, which is why I left that part of the OP's
instructions in my formula.
--
David Biddulph

"Rick Rothstein (MVP - VB)" wrote in
message ...
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



"Garyp1961" wrote in message
...
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.

Formula Funtions
 

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


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Yes, given the big assumption that A1 is an integer. If it isn't, the
rounding makes a difference, which is why I left that part of the OP's
instructions in my formula.
--
David Biddulph

"Rick Rothstein (MVP - VB)" wrote in
message ...
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



"Garyp1961" wrote in message
...
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.

Formula Funtions
 

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

Formula Funtions
 

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

"Rick Rothstein (MVP - VB)" wrote:

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