How can I automate these formulas?

Question

Hello, I need to automate these formula.

Wheat 2 USA minus wheat 1 USA, Wheat 2 Brazil minus wheat 1 Brazil, Wheat 2 Canada minus wheat 1 Canada, Wheat 2 Argentina minus wheat 1 Argentina and so on for every product and taking into account corresponding regions and week numbers.

Barley 2 USA minus barley 1 USA, Barley 2 Brazil minus barley 1 Brazil, Barley 2 Canada minus barley 1 Canada ...

A formula should work in such a way that when I populate it, it understands the logic and make a calculation.

Answer 1

First: I like @basic's solution much better! I keep forgetting that =LOOKUP(LargerThanAll,Nums,Values) returns the value for the highest num!

That said, here's another way:

First, put your cursor on the top line and Insert - Table, making sure you check the "My table has headers" checkbox in the dialog box that appears.

Now, in addition to having it formatted, there are formula benefits.

Keeping with your example where the B2 = "Product",

• Change F2 to "Product|Number" to create a new column
• Change F3 to =[@Product]&"|"&[@[Input Number]] (Because you're using tables, you can use column names rather than cell references, [@ColumnName] is the column value for the current row, it will use that formula for the entire column, and the formula is stored once instead of once per row!)
• Change G2 to "PrevRow"
• Change G3 to =MATCH([@Product]&"|"&([@[Input Number]]-1),[Product|Number],0) (you now have the table row number for the previous value
• Change E3 to =IF(ISNUMBER([@PrevRow]),[@Quantity]-INDEX([Quantity],[@PrevRow]),"")
• Hide columns F and G. (Or fold their functionality into your Volume's formula so your volume formula doesn't need them and delete them.)

Answer 2

Use LOOKUP to find previous value of current product:

=IFERROR(C2-LOOKUP(2,1/($A$1:A1=A2),$C$1:C1),"")

or so if the Input number column matters:

=IFERROR(C2-LOOKUP(2,1/(($A$1:A1=A2)*($B$1:B1=(B2-1))),$C$1:C1),"")