# Paint Job, Coverage Example

Location: http://www.mvps.org/dmcritchie/excel/paint.htm
[View without Frames]
There are three spread sheets in this example:
1. P.Job, for an estimate of cost of materials (paint and beads)
2. P.Paint, for constants associated with particular type of paint.
3. P.Beads, for cost per poind of beads
There are named ranges associated with the P.Paint and P.Beads sheet; and the range names are unique to the workbook and do not match sheetnames, nor cellnames.  Named ranges make use of VLOOKUP a lot easier to write and to maintain.

### Sheet P.Job

 A B C D E F G 1 Paint CoverFor Cans Bead BdWgt Cost CalcCover 2 Sw20 1000 10.00 1 100 99.50 1000 3 DB25 1005 11.00 3 110 115.50 1100 4 HD14 1000 10.00 1 100 110.00 1000 5 6 A2 Sw20 7 B2 1000 8 C2 =ROUNDUP(B2/VLOOKUP(A2,PAINT,4,FALSE),0) 9 D2 1 10 E2 =C2*VLOOKUP(A2,PAINT,6,FALSE) 11 F2 =C2*VLOOKUP(A2,PAINT,8,FALSE) 12 G2 =C2*VLOOKUP(A2,PAINT,4,FALSE)

### Sheet P.Paint

 A B C D E F G H 1 Code Brand Paint Cover BeadType BeadLB Bead/LB Total/Can 2 DB25 Dutch Boy 8.00 100 3 10 0.25 10.50 3 HD14 Home Depot 9.00 100 1 10 0.20 11.00 4 Sw20 Sherwin Williams 7.95 100 1 10 0.20 9.95 5 6 A2 DB25 7 B2 Dutch Boy 8 C2 8 9 D2 100 10 E2 3 11 F2 10 12 G2 =VLOOKUP(\$E2,Beads,2,FALSE) 13 H2 =(C2+F2*G2) 14 15 Name Range 16 Paint P.Paint!\$A\$2:\$M\$40 17 Beads P.Beads!\$A\$1:\$D\$7