Results 1 to 9 of 9
Thread: Problem with formula (Excel 97)

20030116, 08:44 #1
 Join Date
 Feb 2001
 Posts
 107
 Thanks
 0
 Thanked 0 Times in 0 Posts
Problem with formula (Excel 97)
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.

20030116, 09:02 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Problem with formula (Excel 97)
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))

20030116, 09:48 #3
 Join Date
 Feb 2001
 Posts
 107
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Problem with formula (Excel 97)
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

20030116, 09:59 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Problem with formula (Excel 97)
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.

20030116, 10:37 #5
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Problem with formula (Excel 97)
Could you elaborate what the formulas have to do exactly:
What has to be subtracted from what (and how to determine)?Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20030116, 11:50 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Problem with formula (Excel 97)
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

20030116, 12:06 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Problem with formula (Excel 97)
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:S2 7,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)+M ATCH(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.

20030116, 12:13 #8
 Join Date
 Feb 2001
 Posts
 107
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Problem with formula (Excel 97)
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

20030118, 14:20 #9
 Join Date
 Dec 2000
 Location
 Sault Ste. Marie, Michigan, USA
 Posts
 102
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Problem with formula (Excel 97)
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