Offset Sum with Variable formula depending on row.

[Scotster]

I've no idea how to describe what I'm looking for in a heading, so hopefully the below explains it a little better. Is there a more elegant way of calculating the below.

The formula I have in D6 is as follows:

=IF($C6="Net",SUM(OFFSET($D6,MATCH(1,($C1:$C6="Demand")*($B1:$B6=$B6)*($A1:$A6=$A6),0)-6,0):$D5),IF($C6="Demand",-10,5))

It's copied from D4 to D16, where D4 and D5 will REF error for the "Net" true calculation, but doesn't affect anything.

My aim is to replace the "-10" and "5" with a formula from another sheet. The reason for the convoluted method is there can be multiple Suppliers, so some parts cover 3 lines or more. The offset only allows for 6 lines at the maximum so I was hoping there would be a better option.

What I want is for the demand line to use one calculation, the net line to use another calculation and the remaining supply lines to use a 3rd calculation. There are 10000s of rows, so creating one formula to copy down the entire sheet I feel is beneficial, rather than using VBA to determine the individual formulae per row. The difficulty with the "Net" calculation, is that it's simply a sum of the rows above it..... up to and including the "Demand" row. With the "Demand" row varying in distance I've used the match to find the correct offset.

As always, any help gratefully appreciated

 

[awoohaw]

i don't know why that put a diff formula in row 14 hopefully this doesn't:

mr excel questions 20.xlsm
	A	B	C	D	E
1	Material	Location	Demand/Sup/Net
2	Part 1	Loc 1	Demand	15
3	Part 1	Loc 1	Supp 1	-5
4	Part 1	Loc 1	Net	10
5	Part 1	Loc 2	Demand	15
6	Part 1	Loc 2	Supp 1	-5
7	Part 1	Loc 2	Supp 2	-5
8	Part 1	Loc 2	Net	5
9	Part 2	Loc 1	Demand	15
10	Part 2	Loc 1	Supp 1	-5
11	Part 2	Loc 1	Net	10
12	Part 3	Loc 1	Demand	15
13	Part 3	Loc 1	Supp 1	-5
14	Part 3	Loc 1	Net	10
15
Scotster

Cell Formulas
Range	Formula
D2:D14	D2	=IF($C2="Demand",15,IF($C2<>"Net",-5,SUM(INDEX($D$2:$D2,SUM(MAX((--(ISNUMBER(FIND("Demand",$C$2:$C2))))*(ROW($C$2:$C2)-1))),1):$D1)))

 

[Scotster]

i think this is it:

mr excel questions 20.xlsm
	A	B	C	D	E
1	Material	Location	Demand/Sup/Net
2	Part 1	Loc 1	Demand	15
3	Part 1	Loc 1	Supp 1	-5
4	Part 1	Loc 1	Net	10
5	Part 1	Loc 2	Demand	15
6	Part 1	Loc 2	Supp 1	-5
7	Part 1	Loc 2	Supp 2	-5
8	Part 1	Loc 2	Net	5
9	Part 2	Loc 1	Demand	15
10	Part 2	Loc 1	Supp 1	-5
11	Part 2	Loc 1	Net	10
12	Part 3	Loc 1	Demand	15
13	Part 3	Loc 1	Supp 1	-5
14	Part 3	Loc 1	Net	10
15
Scotster

Cell Formulas
Range	Formula
D2:D13	D2	=IF($C2="Demand",15,IF($C2<>"Net",-5,SUM(INDEX($D$2:$D2,SUM(MAX((--(ISNUMBER(FIND("Demand",$C$2:$C2))))*(ROW($C$2:$C2)-1))),1):$D1)))
D14	D14	=IF($C14="Demand",10,IF($C14<>"Net",-5,SUM(INDEX($D$2:$D$14,SUM(MAX((--(ISNUMBER(FIND("Demand",$C$2:$C16))))*(ROW($C$2:$C16)-1))),1):$D15)))

That looks to be ideal, just thinking about how it works. Will try it out tomorrow and see how it works with the actual sheet.

Many thanks for helping out and getting back so quickly

 

[awoohaw]

I used 15 and -5 in my testing. I hope you can figure it out.
It also avoid using the OFFSET function, which is volatile and can slow your worksheets to a crawl if its big.

Also, it is using array formulas, you may need to enter them with CNTRL-SHFT-ENTER

The INDEX function can be used to build range references and that is what this is doing.

Note, I posted an updated mini worksheet. the original had a different formula in row 14 and I wanted it removed.

 

[awoohaw]

Here is what the formula does:

SUM(INDEX($D$2:$D2,SUM(MAX((--(ISNUMBER(FIND("Demand",$C$2:$C2))))*(ROW($C$2:$C2)-1))),1):$D1)

The red part is creating a cell reference that is located on the last Demand above the cell the formula is in.

INDEX($D$2:$D2,SUM(MAX((--(ISNUMBER(FIND("Demand",$C$2:$C2))))*(ROW($C$2:$C2)-1))),1)

The red part here is making an array of 1s and 0s, where if the word demand is in a cell it will have a one and if not it will have a zero

INDEX($D$2:$D2,SUM(MAX((--(ISNUMBER(FIND("Demand",$C$2:$C2))))*(ROW($C$2:$C2)-1))),1)

The red part here is creating and array of numbers beginning with the number 1, with the number 1 being the first row of the range identified in the first part of the matrix formula.

The two arrays above are then multiplied together to get the row number in the INDEX range where the word Demand is on that row.

If you notice the INDEX range is a dynamic going only to the row above the current cell. And since you want to start with the demand closest to this row,… we get the maximum of the multiplied array. This identifies the row in the INDEX range to use.

The last argument of the INDEX formula portion is just the number 1, indicating the column number.

In addition to return values from a range INDEX can be used to calculate a cell reference when paired with a colon after the function, so by referencing the cell above the formula cell (in this case formula is in $D2, so the end reference is $D1.

Finally, its wrapped up with the SUM function.

Since this formula uses array formulas, you may need to use the CNTL-SHFT-ENTER method to tell excel “Hey process the arrays here!”.

 

[awoohaw]

Here is what the formula does:

SUM(INDEX($D$2:$D2,SUM(MAX((--(ISNUMBER(FIND("Demand",$C$2:$C2))))*(ROW($C$2:$C2)-1))),1):$D1)

The red part is creating a cell reference that is located on the last Demand above the cell the formula is in.

INDEX($D$2:$D2,SUM(MAX((--(ISNUMBER(FIND("Demand",$C$2:$C2))))*(ROW($C$2:$C2)-1))),1)

The red part here is making an array of 1s and 0s, where if the word demand is in a cell it will have a one and if not it will have a zero

INDEX($D$2:$D2,SUM(MAX((--(ISNUMBER(FIND("Demand",$C$2:$C2))))*(ROW($C$2:$C2)-1))),1)

The red part here is creating and array of numbers beginning with the number 1, with the number 1 being the first row of the range identified in the first part of the matrix formula.

The two arrays above are then multiplied together to get the row number in the INDEX range where the word Demand is on that row.

If you notice the INDEX range is a dynamic going only to the row above the current cell. And since you want to start with the demand closest to this row,… we get the maximum of the multiplied array. This identifies the row in the INDEX range to use.

The last argument of the INDEX formula portion is just the number 1, indicating the column number.

In addition to return values from a range INDEX can be used to calculate a cell reference when paired with a colon after the function, so by referencing the cell above the formula cell (in this case formula is in $D2, so the end reference is $D1.

Finally, its wrapped up with the SUM function.

Since this formula uses array formulas, you may need to use the CNTL-SHFT-ENTER method to tell excel “Hey process the arrays here!”.

gahhhh. my grammar and spelling is horrible, i hope you can decipher.

 

[Scotster]

I can confirm it works exactly as intended. Thanks very much for the help. It makes it far less cumbersome to use the Max to find the last demand point

 

[awoohaw]

I can confirm it works exactly as intended. Thanks very much for the help. It makes it far less cumbersome to use the Max to find the last demand point

Happy to help!

Best wishes.