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:

P.Job, for an estimate of cost of materials (paint and beads)
P.Paint, for constants associated with particular type of paint.
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

Sheet P.Beads

	A	B
1	Bead Type	price/Lb
2	1	0.20
3	2	0.30
4	3	0.25

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

Paint Job, Coverage Example

Sheet P.Job

Sheet P.Paint

Sheet P.Beads

Related