formula with increments of 6

[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

 

[John C]

A1: 58
A2: 32
=CEILING(A1,6)*CEILING(A2,6)
You can get the rest of your formula in there

 

[ForeverH]

Hi John

where do i put the ceiling formulas

A1: 58
A2: 32
=CEILING(A1,6)*CEILING(A2,6)
You can get the rest of your formula in there

 

[John C]

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.

 

[ForeverH]

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]

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.

 

[ForeverH]

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]

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.