formula with increments of 6

  • Thread starter Thread starter ForeverH
  • Start date Start date
F

ForeverH

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 :(
 
A1: 58
A2: 32
=CEILING(A1,6)*CEILING(A2,6)
You can get the rest of your formula in there :)
 
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.
 
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?
 
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.
 
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!!!!
 
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.
 
Back
Top