Goal Seek Type Formula

[jjramirez85]

I have a total cell ($.024046) that sums all rows above which contain 1 blank cell (XXXX). On the side is a cell which equals 80% of the total cell ($0.19). How can I make the blank cell match the cell returning 80% of the total. With trial and error, $.32 in the blank cell will output $.32 as 80% of the total. They match. How can I match with formula? I tried goal seek but it did not work.

Reference: Ext cost = qty x unit cost

Component	UOM	QTY	UNIT COST	EXT COST
123451	LBS.	0.5100	$ 0.2454	$0.12515
123452	LBS.	0.0007	$ 8.3360	$0.00591
123453	LBS.	0.0019	$ 0.5523	$0.00105
123454	LBS.	0.0397	$ 0.0818	$0.00324
123455	LBS.	0.0033	$ 0.1850	$0.00062
123456	LBS.	0.0041	$ 1.0573	$0.00429
123457	LBS.	0.0054	$ 1.6464	$0.00896
123458	LBS.	0.0434	$ 0.7721	$0.03355
123459	LBS.	0.0003	$ 2.6779	$0.00083		Return	80%
123460	LBS.	0.5000	XXXX	$0.00000		$0.19
123461	LBS.	0.0005	$ 6.7730	$0.00354
123462	LBS.	0.0192	$ 0.7523	$0.01441
123463	LBS.	0.0007	$ 1.2633	$0.00085
123464	LBS.	0.0048	$ 7.9713	$0.03805
				$0.24046

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[shg]

It appears you're missing some significant digits -- the EXT column doesn't track the qty and unit cost numbers.

 

[shg]

With waht's there,

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Comp​	UoM​	Qty​	Unit Cost​	Ext Cost​	​	​	​
2​	123451​	LBS.	0.5100​	$0.2454​	$0.12515​
3​	123452​	LBS.	0.0007​	$8.3360​	$0.00584​
4​	123453​	LBS.	0.0019​	$0.5523​	$0.00105​
5​	123454​	LBS.	0.0397​	$0.0818​	$0.00325​
6​	123455​	LBS.	0.0033​	$0.1850​	$0.00061​
7​	123456​	LBS.	0.0041​	$1.0573​	$0.00433​
8​	123457​	LBS.	0.0054​	$1.6464​	$0.00889​
9​	123458​	LBS.	0.0434​	$0.7721​	$0.03351​
10​	123459​	LBS.	0.0003​	$2.6779​	$0.00080​
11​	123460​	LBS.	0.5000​	$0.2935​	$0.14675​	80%​		D11: =0.8 * SUM(E2:E10) / C11
12​	123461​	LBS.	0.0005​	$6.7730​	$0.00339​			F11: =E11/SUM(E2:E10)

 

[sheetspread]

Solver works:

 

[sheetspread]

(Nonlinear instead of evolutionary is better above)

Here's one way to match with Goalseek:

 

[LloydFinancials]

Unit Cost = .8(Current Grand Total of Extended Cost)/(1-.8(Qty))

 

[jjramirez85]

The extended cost = qty x unit cost

So, what ever is plugged in to XXXX needs to match the output of the return $. The return $ = total extended cost X 80%

It appears you're missing some significant digits -- the EXT column doesn't track the qty and unit cost numbers.

 

[jjramirez85]

This gave me a circular reference

Unit Cost = .8(Current Grand Total of Extended Cost)/(1-.8(Qty))

 

[jjramirez85]

The goal seek method with the zero out cell worked. Could not come up with this for some reason. I would ultimately like to have a formula, so if I change anything in the cost or qty, then it would update as changes are made.

(Nonlinear instead of evolutionary is better above)

Here's one way to match with Goalseek:

 

[LloydFinancials]

I have your data in A1:E16 with the total in E16.
Formula in G16: =0.8*(E16)/(1-0.8*(C11))
I don't get a circular ref.

Component	UOM	QTY	UNIT COST	EXT COST
123451	LBS.	0.51000	0.24540	0.12515
123452	LBS.	0.00070	8.33600	0.00591
123453	LBS.	0.00190	0.55230	0.00105
123454	LBS.	0.03970	0.08180	0.00324
123455	LBS.	0.00330	0.18500	0.00062
123456	LBS.	0.00410	1.05730	0.00429
123457	LBS.	0.00540	1.64640	0.00896
123458	LBS.	0.04340	0.77210	0.03355
123459	LBS.	0.00030	2.67790	0.00083		Return	80%
123460	LBS.	0.50000	XXXX	0.00000		$0.19
123461	LBS.	0.00050	6.77300	0.00354
123462	LBS.	0.01920	0.75230	0.01441
123463	LBS.	0.00070	1.26330	0.00085
123464	LBS.	0.00480	7.97130	0.03805
				0.24045		0.3206

<colgroup><col width="64" style="width: 48pt;" span="2"> <col width="64" style="width: 48pt;" span="3"> <col width="64" style="width: 48pt;" span="3"> <tbody> </tbody>