Sum help

[timsims]

Excel Workbook
	B	C	D	E	F	G	H	I
9	Site Description/Application			Tree Pits (V.O/T)		Material Type	General
10	Complexity of Work			1 - wide areas over 3m - SINGLE COLOUR
11	Number of colours			1 colour	40m/hour	Click here to see table
12	Area			100	m	Please enter number of tree pits.
13	Nominal Stone Size			10	mm
14	Depth			40	Click here to see table
15	No of DIY Kits			533	Kits
16	Coverage (m2 per kit)			0.2	0
17	No of Tree Pits
18	Distance to Site			#N/A	miles to site
19	Total Laying Hours			2.50	hours
20	Travelling Hours			#N/A	hours
21	Total No. of Days			#N/A	#N/A
22	Aggregate Tonnage			8.000	tonne	Approx no of resin Tubs	94
23	Resin Tonnage			0.560	tonne	No of Pallets	9
24	Delivery by			Rigid - HIAB (0-10 tonnes)		Click here to see table
25				Total Costs		Cost per m
26	Haulage to Site		10	#N/A		1.64	/m
27	Haulage to Warehouse		11	230.00		2.30	/m
28	Sub Contractor Labour			700.00	Sub Contractor price	7.00	/m
29	Installation Labour		12	FALSE	4 Men Required	-	/m	Click here to see table
30	Quality Assurance Site Visit			-	1 visit	112.00
31	Accomodation & Meals		12	#N/A		#N/A	/m
32	Wearing Course Costs		6/7	4,000.00		40.00	/m
Item 1

Excel 2007

Cell Formulas
Range	Formula
F11	=((IF(E10="1 - wide areas over 3m - SINGLE COLOUR",'Fixed Settings'!C16,IF(E10="2 - Continuous pathways 1.2 to 3.0m wide - SINGLE COLOUR",'Fixed Settings'!C18,IF(E10="3 - Continuous paths 0.5 to 1.2m wide & separate areas of 5-20 m² avg panel size - SINGLE COLOUR",'Fixed Settings'!C20,(IF(E10="4 - Indiviual Tree Pits - SINGLE COLOUR",'Fixed Settings'!C22,IF(E10="5 - Internal Flooring & covered areas - SINGLE COLOUR",'Fixed Settings'!C24,VLOOKUP(E11,'Fixed Settings'!B26:C30,2)))))))))
F16	=IF(E7="DIY Kits",CONCATENATE("This will cover ",E15*E16,"m2 at ",E14,"mm depth"),)
F21	=ROUNDUP(E12/E21,0)
F29	=VLOOKUP(E10,'Item 1'!A233:N238,'Item 1'!G230)
F30	=IF(E12<501,B348,IF(E12<1001,C348,IF(E12<1501,D348,IF(E12<2001,F348,IF(E12<2501,G348,IF(E12<3001,H348,IF(E12<3501,I348,IF(E12<4001,J348,IF(E12<4501,K348,IF(E12<5001,L348))))))))))
G12	=IF(E7="DIY Kits","When pricing for DIY Kits: If pricing by Area, enter area and depth as normal, and the No of its required will be shown. If pricing by the kit, enter Depth and No of Kits. The coverage will be shown",IF(E9="Tree Pits (V.O/T)","Please enter number of tree pits.",))
G26	=VLOOKUP(E14,A350:F361,3,FALSE)
G27	=VLOOKUP(E14,A350:F361,4,FALSE)
G28	=IF(E12<100,0,7)
G29	=E29/$E$12
G31	=E31/$E$12
G32	=IF(H9="Spectrum",IF(E14=12,IF(E12>46,'Item 1'!C296,'Item 1'!C295),IF(E14=16,IF(E12>34,'Item 1'!F296,'Item 1'!F295),IF(E12>30,'Item 1'!H296,'Item 1'!H295))),VLOOKUP(E14,'Item 1'!B250:H292,I257))
E15	=(1/15*E14)*E12*2
E16	=ROUND((16/E14)*0.5,1)
E18	=Distances!J9
E19	=E12/F11
E20	=('Fixed Settings'!C33*2)
E21	=ROUNDUP(IF(E19<5.9,1,E19/6)+(IF(E18>250,1,0)),0)
E22	=E12/500*E14
E23	=0.07*E22
E26	=IF(E24=0,,IF(K203=I200,"Check Price",IF(OR(E24="Network - Tail-lift Next Day",E24="Network - Tail-lift 2-3 Days",E24="Network - Tail-lift 9am",E24="Network - Tail-lift 10am",E24="Network - Tail-lift am",E24="Network - Tail-lift Timed"),VLOOKUP(K203,B203:I210,K204+1)*H23,VLOOKUP(E213,B213:C228,2)*(IF(E24=B201,G214,G213)))))
E27	=E12*G27
E28	=SUM(E12*G28)
E29	=IF(AND(E12>1,E12<100)=TRUE,(((F29-1)*190+240)*E21)*1.15)
E30	=INT(E12/500)*112
E31	=IF(AND(E12>1,E12<100,E21<>1),'Fixed Settings'!C36*F29*(E21-1))
E32	=G32*E12
H22	=ROUNDUP((E23*1000)/6,0)
H23	=(ROUNDUP(E22,0)+(ROUNDUP(E23,0)))

I need cell G28 to do exactly what it does now but, if I change the drop down box in cell E9 to the treepit option G28 multiplies by 2. Any of the other options it still uses the sum currently in G28.

Thanks

 

[Gerald Higgins]

First, as long as your TRUE argument for G28 should return 0, you can simplify slightly....

=(E12>=100)*7

Then....

=((E12>=100)*7)*(1+(E9="TREEPITS"))

Replace "TREEPITS" with whatever text you are actually putting in E9.