Problem with formula (Excel 97)

Topic

New Reply

Hello,

I am working with a formula (that I got from this board a good while ago), which calculates the change versus the data in a previous cell, but it selects a whole range of cells as the data is ‘dynamic’ in nature, meaning is changing all the time.
This is the formula:
=(OFFSET(G27,0,MAX(0,MATCH(0,G27:S27,0)-2))-OFFSET(G27,0,MAX(0,MATCH(0,G27:S27,0)-3)))
The formula works ok as long as the first cell (ex. G27 in my case) is anything higher than 0. However if it is 0, it stopps there, it doesn’t go all the way to S27. Is there a way to overcome this limitation and to let the range be selected from the first cell and only check for the end whether it is 0 or higher?

Attached a small example for illustration. The problem is highlighted with red.

Thanks,
K.

Reply | Quote

Replies

You could let the range for the MATCH function start one column to the right, i.e. in column H instead of in column G, at the same time decreasing the number of columns to subtract by 1 to compensate:

=OFFSET(G27,0,MAX(0,MATCH(0,H27:S27,0)-1))-OFFSET(G27,0,MAX(0,MATCH(0,H27:S27,0)-2))

Reply | Quote

Yes, I actually did that, and it works fine in this particular case. However I haved a large number of data and while some rows start with actual data, some start with 0. It basically keeps track of products and when they entered the market. Not all the brands entered the market in January/February, some in the middle of the year, for example, so the data before that is 0. I was thinking of a generic solution that finds where the data actually started, rather than me going through 1000’s of lines and fixing each of them separately…

Thanks,
K

Reply | Quote

Ah, that wasn’t clear from your initial question. I’m afraid this is beyond me, but I’m sure that one of the Excel formula gurus will come up with a solution.

Reply | Quote

Could you elaborate what the formulas have to do exactly:

What has to be subtracted from what (and how to determine)?

Reply | Quote

Well, I have come up with a solution of sorts, but the formula is horrible; no doubt it can be done much more efficiently:

=OFFSET(G27,0,MAX(0,MATCH(MAX(G27:S27),G27:S27,0)+MATCH(0,OFFSET(G27:S27,0,MATCH(MAX(G27:S27),G27:S27,0)-1,1,COUNTA(G27:S27)-MATCH(MAX(G27:S27),G27:S27,0)+1),0)-3))-OFFSET(G27,0,MAX(0,MATCH(MAX(G27:S27),G27:S27,0)+MATCH(0,OFFSET(G27:S27,0,MATCH(MAX(G27:S27),G27:S27,0)-1,1,COUNTA(G27:S27)-MATCH(MAX(G27:S27),G27:S27,0)+1),0)-4))

Of course, I didn’t write the formula like that; I created a series of intermediate columns with formulas that calculate parts of it, and when I had it working (as far as I can tell), I used these to create the monster formula above. In fact, you may prefer to keep the intermediate columns (hidden if you prefer).

I have attached a zipped workbook with the modified formulas. I hope they do what you want.

Added: please look at the questions in the reply by Steve (sdckapr) – the attached workbook doesn’t handle zeros in the middle of “valid” data correctly.

Reply | Quote

One problem I see is that you are trying to use a “zero” for 3 separate things. Your formula assumes that it is only used for one. You need to define when a zero is what.

Your formula assumes a zero means that this the “end” of the dataset. Anything later than a zero is “future” and can be ignored.
You can also have zeroes at the beginning meaning that they haven’t YET got any. Your formulas do not account for this.
You (possibly) could have a zero in the middle of the dataset meaning for THAT quarter there were none. You haven’t asked about this, but if there is a zero in the middle, it could be mistaken for the beg/end of the dataset depending on how those get determined.

Also should your calcs change when the dataset gets smaller. You look at past6 and past12, and divide by 3 and 6. Should these ( the 3 and 6) change IF there are only a few values. What if there is ONLY 1 quarter that has any data?

You need to be a lot more specific of the possibilities and what you want.

Steve

Reply | Quote

Thanks for all your input on this problem, I know it’s pretty complex, I’m struggling with it for a while now.
Since the 0 which denotes the end of the range is also used in the formula for something else (as an actual data for products that do not have information in them), is it possible to change that first 0 to something else (in my example column M, which denotes the end of the range) and also that reference to the 0 to whatever will be changed in the column M? Maybe in that case the formula will not stop at the first occurance of 0, because it will not search for it, for example if I replace the 0 with a * or any other character…just a thought. But how can I change then the formula to reflect the changes?

Thanks,
K

Reply | Quote

Here’s an example of finding the last column with an array formula.

First example puts the last column in a separate cell, the second integrates it into your own formulas in place of the match function.

HTH
Ken

Reply | Quote