OFFSET problem
 

Hi,

I have a problem when using the offset function. I am using the Match
function to find the first and last occurences of a string in a series
of data. When the string is early in the data this works. When the
string is late in the data -- past 1000 rows -- the offset function
returns nothing.

Does anybody know why this is??

Here is the formula:

=COUNT(OFFSET(MGCSales,MATCH($F$5,Source!$A$3:$A$3 3412,0),8,MATCH($F
$5,Source!$A$3:$A$33412,1)-MATCH($F$5,Source!$A$3:$A$33412,0),1))

Thanks in advance for your help.

A

OFFSET problem
 

Venturing some thoughts ..

Think the prob lies in this term:
MATCH($F$5,source!$A$3:$A$33412,1)
which may not always return the "last" row

Think we could try replacing the above with this expression (it requires
array-entry):
MATCH(MAX(IF($F$5=source!$A$3:$A$33412,ROW($A$3:$A $33412))),IF($F$5=source!$A$3:$A$33412,ROW($A$3:$A $33412)),0)

Try, array-entered, ie press CTRL+SHIFT+ENTER to confirm the formula:
=COUNT(OFFSET(MGCSales,MATCH($F$5,source!$A$3:$A$3 3412,0),8,MATCH(MAX(IF($F$5=source!$A$3:$A$33412,R OW($A$3:$A$33412))),IF($F$5=source!$A$3:$A$33412,R OW($A$3:$A$33412)),0)-MATCH($F$5,source!$A$3:$A$33412,0),1))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:362 Subscribers:64
xdemechanik
---
"alan82" wrote in message
...
Hi,

I have a problem when using the offset function. I am using the Match
function to find the first and last occurences of a string in a series
of data. When the string is early in the data this works. When the
string is late in the data -- past 1000 rows -- the offset function
returns nothing.

Does anybody know why this is??

Here is the formula:

=COUNT(OFFSET(MGCSales,MATCH($F$5,Source!$A$3:$A$3 3412,0),8,MATCH($F
$5,Source!$A$3:$A$33412,1)-MATCH($F$5,Source!$A$3:$A$33412,0),1))

Thanks in advance for your help.

A

OFFSET problem
 

Alan,

No feedback for the thoughts ventured?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:362 Subscribers:64
xdemechanik
---

OFFSET problem
 

alan82 wrote...
I have a problem when using the offset function. I am using the Match
function to find the first and last occurences of a string in a series
of data. When the string is early in the data this works. When the
string is late in the data -- past 1000 rows -- the offset function
returns nothing.

Does anybody know why this is??
....
=COUNT(OFFSET(MGCSales,MATCH($F$5,Source!$A$3:$A$ 33412,0),8,
MATCH($F$5,Source!$A$3:$A$33412,1)-MATCH($F$5,Source!$A$3:$A$33412,0),1))

Is Source!$A$3:$A$33412 sorted in ascending order? If not, MATCH($F
$5,Source!$A$3:$A$33412,1) is unreliable. That's the most likely
reason your formula fails.

Also, your 2nd argument to your OFFSET call gives the row index
(starting from 1) in Source!$A$3:$A$33412 of the first instance of the
value of F5. Unless MGCSales has a header row as its top row that you
want to skip, you should be subtracting 1 from the first MATCH call's
result.

You could avoid the volatile OFFSET call and make the formula clearer
using two INDEX calls. Defining the name LIST referring to Source!$A
$3:$A$33412,

=COUNT(INDEX(MGCSales,MATCH($F$5,LIST,0),9):INDEX( MGCSales,MATCH(2,1/
(LIST=$F$5)),9))

OFFSET problem
 

"Max" wrote...
No feedback for the thoughts ventured?

You need to learn how to read NNTP header tags. Had you done so, you
might have noticed that the OP posted a bit after 9 AM GMT from the
British time zone. You posted your first response around 4:40 AM (19
Nov) from Singapore's time zone, which would be around 8:40 PM (18
Nov) GMT. In the OP's local time, if the OP posted from work in the
morning then left work even as late as 8 PM (so 40 minutes BEFORE you
posted your 1st response), there would have been NOTHING to which the
OP could sensibly respond/for which to provide feedback.

You probably need to wait until 5 PM your time (9 AM GMT) for the OP
to have a chance to see your first response. Right now (as I write
this, 3 AM GMT 19 Nov) the OP is either asleep or has much, much
better things to do than check newsgroups.

Patience!