Super dynamic offset - impossible?

angelos19

New Member
Joined
Nov 13, 2020
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
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
smile.gif


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
    Excel Function Qu.PNG
    5.1 KB · Views: 6

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

angelos19

New Member
Joined
Nov 13, 2020
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
i've just worked out how to solve it. how do i delete the thread?
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,739
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
Joined
May 28, 2005
Messages
48,347
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,928
Messages
5,621,654
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top