How to handle variable formulas in a table?

[JenniferMurphy]

Is there a good way to handle a situation in a table when the formulas in one or more columns vary from row to row?

This little table that compares the cost of candy bars has 2 records.

• In row 7, the large (49g) bars come 18 to a box. The weight of the box is not given.
• In row 8, the small (17g) bars come in a 320g bag. The number of bars is not given.

I want to compare the cost/bar and the cost/17g of the candy. To do that, I need the total weight of the order and the number of bars.

• Column E has the weight of each package (box or bag). In E7 it is calculated. In E8 it is given.
• Column F has the number of bars/package. In F7 it is given. In F8 is it calculated.

If I add a row, I need to pick one of these 2 package types. Is there a way to do that in one table or am I trying to compare apples and oranges?

What if I add another field, maybe something like "Wt Type", which I could set to "Bag" or "Bar" to indicate what the weight applies to and then modify the formulas based on that?

Should I make two separate tables? If I do, how can I make the last 2 comparisons (columns K & L)?

$Table Test.xlsx
	C	D	E	F	G	H	I	J	K	L
5		Bars	Package		This Order
6	Product	Wt/Bar	Wt/Pkg	Bars/Pkg	#Pkgs	#Bars	Wt	Price	$/Bar	$/17g
7	Box of 36 large bars	49g	1,764g	36	1	36	1,764g	$33.11	$0.92	$0.32
8	2 bags of small bars	17g	320g	18	2	36	640g	$15.88	$0.44	$0.44
Test

Cell Formulas
Range	Formula
E7	E7	=[@[Wt/Bar]]*[@[Bars/Pkg]]
H7:H8	H7	=[@[Bars/Pkg]]*[@['#Pkgs]]
I7:I8	I7	=[@[Wt/Pkg]]*[@['#Pkgs]]
K7:K8	K7	=[@Price]/[@['#Bars]]
L7:L8	L7	=[@[$/Bar]]/([@[Wt/Bar]]/17)
F8	F8	=ROUNDDOWN([@[Wt/Pkg]]/[@[Wt/Bar]],0)

 

[Dermot]

The simplest I can think of is to put the formula into the whole of column E, and if you want to specify a fixed weight, just type it in.
Or you could include a column where you can put in a fixed weight, if applicable. then the next column uses this, but if not provided, it calculates it.

 

[JenniferMurphy]

The simplest I can think of is to put the formula into the whole of column E, and if you want to specify a fixed weight, just type it in.
Or you could include a column where you can put in a fixed weight, if applicable. then the next column uses this, but if not provided, it calculates it.

I tried that. Depending on where the row is that I modify, it seems to change how the table adds subsequent new rows. I haven't quite figured out the algorithm the table used. It seems like it stores the formula outside the table.

Thanks