Super dynamic offset - impossible?

[angelos19]

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!

 

[angelos19]

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

 

[Eric W]

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
	A	B	C	D	E	F	G	H	I	J	K
1	ID 1	ID	6/30/2020	7/31/2020	8/31/2020	9/30/2020		ID	Date	# of months	# of times cost > revenue
2	Revenue	A	15	5	15	25		A	9/30/2020	4	3
3	Cost	A	10	20	30	40
4	Revenue	B	11	21	31	41
5	Cost	B	10	20	30	40
Sheet6

Cell Formulas
Range	Formula
K2	K2	=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]

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
	A	B	C	D	E	F	G	H	I	J	K
1	ID 1	ID	30/06/2020	31/07/2020	31/08/2020	30/09/2020		ID	Date	# of months	# of times cost > revenue
2	Revenue	A	15	5	15	25		A	31/08/2020	3	2
3	Cost	A	10	20	30	40		B	31/08/2020	3	0
4	Revenue	B	11	21	31	41		A	30/06/2020	1	0
5	Cost	B	10	20	30	40		A	30/09/2020	4	3
Sheet1

Cell Formulas
Range	Formula
K2:K5	K2	=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.