Diaphanosoma
asked on
Update a dynamic text box in excel chart automatically
Hi,
I have a chart in excel 2007 that a user updates almost daily. The chart gets updated appropriately using a named range that contains the offset function (=OFFSET('T'!$U$17,15,0,1,
Thanks,
Bruce
I have a chart in excel 2007 that a user updates almost daily. The chart gets updated appropriately using a named range that contains the offset function (=OFFSET('T'!$U$17,15,0,1,
Thanks,
Bruce
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'm happy to do so. There are actually several different formulas you could use.
E.g., find the last number in a column:
=INDEX(A:A,MATCH(99^99,A:A
find the last text in a column
=INDEX(A:A,MATCH(rept("z")
find the last value (number or text) could be a complicated version of the above, or we can use LOOKUP. Let me explain LOOKUP by talking about the match and lookup (including vlookup) functions in general:
1. They all THINK your data is sorted - this is important
2. MATCH/VLOOKUP have options for exact match, or NEXT HIGHEST value (re: the largest value being looked for that is less than or equal to what you're looking for).
3. The Lookup function also find either the match or next highest value.
So, in the case of Lookup(2,1/(A:A<>""),A:A) what is going on?
The first parameter is the search parameter,
The second parameter is what is being searched.
These two parameters act together to create an index which goes against the last parameter. Kind of how INDEX/MATCH works but all in one formula.
Let's talk about the 1/(A:A<>""). This returns an array of 1/(TRUE's and FALSEs) which returns an array of 1's and #DIV!0's. Note the maximum valid value of 1/(A:A<>"") is 1. You can combine this with other arrays to make multiple criteria work.
So, if we do a Lookup(2,1/(A:A<>""),A:A) we would get the LAST instance of a value that is non blank in column A. Why? Well, Lookup thinks the data is sorted, so it starts at the bottom of the range and works its way up, looking for the maximum value (because we're using 2 and 1 is the only valid value) until it finds it. As soon as it finds a valid value that is less than or equal to 2, it uses that. That's why it returns the last value.
Hope this helps.
Dave
E.g., find the last number in a column:
=INDEX(A:A,MATCH(99^99,A:A
find the last text in a column
=INDEX(A:A,MATCH(rept("z")
find the last value (number or text) could be a complicated version of the above, or we can use LOOKUP. Let me explain LOOKUP by talking about the match and lookup (including vlookup) functions in general:
1. They all THINK your data is sorted - this is important
2. MATCH/VLOOKUP have options for exact match, or NEXT HIGHEST value (re: the largest value being looked for that is less than or equal to what you're looking for).
3. The Lookup function also find either the match or next highest value.
So, in the case of Lookup(2,1/(A:A<>""),A:A) what is going on?
The first parameter is the search parameter,
The second parameter is what is being searched.
These two parameters act together to create an index which goes against the last parameter. Kind of how INDEX/MATCH works but all in one formula.
Let's talk about the 1/(A:A<>""). This returns an array of 1/(TRUE's and FALSEs) which returns an array of 1's and #DIV!0's. Note the maximum valid value of 1/(A:A<>"") is 1. You can combine this with other arrays to make multiple criteria work.
So, if we do a Lookup(2,1/(A:A<>""),A:A) we would get the LAST instance of a value that is non blank in column A. Why? Well, Lookup thinks the data is sorted, so it starts at the bottom of the range and works its way up, looking for the maximum value (because we're using 2 and 1 is the only valid value) until it finds it. As soon as it finds a valid value that is less than or equal to 2, it uses that. That's why it returns the last value.
Hope this helps.
Dave
PS - if you know your data is numeric, you can use INDEX/MATCH as above, or the Lookup:
=LOOKUP(99^99,A:A,A:A) 99^99 a very big number, so would find the next highest - thinking its sorted, would return the next valid number
and if you know your data is text, you can use:
=LOOKUP(REPT("z",20),A:A,A
Why did I use =Lookup(2,1/(A:A<>""),A:A)
As I didn't know if it was text or numeric, I just gave you a lookup that used criteria A:A<>"" so I KNEW the result would be a correct one.
Dave
=LOOKUP(99^99,A:A,A:A) 99^99 a very big number, so would find the next highest - thinking its sorted, would return the next valid number
and if you know your data is text, you can use:
=LOOKUP(REPT("z",20),A:A,A
Why did I use =Lookup(2,1/(A:A<>""),A:A)
As I didn't know if it was text or numeric, I just gave you a lookup that used criteria A:A<>"" so I KNEW the result would be a correct one.
Dave
>>"Well, Lookup thinks the data is sorted, so it starts at the bottom of the range and works its way up"
It's a binary search, I think you'll find, which is why it's much faster than exact matches.
It's a binary search, I think you'll find, which is why it's much faster than exact matches.
ASKER
Perfect solution and explaination.
Thanks so much.
Bruce
Thanks so much.
Bruce
>>It's a binary search, I think you'll find, which is why it's much faster than exact matches.
That's good to know, but the reason it finds the last cell (meeting the criteria of a very large number/text stream that doesn't exist in the data) is due to the fact it assumes the data is sorted
That's good to know, but the reason it finds the last cell (meeting the criteria of a very large number/text stream that doesn't exist in the data) is due to the fact it assumes the data is sorted
Correct - I wasn't disputing that part (otherwise a binary search wouldn't work for LOOKUP). :)
Got it!
Dave
Dave
ASKER
Why are you looking for a value of 2?
Why is there division in the lookup vector?
Better yet can you explain how and why the formula works?
Thanks,
Bruce