Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
funtions and conditions | Excel Discussion (Misc queries) | |||
FUNTIONS | Excel Worksheet Functions | |||
nested funtions (IF OR) | Excel Worksheet Functions | |||
help with spaces in funtions | Excel Worksheet Functions | |||
Nested Funtions | Excel Worksheet Functions |