Match Function - Assistance with Chart 1

[shelby55]

Hi

My life of late is trying to decipher a workbook designed by someone else who is not around to ask questions of! I'm working in Excel 2003.

So what does {MATCH(1,--(B9:B29<>""),0)} do?

B9 to B29 is a column of dates. The cell above is used in a named range field which I'm using in a graph (above is B5):

CODE
offset(Graph_Data!$B$9,Graph_Data!$B$5-1,0,Graph_Data!$B$6)

The problem is that the formula and graphs are a week behind. What I mean is that if the current date in the worksheet isn't a full week (so July 17) then there won't be a date for B29 on the graph_data worksheet (which makes sense - all the data being plotted is weekly). But if I select a date farther back, say July 11 which is the end of a week, then there is a date and data produced on row 29 for that date but because of the offset above of 1, it only includes B9 to B28.

So how do I indicate to include all when there are entries in B? Thanks.

 

[SkipVought]

MATCH(1,--(B9:B29<>""),0)

is entered as an ARRAY FORMULA, using SHIFT+ctr+ENTER.

As such it evaluates what offset has the first value.

So if B9 & B10 were empty and B11 had a value, the function would return 3, as B11 is the third cell in the range.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

I'm surprised you responded to this...I'm still tired from the last round!!

Thanks for the answer on MATCH but it's not working the way it should so I'm not sure how to change.

The data worksheet has 3 cells used in the named ranges that are used in the graphs:
range top: {MATCH(1,--(B9:B29<>""),0)}
range size: =if(count(B9:29)>20,20,Count(B9:B29))
range bottom: Match(9.999999999E+307,B9:B29)

There data rows for the chart are B9 to B29. As mentioned previously if the most recent timeframe isn't a full week then there is only data from B9 to B28 (when running using July 17) and therefore the last week of data showing will be July 11.

But if I select a timeframe from within the list that does have a full week of data i.e. I choose July 11, then B9 to B29 fills in with data but the named ranged shows B9 to B28 still which I don't want it to do.

Any ideas how to get around this? Thanks.

 

[SkipVought]

[tt]
range top: {MATCH(1,--(B9:B29<>""),0)}
range size: =if(count(B9:29)>20,20,Count(B9:B29))
range bottom: Match(9.999999999E+307,B9:B29)
[/tt]
Already explained the first function

The second is going to return either 20 or 21

The third returns the offset of the cell in B9:B29 containing the largest value, assuming that your data is collated in ascending order. I'd assume that that value would be 21 or 20.

So then your OFFSET function only returns B9:B28 and you think it ought to be B9:B29, what are the values of the three formulas you posted above? Hint: you can HIGHLIGHT a function like...
[tt]
[highlight]Match(9.999999999E+307,B9:B29)[/highlight]
[/tt]
and hit F9, and it will CALCULATE the highlighted expression.

[red]Hit the [ESC] key to reverse this process[/red]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Thanks very much. I did as you suggested and the answer was 21 when selected on July 11 and 20 when selected on July 17.

I'm just looking at this and I think it's the range size that isn't working. It shows as 20 whether B29 has data in it or not which explains why the July 11 graph isn't showing July 11.

So the named range in the graph is showing B9 to B28 whether there is data in B29 or not. How can I fix that?

Thanks!

 

[SkipVought]

[tt]
range size: =count(B9:29)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

That was way too easy. But I really appreciate your approach because you are teaching me what questions to ask to determine what I want and also to assess what the current code is doing.

You rock!!! Thanks again.

 

[SkipVought]

That makes me happy! I'd much rather teach someone how to fish than give them a trout.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

BTW, Excel Help Excel specifications and limits shows that the largest number that can be stored in Excel is
[tt]
Largest allowed positive number 9.99999999999999E+307
[/tt]
Hance the formula
[tt]
Match(9.999999999E+307,B9:B29)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Thanks Skip

Believe it or not I knew that from my many internet searches but thanks for following up!

However, I'm not sure of the logic of using it in this document in this way. It's for the cell of "range bottom" which is B7 but I don't see it referenced in any of the named ranges. I obviously won't delete it but it seems odd.

 

[SkipVought]

All you really need is the first two.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}