Need assistance with some formulas.

[craigwojo]

Hi everyone,

New to Excel and trying to put together a spread sheet for my business
If anyone can assist in figuring out some of the cell formulas it woul
be greatly appreciated. This is what I have so far.

Column A: Quantity (this is an input cell)
Column B: Height (input cell)
Column C: Length (input cell)
Column D: Total tags per 16x24 sht (formula
=IF(OR(B2="",C2=""),"",FLOOR(16/B2,1)*FLOOR(24/C2,1))
Column E: Square Inches per Tag (formula) =SUM(B2*C2)
Column F: Customer Price per Tag (formula) =SUM(H2*E2)
Column G: Total Tag Cost per Sheet (formula) =SUM(D2*F2)
Column H: Customer Cost per Square Inch – (formula) I don’t know how t
do this, what I need is; If the square inches of the tag (Column E) i
0-4.50 then the price would be .25 square inch / 4.51-100.00 - .2
square inch / 100.01-200.00 - .20 square inch / 200.01-432.00 - .1
square inch.
Column I: # of 16x24 shts for Job (formula) =SUM(A2/D2) but I wha
this to be rounded up to the next number, no numbers after decima
point. I don’t know how to do this also.
Column J: # of Full 24x48 shts to Order. (formula) this is a formul
I need to order full sheets from the manufacturer. When I order a shee
of material I need to buy the full sheet, which is 24x48. My machin
only holds 16x24 sheets, I have them cut it in 3rds. So what I nee
for this cell is if Column I is saying that if I need 2.45 shts o
material for the job, then I need to this cell to say “1” (only 1 ful
sheet is needed to be ordered. If Column I says 3.5 (16x24) shts neede
for the job then this cell needs to say that I need to order “2” ful
sheets (24x48), and so on.

If anyone can figure some of this out for me it will be appreciated.

Thank you,
Crai

 

[Biff]

Hi!

For columns E,F,G, and I, you don't need the SUM function.

Simply, cell * cell. eg: =B2*C2

For column H:

=IF(E2="","",LOOKUP(E2,
{0,0.25;4.51,0.23;100.01,0.2;201.01,0.15}))

Note: any value greater than 201.01 will return .15.

Now, column I, you want rounded up to the next whole
number but in your explanation for the formula in column J
you use decimal values derived from column I which you
will not get if you round up in column I.

Biff