Paint Job, Coverage Example

Location: http://www.mvps.org/dmcritchie/excel/paint.htm      
Home page: http://www.mvps.org/dmcritchie/excel/excel.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

 ABCDEFG
1PaintCoverFor CansBeadBdWgt CostCalcCover
2Sw201000 10.001100 99.501000
3DB251005 11.003110 115.501100
4HD141000 10.001100 110.001000
5        
6A2Sw20  
7B21000  
8C2 =ROUNDUP(B2/VLOOKUP(A2,PAINT,4,FALSE),0) 
9D21 
10E2 =C2*VLOOKUP(A2,PAINT,6,FALSE) 
11F2 =C2*VLOOKUP(A2,PAINT,8,FALSE) 
12G2 =C2*VLOOKUP(A2,PAINT,4,FALSE) 

Sheet P.Paint

 ABCDEFGH
1CodeBrand PaintCoverBeadType BeadLBBead/LB Total/Can
2DB25Dutch Boy 8.001003 100.25 10.50
3HD14Home Depot 9.001001 100.20 11.00
4Sw20Sherwin Williams 7.951001 100.20 9.95
5         
6A2DB25     
7B2Dutch Boy     
8C28     
9D2100     
10E23     
11F210     
12G2 =VLOOKUP($E2,Beads,2,FALSE)     
13H2=(C2+F2*G2)     
14       
15NameRange     
16Paint P.Paint!$A$2:$M$40     
17Beads P.Beads!$A$1:$D$7     

Sheet P.Beads

 AB
1Bead Type price/Lb
21 0.20
32 0.30
43 0.25

Related

VLOOKUP page
You are one of many distinguished visitors who have visited my site here or in a previous location  since this page was created on September 11, 2001.

Visit [my Excel home page]   [Index page]   [Excel Onsite Search]   [top of this page]

Please send your comments concerning this web page to: David McRitchie send email comments


Copyright © 1997 - 2006,  F. David McRitchie,  All Rights Reserved