Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OFFSET problem
Alan,
No feedback for the thoughts ventured? -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:362 Subscribers:64 xdemechanik --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Offset Problem | Excel Discussion (Misc queries) | |||
OFFSET PROBLEM | Excel Discussion (Misc queries) | |||
Offset VBA Problem | Excel Discussion (Misc queries) | |||
Offset to different worksheet problem | Excel Worksheet Functions | |||
Offset Problem | New Users to Excel |