Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula with increments of 6
i am creating an work order with pricing. the overall size breaks down into
many parts of different sizes. i use square footage and linear measurments. right now i've got all the pricing formulas figured out for each part (for the exact sizes used), but i need the overall size to be rounded up to increments of 6. for example the overall size is 58" x 32" part #5: size is 26 1/2" x 30 1/4" (calculated in sq ft) cost of part #5 is 4.5/sqft so the formula i'm using is =(26.5*30.25/144)*4.5 but i need the the overall size to be rounded up to the next 6" increment in order for each part to be PRICED correct - but CAN'T change the sizes on my work order so part#5 needs to be priced as if the overall size is 60 x 36 (the part size would then be 27 1/2" x 34 1/4" help please :( |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula with increments of 6
A1: 58
A2: 32 =CEILING(A1,6)*CEILING(A2,6) You can get the rest of your formula in there :) -- ** John C ** "ForeverH" wrote: i am creating an work order with pricing. the overall size breaks down into many parts of different sizes. i use square footage and linear measurments. right now i've got all the pricing formulas figured out for each part (for the exact sizes used), but i need the overall size to be rounded up to increments of 6. for example the overall size is 58" x 32" part #5: size is 26 1/2" x 30 1/4" (calculated in sq ft) cost of part #5 is 4.5/sqft so the formula i'm using is =(26.5*30.25/144)*4.5 but i need the the overall size to be rounded up to the next 6" increment in order for each part to be PRICED correct - but CAN'T change the sizes on my work order so part#5 needs to be priced as if the overall size is 60 x 36 (the part size would then be 27 1/2" x 34 1/4" help please :( |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula with increments of 6
Hi John
where do i put the ceiling formulas "John C" wrote: A1: 58 A2: 32 =CEILING(A1,6)*CEILING(A2,6) You can get the rest of your formula in there :) -- ** John C ** "ForeverH" wrote: i am creating an work order with pricing. the overall size breaks down into many parts of different sizes. i use square footage and linear measurments. right now i've got all the pricing formulas figured out for each part (for the exact sizes used), but i need the overall size to be rounded up to increments of 6. for example the overall size is 58" x 32" part #5: size is 26 1/2" x 30 1/4" (calculated in sq ft) cost of part #5 is 4.5/sqft so the formula i'm using is =(26.5*30.25/144)*4.5 but i need the the overall size to be rounded up to the next 6" increment in order for each part to be PRICED correct - but CAN'T change the sizes on my work order so part#5 needs to be priced as if the overall size is 60 x 36 (the part size would then be 27 1/2" x 34 1/4" help please :( |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula with increments of 6
A1: =58
A2: =32 A3: =CEILING(A1,6) ... this would be 60 A4: =CEILING(A2,6) ... this would be 36 I have to admit, looking at your data and saying the overall size being 58" x 32" since it must be in increments of 6" would change to 60" x 36", and then taking that 60x36 (which is what the ceiling formulas will do), how you come up with 27 1/2" x 34 1/4" I do not know. But the ceilings will convert your 58x32 to 60x36. -- ** John C ** "ForeverH" wrote: Hi John where do i put the ceiling formulas "John C" wrote: A1: 58 A2: 32 =CEILING(A1,6)*CEILING(A2,6) You can get the rest of your formula in there :) -- ** John C ** "ForeverH" wrote: i am creating an work order with pricing. the overall size breaks down into many parts of different sizes. i use square footage and linear measurments. right now i've got all the pricing formulas figured out for each part (for the exact sizes used), but i need the overall size to be rounded up to increments of 6. for example the overall size is 58" x 32" part #5: size is 26 1/2" x 30 1/4" (calculated in sq ft) cost of part #5 is 4.5/sqft so the formula i'm using is =(26.5*30.25/144)*4.5 but i need the the overall size to be rounded up to the next 6" increment in order for each part to be PRICED correct - but CAN'T change the sizes on my work order so part#5 needs to be priced as if the overall size is 60 x 36 (the part size would then be 27 1/2" x 34 1/4" help please :( |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula with increments of 6
that won't work, as it will change the part sizes of the work order - and of
course that would mean we'd make the part the wrong size. maybe i can insert and hide a row so B1 has the formula you've put in for A3 and then base the pricing on B1 and B2?? do you think that would work? "John C" wrote: A1: =58 A2: =32 A3: =CEILING(A1,6) ... this would be 60 A4: =CEILING(A2,6) ... this would be 36 I have to admit, looking at your data and saying the overall size being 58" x 32" since it must be in increments of 6" would change to 60" x 36", and then taking that 60x36 (which is what the ceiling formulas will do), how you come up with 27 1/2" x 34 1/4" I do not know. But the ceilings will convert your 58x32 to 60x36. -- ** John C ** "ForeverH" wrote: Hi John where do i put the ceiling formulas "John C" wrote: A1: 58 A2: 32 =CEILING(A1,6)*CEILING(A2,6) You can get the rest of your formula in there :) -- ** John C ** "ForeverH" wrote: i am creating an work order with pricing. the overall size breaks down into many parts of different sizes. i use square footage and linear measurments. right now i've got all the pricing formulas figured out for each part (for the exact sizes used), but i need the overall size to be rounded up to increments of 6. for example the overall size is 58" x 32" part #5: size is 26 1/2" x 30 1/4" (calculated in sq ft) cost of part #5 is 4.5/sqft so the formula i'm using is =(26.5*30.25/144)*4.5 but i need the the overall size to be rounded up to the next 6" increment in order for each part to be PRICED correct - but CAN'T change the sizes on my work order so part#5 needs to be priced as if the overall size is 60 x 36 (the part size would then be 27 1/2" x 34 1/4" help please :( |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula with increments of 6
You would still keep A1 & A2 for your sizes, the ceiling formula would be for
calculating your price. The problem is, I don't see how 60x36 = 27.5 x 34.25. -- ** John C ** "ForeverH" wrote: that won't work, as it will change the part sizes of the work order - and of course that would mean we'd make the part the wrong size. maybe i can insert and hide a row so B1 has the formula you've put in for A3 and then base the pricing on B1 and B2?? do you think that would work? "John C" wrote: A1: =58 A2: =32 A3: =CEILING(A1,6) ... this would be 60 A4: =CEILING(A2,6) ... this would be 36 I have to admit, looking at your data and saying the overall size being 58" x 32" since it must be in increments of 6" would change to 60" x 36", and then taking that 60x36 (which is what the ceiling formulas will do), how you come up with 27 1/2" x 34 1/4" I do not know. But the ceilings will convert your 58x32 to 60x36. -- ** John C ** "ForeverH" wrote: Hi John where do i put the ceiling formulas "John C" wrote: A1: 58 A2: 32 =CEILING(A1,6)*CEILING(A2,6) You can get the rest of your formula in there :) -- ** John C ** "ForeverH" wrote: i am creating an work order with pricing. the overall size breaks down into many parts of different sizes. i use square footage and linear measurments. right now i've got all the pricing formulas figured out for each part (for the exact sizes used), but i need the overall size to be rounded up to increments of 6. for example the overall size is 58" x 32" part #5: size is 26 1/2" x 30 1/4" (calculated in sq ft) cost of part #5 is 4.5/sqft so the formula i'm using is =(26.5*30.25/144)*4.5 but i need the the overall size to be rounded up to the next 6" increment in order for each part to be PRICED correct - but CAN'T change the sizes on my work order so part#5 needs to be priced as if the overall size is 60 x 36 (the part size would then be 27 1/2" x 34 1/4" help please :( |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula with increments of 6
i just found out that i have to create a duplicate line of each part based on
the ceiling size in order to get the prices i want. i can hide each of these duplicate lines - its a bit of work - but i get paid by the hour :) 60 x 36 = 27.5 x 34.25 because A1: 60 B1: =(A1-5)/2 the 27.5 is just one part of the entire product thank you for your help!!!! "John C" wrote: You would still keep A1 & A2 for your sizes, the ceiling formula would be for calculating your price. The problem is, I don't see how 60x36 = 27.5 x 34.25. -- ** John C ** "ForeverH" wrote: that won't work, as it will change the part sizes of the work order - and of course that would mean we'd make the part the wrong size. maybe i can insert and hide a row so B1 has the formula you've put in for A3 and then base the pricing on B1 and B2?? do you think that would work? "John C" wrote: A1: =58 A2: =32 A3: =CEILING(A1,6) ... this would be 60 A4: =CEILING(A2,6) ... this would be 36 I have to admit, looking at your data and saying the overall size being 58" x 32" since it must be in increments of 6" would change to 60" x 36", and then taking that 60x36 (which is what the ceiling formulas will do), how you come up with 27 1/2" x 34 1/4" I do not know. But the ceilings will convert your 58x32 to 60x36. -- ** John C ** "ForeverH" wrote: Hi John where do i put the ceiling formulas "John C" wrote: A1: 58 A2: 32 =CEILING(A1,6)*CEILING(A2,6) You can get the rest of your formula in there :) -- ** John C ** "ForeverH" wrote: i am creating an work order with pricing. the overall size breaks down into many parts of different sizes. i use square footage and linear measurments. right now i've got all the pricing formulas figured out for each part (for the exact sizes used), but i need the overall size to be rounded up to increments of 6. for example the overall size is 58" x 32" part #5: size is 26 1/2" x 30 1/4" (calculated in sq ft) cost of part #5 is 4.5/sqft so the formula i'm using is =(26.5*30.25/144)*4.5 but i need the the overall size to be rounded up to the next 6" increment in order for each part to be PRICED correct - but CAN'T change the sizes on my work order so part#5 needs to be priced as if the overall size is 60 x 36 (the part size would then be 27 1/2" x 34 1/4" help please :( |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula with increments of 6
Thanks for the feedback....
Just curious.... A1: 58 A2: 32 Formula for Actual size: A3: =((A1-5)/2*(A2-1.75)/12^2)*4.5 Formula for Price determination: A4: =((CEILING(A1,6)-5)/2*(CEILING(A2,6)-1.75)/12^2)*4.5 Note: I am assuming that on the second dimension, you are always 'buffering' by 1 3/4", hence the -1.75. -- ** John C ** "ForeverH" wrote: i just found out that i have to create a duplicate line of each part based on the ceiling size in order to get the prices i want. i can hide each of these duplicate lines - its a bit of work - but i get paid by the hour :) 60 x 36 = 27.5 x 34.25 because A1: 60 B1: =(A1-5)/2 the 27.5 is just one part of the entire product thank you for your help!!!! "John C" wrote: You would still keep A1 & A2 for your sizes, the ceiling formula would be for calculating your price. The problem is, I don't see how 60x36 = 27.5 x 34.25. -- ** John C ** "ForeverH" wrote: that won't work, as it will change the part sizes of the work order - and of course that would mean we'd make the part the wrong size. maybe i can insert and hide a row so B1 has the formula you've put in for A3 and then base the pricing on B1 and B2?? do you think that would work? "John C" wrote: A1: =58 A2: =32 A3: =CEILING(A1,6) ... this would be 60 A4: =CEILING(A2,6) ... this would be 36 I have to admit, looking at your data and saying the overall size being 58" x 32" since it must be in increments of 6" would change to 60" x 36", and then taking that 60x36 (which is what the ceiling formulas will do), how you come up with 27 1/2" x 34 1/4" I do not know. But the ceilings will convert your 58x32 to 60x36. -- ** John C ** "ForeverH" wrote: Hi John where do i put the ceiling formulas "John C" wrote: A1: 58 A2: 32 =CEILING(A1,6)*CEILING(A2,6) You can get the rest of your formula in there :) -- ** John C ** "ForeverH" wrote: i am creating an work order with pricing. the overall size breaks down into many parts of different sizes. i use square footage and linear measurments. right now i've got all the pricing formulas figured out for each part (for the exact sizes used), but i need the overall size to be rounded up to increments of 6. for example the overall size is 58" x 32" part #5: size is 26 1/2" x 30 1/4" (calculated in sq ft) cost of part #5 is 4.5/sqft so the formula i'm using is =(26.5*30.25/144)*4.5 but i need the the overall size to be rounded up to the next 6" increment in order for each part to be PRICED correct - but CAN'T change the sizes on my work order so part#5 needs to be priced as if the overall size is 60 x 36 (the part size would then be 27 1/2" x 34 1/4" help please :( |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel: Round formula up in $5. increments =+(M4*1.1)+M4 | Excel Worksheet Functions | |||
extend my formula with increments of 4 cells | Excel Worksheet Functions | |||
Need formula to add increments of 0.16 seconds to time | Excel Discussion (Misc queries) | |||
How do I sum a formula and paste it in 7 cell increments? | Excel Discussion (Misc queries) | |||
formula for defining axis maximum and increments | Charts and Charting in Excel |