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

[TABLE="width: 470"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Component[/TD]
[TD]UOM[/TD]
[TD] QTY [/TD]
[TD]UNIT COST[/TD]
[TD]EXT COST[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123451[/TD]
[TD]LBS.[/TD]
[TD] 0.5100[/TD]
[TD] $ 0.2454[/TD]
[TD="align: right"]$0.12515[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123452[/TD]
[TD]LBS.[/TD]
[TD] 0.0007[/TD]
[TD] $ 8.3360[/TD]
[TD="align: right"]$0.00591[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123453[/TD]
[TD]LBS.[/TD]
[TD] 0.0019[/TD]
[TD] $ 0.5523[/TD]
[TD="align: right"]$0.00105[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123454[/TD]
[TD]LBS.[/TD]
[TD] 0.0397[/TD]
[TD] $ 0.0818[/TD]
[TD="align: right"]$0.00324[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123455[/TD]
[TD]LBS.[/TD]
[TD] 0.0033[/TD]
[TD] $ 0.1850[/TD]
[TD="align: right"]$0.00062[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]LBS.[/TD]
[TD] 0.0041[/TD]
[TD] $ 1.0573[/TD]
[TD="align: right"]$0.00429[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123457[/TD]
[TD]LBS.[/TD]
[TD] 0.0054[/TD]
[TD] $ 1.6464[/TD]
[TD="align: right"]$0.00896[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123458[/TD]
[TD]LBS.[/TD]
[TD] 0.0434[/TD]
[TD] $ 0.7721[/TD]
[TD="align: right"]$0.03355[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123459[/TD]
[TD]LBS.[/TD]
[TD] 0.0003[/TD]
[TD] $ 2.6779[/TD]
[TD="align: right"]$0.00083[/TD]
[TD][/TD]
[TD]Return[/TD]
[TD="align: right"]80%[/TD]
[/TR]
[TR]
[TD]123460[/TD]
[TD]LBS.[/TD]
[TD] 0.5000[/TD]
[TD] XXXX[/TD]
[TD="align: right"]$0.00000[/TD]
[TD][/TD]
[TD="align: right"]$0.19[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123461[/TD]
[TD]LBS.[/TD]
[TD] 0.0005[/TD]
[TD] $ 6.7730[/TD]
[TD="align: right"]$0.00354[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123462[/TD]
[TD]LBS.[/TD]
[TD] 0.0192[/TD]
[TD] $ 0.7523[/TD]
[TD="align: right"]$0.01441[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123463[/TD]
[TD]LBS.[/TD]
[TD] 0.0007[/TD]
[TD] $ 1.2633[/TD]
[TD="align: right"]$0.00085[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123464[/TD]
[TD]LBS.[/TD]
[TD] 0.0048[/TD]
[TD] $ 7.9713[/TD]
[TD="align: right"]$0.03805[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$0.24046[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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,

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][td="bgcolor:#C0C0C0"]

G​

[/td][td="bgcolor:#C0C0C0"]

H​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Comp​

[/td][td="bgcolor:#F3F3F3"]

UoM​

[/td][td="bgcolor:#F3F3F3"]

Qty​

[/td][td="bgcolor:#F3F3F3"]

Unit Cost​

[/td][td="bgcolor:#F3F3F3"]

Ext Cost​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

123451​

[/td][td]LBS.[/td][td]

0.5100​

[/td][td]

$0.2454​

[/td][td]

$0.12515​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

123452​

[/td][td]LBS.[/td][td]

0.0007​

[/td][td]

$8.3360​

[/td][td]

$0.00584​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

123453​

[/td][td]LBS.[/td][td]

0.0019​

[/td][td]

$0.5523​

[/td][td]

$0.00105​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

123454​

[/td][td]LBS.[/td][td]

0.0397​

[/td][td]

$0.0818​

[/td][td]

$0.00325​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

123455​

[/td][td]LBS.[/td][td]

0.0033​

[/td][td]

$0.1850​

[/td][td]

$0.00061​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

123456​

[/td][td]LBS.[/td][td]

0.0041​

[/td][td]

$1.0573​

[/td][td]

$0.00433​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

123457​

[/td][td]LBS.[/td][td]

0.0054​

[/td][td]

$1.6464​

[/td][td]

$0.00889​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

123458​

[/td][td]LBS.[/td][td]

0.0434​

[/td][td]

$0.7721​

[/td][td]

$0.03351​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

123459​

[/td][td]LBS.[/td][td]

0.0003​

[/td][td]

$2.6779​

[/td][td]

$0.00080​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

123460​

[/td][td]LBS.[/td][td]

0.5000​

[/td][td="bgcolor:#CCFFFF"]

$0.2935​

[/td][td]

$0.14675​

[/td][td="bgcolor:#CCFFCC"]

80%​

[/td][td][/td][td="bgcolor:#CCFFFF"]D11: =0.8 * SUM(E2:E10) / C11[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

123461​

[/td][td]LBS.[/td][td]

0.0005​

[/td][td]

$6.7730​

[/td][td]

$0.00339​

[/td][td][/td][td][/td][td="bgcolor:#CCFFCC"]F11: =E11/SUM(E2:E10)[/td][/tr]
[/table]

 

[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.

[TABLE="width: 384"]
<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>[TR]
[TD="width: 64, bgcolor: transparent"]Component[/TD]
[TD="width: 64, bgcolor: transparent"]UOM[/TD]
[TD="width: 64, bgcolor: transparent"]QTY [/TD]
[TD="width: 64, bgcolor: transparent"]UNIT COST[/TD]
[TD="width: 64, bgcolor: transparent"]EXT COST[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]123451[/TD]
[TD="bgcolor: transparent"]LBS.[/TD]
[TD="bgcolor: transparent, align: right"]0.51000[/TD]
[TD="bgcolor: transparent, align: right"]0.24540[/TD]
[TD="bgcolor: transparent, align: right"]0.12515[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]123452[/TD]
[TD="bgcolor: transparent"]LBS.[/TD]
[TD="bgcolor: transparent, align: right"]0.00070[/TD]
[TD="bgcolor: transparent, align: right"]8.33600[/TD]
[TD="bgcolor: transparent, align: right"]0.00591[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]123453[/TD]
[TD="bgcolor: transparent"]LBS.[/TD]
[TD="bgcolor: transparent, align: right"]0.00190[/TD]
[TD="bgcolor: transparent, align: right"]0.55230[/TD]
[TD="bgcolor: transparent, align: right"]0.00105[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]123454[/TD]
[TD="bgcolor: transparent"]LBS.[/TD]
[TD="bgcolor: transparent, align: right"]0.03970[/TD]
[TD="bgcolor: transparent, align: right"]0.08180[/TD]
[TD="bgcolor: transparent, align: right"]0.00324[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]123455[/TD]
[TD="bgcolor: transparent"]LBS.[/TD]
[TD="bgcolor: transparent, align: right"]0.00330[/TD]
[TD="bgcolor: transparent, align: right"]0.18500[/TD]
[TD="bgcolor: transparent, align: right"]0.00062[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]123456[/TD]
[TD="bgcolor: transparent"]LBS.[/TD]
[TD="bgcolor: transparent, align: right"]0.00410[/TD]
[TD="bgcolor: transparent, align: right"]1.05730[/TD]
[TD="bgcolor: transparent, align: right"]0.00429[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]123457[/TD]
[TD="bgcolor: transparent"]LBS.[/TD]
[TD="bgcolor: transparent, align: right"]0.00540[/TD]
[TD="bgcolor: transparent, align: right"]1.64640[/TD]
[TD="bgcolor: transparent, align: right"]0.00896[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]123458[/TD]
[TD="bgcolor: transparent"]LBS.[/TD]
[TD="bgcolor: transparent, align: right"]0.04340[/TD]
[TD="bgcolor: transparent, align: right"]0.77210[/TD]
[TD="bgcolor: transparent, align: right"]0.03355[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]123459[/TD]
[TD="bgcolor: transparent"]LBS.[/TD]
[TD="bgcolor: transparent, align: right"]0.00030[/TD]
[TD="bgcolor: transparent, align: right"]2.67790[/TD]
[TD="bgcolor: transparent, align: right"]0.00083[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Return[/TD]
[TD="bgcolor: transparent, align: right"]80%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]123460[/TD]
[TD="bgcolor: transparent"]LBS.[/TD]
[TD="bgcolor: transparent, align: right"]0.50000[/TD]
[TD="bgcolor: transparent"]XXXX[/TD]
[TD="bgcolor: transparent, align: right"]0.00000[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]$0.19 [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]123461[/TD]
[TD="bgcolor: transparent"]LBS.[/TD]
[TD="bgcolor: transparent, align: right"]0.00050[/TD]
[TD="bgcolor: transparent, align: right"]6.77300[/TD]
[TD="bgcolor: transparent, align: right"]0.00354[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]123462[/TD]
[TD="bgcolor: transparent"]LBS.[/TD]
[TD="bgcolor: transparent, align: right"]0.01920[/TD]
[TD="bgcolor: transparent, align: right"]0.75230[/TD]
[TD="bgcolor: transparent, align: right"]0.01441[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]123463[/TD]
[TD="bgcolor: transparent"]LBS.[/TD]
[TD="bgcolor: transparent, align: right"]0.00070[/TD]
[TD="bgcolor: transparent, align: right"]1.26330[/TD]
[TD="bgcolor: transparent, align: right"]0.00085[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]123464[/TD]
[TD="bgcolor: transparent"]LBS.[/TD]
[TD="bgcolor: transparent, align: right"]0.00480[/TD]
[TD="bgcolor: transparent, align: right"]7.97130[/TD]
[TD="bgcolor: transparent, align: right"]0.03805[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]0.24045[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow, align: right"]0.3206[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]