Multiple Cells through same set of calculations

[bryandaniel5]

I have a spreadsheet with a list of values that I need to run each through the same series of calculations on another sheet and then output the answer to each into another column in a table
Here is the sample table with the values i need to input into a series of calculations:

Book1
	B	C	D
1		Input Values	Output Values
2	a	12352
3	b	983720
4	c	3928
5	d	93849
6	e	35702
7	f	46392
8	g	12830
Sheet1

Here is a sample of calculations i might want to put each value through and fill in the Output Values column of the above table.

Book1
	B	C
2	Input Value	12352
3	Calculation 1	111.139552
4	Calculation 2	1372795.75
5	Calculation 3	62399.8066
6	Calculation 4	249748.302
7	Calculation 5	335599.281
8	Output value	27.1696309
Sheet2

Cell Formulas
Range	Formula
C2	C2	=Sheet1!C2:C8
C3	C3	=SQRT(C2)
C4	C4	=C3^3
C5	C5	=C4/22
C6	C6	=C5*1.274*PI()
C7	C7	=C6/32*43
C8	C8	=C7/C2
Press CTRL+SHIFT+ENTER to enter array formulas.

i've thought maybe the array function might be the answer, but after spending about 45 minutes searching the web, i can't find an answer. Anybody have any suggestions?

 

[dreid1011]

Here are all your calculations in a formula which you can now use in your first sheet col D and copy down:

=(((((((SQRT(C2))^3)/22)*1.274)*PI())/32)*43)/C2

Book1
	B	C	D
1		Input Values	Output Values
2	a	12352	27.1696309
3	b	983720	242.4659999
4	c	3928	15.3214842
5	d	93849	74.8910504
6	e	35702	46.1914260
7	f	46392	52.6546397
8	g	12830	27.6903487
Sheet5

Cell Formulas
Range	Formula
D2:D8	D2	=(((((((SQRT(C2))^3)/22)*1.274)*PI())/32)*43)/C2

 

[bryandaniel5]

this was just a sample of calculations, what i actually need is much greater and can't easily be put in singular formula. here's the sheet that i need to run each value through

NHCRWA (10-10-23).xlsx
	A	B	C	D	E	F	G	H	I	J
1
2			Yellow fill = Automatic equation or copy			red font text = required data entry
3			White Fill = Information or data preparer must enter
4
5			PROJECT INFORMATION
6			Project	60" WL Along Mills Rd from Copeland Dr to FM 1960		Prepared by:	0
7			Bid Date	10/10/23
8			Time	10:00 AM
9
10			Owner	NHCRWA
11
12			Engineer
13			0					Bid Unit Price:	$ -
14								Total Estimate:	$ -
15			Project location:
16								Suggested Unit Price:	$ 1,246.72
17								Total Suggested Item Price	$ 386,484.09
18
19			Units LF	310
20			Soil Conditions:
21
22			Construction Procedure
23
24
25			Production/Shift:	12
26			Shifts/Week:	5	Hours/Shift:	10		Shifts to Complete:	36
27			Scope DCS Inc. to:	Furnish work shaft(s); F&I Casing; stockpile spoil adjacent to work shaft; grout outside casing;
28			General Contractor to:	furnish barricades and traffic control; furnish and install carrier pipe; furnish engineering, permits, and survey; locate existing utilities and adjust as needed; haul off spoil; back fill; and clean up.
29
30			OTHER COMMENTS OR INFORMATION
31
32
33
34
35
36		1	LABOR
37			1.A Crew Labor
38			Classification	No./Shift	M. Hour/Shift	Hr. Rate	DavisBacon Fringe Benefit	Straight Time	Davis Bacon Fringe Total
39			Head-In-Man	2	10	28		$560.00	$0.00
40			Kickers	2	10	24		$480.00	$0.00
41			Shaft Men	1	10	22		$220.00	$0.00
42			Top Men	1	10	22		$220.00	$0.00
43			Crane Operator	1	10	35		$350.00	$0.00
44			Boring Machine Operator	1	10	25		$250.00	$0.00
45					10			$0.00	$0.00
46					10			$0.00	$0.00
47					10			$0.00	$0.00
48					10			$0.00	$0.00
49			Salaried Foreman	1				$550.00
50				TOTAL MHr/SHIFT:	90	Totals		$2,080.00	$0.00
51			SHIFT ANALYSIS
52			Work Type	Work Type #	# SHIFTS
53			Move In & Out	1000	2
54			Move on Site	1025		Overtime Conversion Factors
55			Setup	1050	1	Hrs/Week	**Choice**	Factor
56			Tunnel/Bore	1100	26	40		1
57			Shaft Excavation	1150	1	50	1	1.1
58			Shaft Backfill	1200	1	55		1.13636
59			Grouting	1250	1	60		1.16667
60			Rail Installation	1300		66		1.19696
61			Pipe Installation	1350		72		1.22222
62			Welding	1375
63			Pilot Tunnel	1550
64			Lost time	1600	4
65			Time & Materials	1625
66			Pot Holing	1650
67			Turn Eye	1750
68			Drill Shafts for Beams	1800
69			Dewatering	1850
70			Total Shifts		36
71						Cost/shift straight time		$ 2,080.00
72						Cost/shift * OT Conversion Factor		$ 2,288.00
73						Cost/shift Fringe Benefits		$ -
74						Total Cost/Shift		$ 2,838.00
75
76						Est. No. of Shifts		36
77						Total Raw Labor Cost		$ 102,168.00
78						Inflation Hedge %	0%	$ -
79						TOTAL EST. LABOR COST		$ 102,168.00
80				Cost Per Unit				$ 329.57
81
82			1.B Supervision	% TIME	Number	Rate	Daily Total	Job Total
83				100%	1	$ 700.00	$ 700.00	$ 25,200.00
84
85							TOTAL LABOR COST	$ 127,368.00
86							Unit Cost	$ 410.86
87
88		2	Materials
89				Quantity	Unit Cost	Total Cost
90			Steel Casing	310		$0.00
91			Tunnel Liner Plate	310		$0.00
92			Steel Ribs	310		$0.00
93			Reinforced Concrete Pipe/Box	310		$0.00
94			Lumber	310		$0.00
95			Grout			$0.00
96			Concrete			$0.00
97			Structural Steel			$0.00
98			Rail & Invert			$0.00
99			Mud lines etc.			$0.00
100			Misc.-Small Tools, Etc.			$ 18,390.24
101			TOTAL ESTIMATED MATERIAL COST			$18,390.24
102			Unit cost			$59.32
103
104
105		3	Sub Contract
106				Units	Cost per Unit	Total Cost
107			Dewatering			$0.00
108			Crane & Excavator Hauling	2	3000	$6,000.00
109			Welding			$0.00
110			Equipment Long Hauls	2	1000	$2,000.00
111			Equipment Short Hauls		500	$0.00
112			Concrete Pumping			$0.00
113			Dump Truck Hauling			$0.00
114			Sub Contract GL Insurance	$8,000.00	10.47%	$837.36
115			TOTAL ESTIMATED SUB CONTRACT			$8,837.36
116			Unit Cost			$28.51
117
118
119		4 & 5		Equipment
120
121			Description	Number Of Owned Pieces	Number Of Rented Pieces	Number Of Shifts	Rate	Total Cost Owned Equipment	Total Cost Rented Equipment
122			BASIC TUNNEL EQUIPMENT	1		36	325	$11,700.00	$0.00
123			BASIC BARBCO DRY BORE RIG			36	225	$0.00	$0.00
124			BASIC BARBCO DRY BORE TOOLS			36		$0.00	$0.00
125			Gen Set for TBM			36	225	$0.00	$0.00
126			Excavator	1		36	650	$23,400.00	$0.00
127			Crane		1	36	600	$0.00	$21,600.00
128			Rubber Tire Loader			36		$0.00	$0.00
129			Standard jacks, rails, power unit			36		$0.00	$0.00
130			Grout Pump/Hose	1		36	50	$1,800.00	$0.00
131			Large Jacks, Stands, Power unit			36		$0.00	$0.00
132			Skid Steer Loader			36		$0.00	$0.00
133			TBM/Shield			36		$0.00	$0.00
134			Trench Boxes		2	36	135	$0.00	$9,720.00
135								$36,900.00	$31,320.00
136			Fuel, Oil, & maintenance	32%				$11,808.00	$10,022.40
137			TOTAL ESTIMATED EQUIPMENT COST:					$48,708.00	$41,342.40
138			UNIT COST:					$ 157.12	$ 133.36
139
140
141		6	LABOR BURDEN
142			TOTAL ESTIMATED LABOR BURDEN		33%	Percent of Raw Labor Dollars		$33,715.44
143
144		9	FIELD OVERHEAD ALLOCATION
145			FIELD OVERHEAD ALLOCATION		35%	Percent of Raw Labor Dollars		$35,758.80
146
147		10	PER DIEM
148				# Employees	Rate	# Shifts	Total
149			Per Diem		150	36	$ -
150
151			CONTINGENCY
152				Description	#	Amount	TOTAL	NOTE: this is factored into the suggested price but is not included in the budget.
153							$ -
154							$ -
155				Total Contingency Cost			$ -
156
157		SUMMARY
158
159		#	Work Type	Projected Cost
160				Cost/Ft.	Total Costs	PCT Suggested	PCT Actual
161		1	Labor	$ 410.86	$ 127,368.00	33%	0%
162		2	Materials	$ 59.32	$ 18,390.24	5%	0%
163		3	Sub Contract	$ 28.51	$ 8,837.36	2%	0%
164		4	Owned Equipment	$ 157.12	$ 48,708.00	13%	0%
165		5	Rented Equipment	$ 133.36	$ 41,342.40	11%	0%
166		6	Labor Burden	$ 108.76	$ 33,715.44	9%	0%
167		9	Field Overhead Allocation	$ 115.35	$ 35,758.80	9%	0%
168		10	Per Diem	$ -	$ -	0%	0%
169		n/a	Contingency	$ -	$ -	0%	0%
170			Total Field Cost	$ 1,013.29	$ 314,120.24	81%	0%
171
172			General Overhead	$ 108.76	$ 33,715.44	9%	0%
173			Total Job Costs	$ 1,122.05	$ 347,835.68	90%	0%
174			GENERAL OVERHEAD %:	33%	of raw labor dollars				Actual bid reasoning:
175
176			Bid	Suggested Bid			Actual Bid
177				Price per foot	Total		Price per foot	Total
178			TOTAL BID	$ 1,246.72	$ 386,484.09			$0.00
179
180
181			Total Profit Calculation	Based on Suggested Price			Based on Actual Bid
182				Per LF	Total		Price per foot	Total
183			Job Profit before income taxes	$ 124.67	$ 38,648.41		$ (1,122.05)	($347,835.68)
184			Income taxes	49.25	15,266.12		$ (443.21)	$ (137,395.09)
185			Job Profit after income taxes	$ 75.43	$ 23,382.29		$ (678.84)	$ (210,440.59)
186
187			TOTAL MAN HOURS TO COMPLETE		3,240
188			COST PER SHIFT		$ 9,662.10
189
Estimate One

Cell Formulas
Range	Formula
G6	G6	='TAKE OFF'!C6
D6	D6	='TAKE OFF'!B2
D7:D8	D7	='TAKE OFF'!B4
D10	D10	='TAKE OFF'!B3
C13	C13	='TAKE OFF'!B7:D7
I13	I13	=G178
I14	I14	=H178
I16	I16	=D178
I17	I17	=E178
D19	D19	='Price Calculator'!B3:B21
I26	I26	=E70
H39:H48,I122:I134	H39	=D39*E39*F39
I39:I48	I39	=D39*E39*G39
E39	E39	=F26
E40	E40	=F26
E41	E41	=F26
E42	E42	=F26
E43	E43	=F26
E44	E44	=F26
E45	E45	=F26
E46	E46	=F26
E47	E47	=F26
E48	E48	=F26
E50	E50	=SUM(D39:D49)*F26
H50:I50	H50	=SUM(H39:H48)
E56	E56	=ROUNDUP((D19)/D25,0)-E67
E64	E64	=ROUNDUP(SUM(E53:E63, E65:E69)*10%,0)
E70	E70	=SUM(E53:E69)
H71	H71	=H50
H72	H72	=IF(G57>=1,H71*H57,IF(G58>=1,H71*H58,IF(G59>=1,H71*H59,IF(G60>=1,H71*H60,IF(G61>=1,H71*H61,H71)))))
H73	H73	=I50
H74	H74	=H72+H73+H49
H76	H76	=E70
H77	H77	=H74*E70
H78	H78	=H77*(G78)
H79,G155	H79	=SUM(H77:H78)
H80	H80	=H79/D19
G83	G83	=F83*E83*D83
H83	H83	=F83*E83*D83*I26
H85	H85	=H83+H79
H86	H86	=H85/D19
D90	D90	=D19
D91	D91	=D19
D92	D92	=D19
D93	D93	=D19
D94	D94	=D19
F90:F99,G153:G154,F107:F114	F90	=D90*E90
F100	F100	=H79*18%
F101	F101	=SUM(F90:F100)
F102	F102	=F101/D19
D114	D114	=SUM(F107:F113)
E114	E114	=0.04084+0.06383
F115	F115	=ROUNDUP(SUM(F107:F114),2)
F116	F116	=F115/D19
H122:H134	H122	=D122*F122*G122
H135:I135	H135	=SUM(H122:H134)
H136	H136	=H135*0.32
I136	I136	=I135*D136
H137:I137	H137	=H136+H135
H138:I138	H138	=H137/$D19
F122	F122	=E70
F123	F123	=E70
F124	F124	=E70
F125	F125	=E70
F126	F126	=E70
F127	F127	=E70
F128	F128	=E70
F129	F129	=E70
F130	F130	=E70
F131	F131	=E70
F132	F132	=E70
F133	F133	=E70
F134	F134	=E70
H142	H142	=H79*E142
H145	H145	=E145*$H$79
F149	F149	=E70
G149	G149	=D149*E149*F149
D161	D161	=E161/D19
E161	E161	=H85
F161:F170,F172:F173	F161	=E161/$E$178
G161:G170,G172:G173	G161	=IF($G$178="",0,E161/$H$178)
D162	D162	=E162/D19
E162	E162	=F101
D163	D163	=E163/D19
E163	E163	=F115
D164:D165	D164	=E164/$D$19
E164	E164	=H137
E165	E165	=I137
D166	D166	=E166/D19
E166	E166	=H142
D167	D167	=E167/D19
E167	E167	=H145
D168	D168	=E168/D19
E168	E168	=G149
D169	D169	=E169/D19
E169	E169	=G155
D170	D170	=E170/D19
E170	E170	=SUM(E161:E169)
D172	D172	=E172/D19
E172	E172	=D174*H79
D173	D173	=D170+D172
E173	E173	=E172+E170
D178	D178	=E178/D19
E178	E178	=E173/0.9
H178	H178	=G178*D19
D183	D183	=E183/D19
E183	E183	=E178-E173
D184	D184	=E184/D19
E184	E184	=0.395*E183
D185	D185	=E185/D19
E185,H185	E185	=E183-E184
G183	G183	=H183/D19
H183	H183	=H178-E173
G184	G184	=H184/D19
H184	H184	=H183*0.395
G185	G185	=H185/D19
E187	E187	=E50*E70
E188	E188	=E173/E70
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[bryandaniel5]

here's the table with list of input variables

NHCRWA (10-10-23).xlsx
	A	B	C	D
2	Location	LF	Unit Price	Total
3	Copeland @ Mills	310
4	Along Mills Rd at drives	207
5	Along Mills Rd opposite Willow Park Village Dr	393
6	Along Mills Rd at exitsitn storm sewer	92
7	Along Mills Rd at drives	54
8	Pipeline ROW and Mills rd	178
9	Along Mills Rd at drives	124
10	Along Mills Rd at drives	165
11	Along Mills Rd at Pipeline ROW	161
12	Along Mills Rd at drives	230
13	Under Perry Rd near Mills Rd	145
14	Along Mills Rd at drives	53
15	Mills Rd	538
16	Drive off Mills Rd	62
17	Drive off Mills Rd	30
18	Mills Rd	106
19	Mills Rd	179
20	Mills Rd	100
21	Mills Rd	97
Price Calculator

Cell Formulas
Range	Formula
A3:A21	A3	='TAKE OFF'!D24
B3:B21	B3	='TAKE OFF'!C24

 

[dreid1011]

Ah, I see. When you input a value on the second sheet, is there any further manual input before you get the final output? And which cell is the input value entered in on the second sheet?

 

[bryandaniel5]

Cell D19 is where I would normally enter the value from the Table with list of values. There's a bunch of other variables to adjust and enter but those would remain constant regardless of what is entered in D19. The value that results from the calculation in I16 is what i want to go into the table with all the input variables. I can do this manually but i'd like to find an easier way that can be used over and over.

NHCRWA (10-10-23).xlsx
	A	B	C
2	Location	LF	Unit Price
3	Copeland @ Mills	310	$ 2,146.72
4	Along Mills Rd at drives	207	$ 942.96
5	Along Mills Rd opposite Willow Park Village Dr	393	$ 1,482.00
6	Along Mills Rd at exitsitn storm sewer	92
7	Along Mills Rd at drives	54
8	Pipeline ROW and Mills rd	178
9	Along Mills Rd at drives	124
10	Along Mills Rd at drives	165
11	Along Mills Rd at Pipeline ROW	161
12	Along Mills Rd at drives	230
13	Under Perry Rd near Mills Rd	145
14	Along Mills Rd at drives	53
15	Mills Rd	538
16	Drive off Mills Rd	62
17	Drive off Mills Rd	30
18	Mills Rd	106
19	Mills Rd	179
20	Mills Rd	100
21	Mills Rd	97
Price Calculator

Cell Formulas
Range	Formula
A3:A21	A3	='TAKE OFF'!D24

 

[dreid1011]

I'm thinking a short bit of VBA to loop through the input variables and enter them into D19, then copy the output back to the first sheet will best here. If only formulas could push values to other cells.

 

[dreid1011]

Give this a try in the sheet code for "Price Calculator".

Private Sub PriceCalc()
Dim ws As Worksheet
Dim i&, lRow&

Set ws = Worksheets("Estimate One")

lRow = Range("B" & Rows.Count).End(xlUp).Row

For i = 3 To lRow
    ws.Range("D19").Value = Range("B" & i).Value
    Range("C" & i).Value = ws.Range("I16").Value
Next i
    
End Sub

 

[bryandaniel5]

well I guess I'll have to learn VBA. thanks for your help! any recommendations about the best place to learn VBA?

 

[dreid1011]

well I guess I'll have to learn VBA. thanks for your help! any recommendations about the best place to learn VBA?

Google and a desire to create a fancy spreadsheet, haha. That is how I learned. I had some programming logic experience before learning VBA so it's just been a case of learning the language for me. I am not nearly as quick or fluent as most other regulars here, but I can get the job done most of the time.

And you're welcome. I am happy to help.