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!
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!