# Super dynamic offset - impossible?

#### angelos19

##### New Member
Hello!

I would be super grateful to an excel wizard who can help me out with this.

I want to create a dynamic formula, that will do the following:

- identify right ID, date, and look back over 3months, and check if on any occasion, costs exceed revenue.
- so for eg, formula may want to look up ID B, start date 31/08/2020. It then needs to be able to check the previous 3 months for any occasions that costs exceed revenues. but crucially, this 3 months needs to be dynamic, so that we can look up say 4 or 5 months instead.
- so for eg, for ID B, looking up 31/08/2020, can see that for the 3 months prior (inc 31/08/2020), that revenue does exceed costs for each one.
- note that a bunch of offsets with sums / std dev calcs won't work, as it is not total rev / costs over 3 months that matter, rather needs to look up each individual month

I have wrecked my brain trying to solve this with loads of offsets, but I cannot work out how to have something calculate a dynamic number of pairs simultaneously, ie do like rev minus costs, analysing each pair, but having a dynamic number of pairs.

If an excel genius can solve this, i will be eternally grateful

i get the feeling it will have to be an array formula, but I've never built this one complex.

thanks!

#### Attachments

• Excel Function Qu.PNG
5.1 KB · Views: 6

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

#### angelos19

##### New Member
i've just worked out how to solve it. how do i delete the thread?

#### Eric W

##### MrExcel MVP
Welcome to the forum!

The policy here is that threads are never deleted. They like to maintain a searchable history of everything ever asked and answered. If you would be so kind as to explain how you worked it out, that would be great. I worked on an option that works, but it's pretty complicated.

Book1
ABCDEFGHIJK
1ID 1ID6/30/20207/31/20208/31/20209/30/2020IDDate# of months# of times cost > revenue
2RevenueA1551525A9/30/202043
3CostA10203040
4RevenueB11213141
5CostB10203040
Sheet6
Cell Formulas
RangeFormula
K2K2=SUMPRODUCT(--(MMULT(COLUMN(C1:F1)^0,C2:F5*(C1:F1>EOMONTH(I2,-J2))*(C1:F1<=I2)*(B2:B5=H2)*(A2:A5="cost"))>MMULT(COLUMN(C1:F1)^0,C2:F5*(C1:F1>EOMONTH(I2,-J2))*(C1:F1<=I2)*(B2:B5=H2)*(A2:A5="revenue"))))

It's likely that you could come up with something better, especially if there are rules such as all the IDs are sorted together, and the Revenue and Cost rows are always together.

#### Peter_SSs

##### MrExcel MVP, Moderator
This is what I came up with. I have assumed ...
- that column J would not be filled with a value greater than would allow for that many months before the column I date to be checked in the table. This could be enforced with Data Validation in column J
- that column I will be filled with one of the actual dates in C1:F1

angelos19.xlsm
ABCDEFGHIJK
1ID 1ID30/06/202031/07/202031/08/202030/09/2020IDDate# of months# of times cost > revenue
2RevenueA1551525A31/08/202032
3CostA10203040B31/08/202030
4RevenueB11213141A30/06/202010
5CostB10203040A30/09/202043
Sheet1
Cell Formulas
RangeFormula
K2:K5K2=SUMPRODUCT(--(OFFSET(B\$1,MATCH(H2,B\$2:B\$5,0)+1,MATCH(I2,C\$1:F\$1,0),1,-J2)>OFFSET(B\$1,MATCH(H2,B\$2:B\$5,0),MATCH(I2,C\$1:F\$1,0),1,-J2)))

If either of the assumptions is incorrect, then I think the formula could be amended if details were known.

Replies
7
Views
64
Replies
2
Views
347
Replies
4
Views
174
Replies
0
Views
45
Replies
1
Views
151

### Forum statistics

1,126,928
Messages
5,621,642
Members
415,849
Latest member
PhoenixRising2015

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back