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]

 

[jjramirez85]

https://drive.google.com/file/d/1RzJxVeF87W-15VgrKeUEtSYvcU_J_8vl/view?usp=sharing

When summing ext cost to equal total cost, and returns being 80% of total cost, the $0.19 must equal the same value returned as $0.3206.

The goal seek in the previous example by the other member solves the problem, but I want in formula.

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"]
<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]