Need Help Nesting a Formula inside MAX formula

  • Thread starter Thread starter Terra S.
  • Start date Start date
T

Terra S.

I have Stock Market quotes that gets stored in Excel intra-day

When every 30 minutes passes, the data is automatically updated and
archived in the next row up and the new update begins.

EXAMPLE:

C2 9:30 D2 25
C3 10:00 D3 25.5
C4 10:30 D4 25.10
C5 11:00 D5 26

After 30 mins the data looks like this:

C2 10:00 D2 25.5
C3 10:30 D3 25.10
C4 11:00 D4 26
C5 11:30 D5 26.25

This process keeps going on.
I can archive as many periods I want, in any timeframe.
I currently store 100 rows of 30 minute periods.
Everytime a new peiod begins all the data is shifted up one row and
the very last period drops off.
Here's what i want to do and the problem I'm having


OBJECTIVE: I need to tell Excel to find the highest value in the data
begining from a certain time. i.e, I only want it to consider part of
the data starting from a fixed time. Since that fixed time gets
shifted to another cell wwhen the quotes update, the formula needs to
dynamically change the cell address in which to begin it's anaysis.

PROBLEM: I can't use the MAX formula because the table of data being
analyed for the highest value is constantly changing. i.e., I want
Excel to start in cell D32 and go down to D131 and tell me the highest
value. But after 30 minutes goes by, it now needs to look in cell D31
down to D131

I tried using =ADDRESS(VLOOKUP(J6,D32:F131,3,FALSE),4) inside the MAX
formula. This formula above gave me the cell address I wanted MAX to
start from and it changed dynamically when Excel moved the data to the
next row dowm. But when I tried nesting it inside like :
Max(ADDRESS(VLOOKUP(J6,D32:F131,3,FALSE),4):D131) it says i have an
error in the formula.

I dont understand why since the =ADDRESS(VLOOKUP part of the formula
returns the cell address if put into a seperate cell all by it's self.

I'm really stumped on this one. I dont understand why Excel lets me
write a formula that returns a cell address when i cant use the result
inside another formula.

QUESTION: Why cant I use a formula inside MAX that returns a cell
address when all that MAX wants is two cell addresses to look for the
highest value in an array?

Is there any way around this problem to look for the highest value in
an array that keeps changing where in Excel it's starts and ends?

By the way, the Ending value is always static since the data drops
off. I just need to dynamically update the begining cell address of
the data array I'm working with.


It seems as tho it shouldnt be this difficult yet I'm out of ideas.

Hope someone can help,

TIA,

Terra S.
 
Try creating a set of index cells across one axis, which contain just
bare numbers, and wont be affected by that column shifting function.
Then you can use these numbered cells for your index reference in the
various lookup functions.
 
I've already thought of that but that still doesn't give me the MAX
valule in the cloumn I'm looking for.

Please, Look at this formula again:

=ADDRESS(VLOOKUP(J6,D32:F131,3,FALSE),4)

Please dont get confused by the VLOOKUP function in that formula.
That part of the formula's only purpose was to return the row number
of the cell I wanted to have Excel begin the array in which to begin
looking for the MAX value.

Please remember the formula began with ADDRESS.

The ADDRESS function only need two variables, the row number and th
column number

This part of the formula:" VLOOKUP(J6,D32:F131,3,FALSE) " simply
returned the number 38.

Why? This is because the value in J6 was where I wanted to begin the
anaysis to find the highest (MAX) value in the data array after that.

The data array in which to look for higher (MAX) values is D32 : D131.

Once it found this value, VLOOKUP would go into column F, which was
the 3rd column in the data array, hence, the number 3 in the formula.

I simply enetered a numbesr in the cells of column F that matched the
row I was referencing. i.e., in F38 I had entered the number 38, in
F39 I had enetered the number 39

In this case the look up value was 15.96, the value in J6. This same
value was in D38 of the data array (D32 to D131)

So, the VLOOKUP part of the formula returned the number 38 and then
the number 4 in the formula, which is after the VLOOKUP section
returned the letter "D"

Formula result = $D$38

Perfect, exact;y what I want. I want to find the highest (MAX) value
from D38 to D131.
I don't want it looking in D32 to D37, yet.
And when the data shifts up one row because ot the continious updates
from my stock quotes, the formula result chages from D38 to D37. So
now the data array I want to check for the highest (MAX) value is in
D37 to D131

But I can't nest the ADDRESS function inside
the MAX function. EXCEL says there's an error.

Here's really what I'm asking....

Why does EXCEL find the MAX value from D38 : D131
But I can't have a formula nested inside the MAX function that returns
the same cell address "D38" to D131?

If the ADDRESS formula returns D38, and I use that inside the MAX
formula,. for the first part of the data array, and then after " : "
I eneter D131, isn't that the same thing as =MAX(D38 : D131) ?

in other words, what good is it to have a formula, such as "ADDRESS",
return a cell address if I can't use the result I get from it ?

Frustrating,

Appreciate any help I canget on this.

Thank you,

Terra S.
 
Try using the INDIRECT function.

Example: MAX(A3:A9) is the same as MAX(INDIRECT(ADDRESS(3;1)& ":" & ADDRESS(9;1)))

Hope this helps

Fabio Coatis
 
OK, I attempted to enter the formula you suggested below.

I'm assuming the formula you suggested should have all the " & " signs
removed.

I also removed all the quotation marks from the formula.

Also, I believe there should be commas between the 3 and 1 and commas
between the 9 and 1 instead of semi colons, no?

Anyway, I entered the formula into cell B2 exactly as follows:

=MAX(INDIRECT(ADDRESS(3,1):ADDRESS(9,1)))

I get the same result as I did before when I tried to nest ADDRESS and
VLOOKUP inside the MAX function.

A Microsoft Excel box pops up with a red X that reads " The formula
you typed contains an error."

I'm using Windows 98 and I have Excel 97

Obviously I'm doing something wrong or the formula syntax is
incorrect.

But you did exactly the same thing I did. You attempted to nest
ADDRESS and INDIRECT inside the MAX function, just as I attempted to
nest ADDRESS and VLOOKUP inside MAX.

Both formulas give me the error message.

I don't understand why I'm getting a message saying the formula
contains an error.

Please, try entering the formula you typed into Excel and see if you
get an error message too.

If you can successfully enter the formula, then please paste it here
exactly as you have it entered so I can see what I'm doing wrong.

Thank you for all your help

Anyone else care to weigh in on why I keep getting error messages?


Terra S.
 
Dont remove the " & ". Just change the semi colons for commas. My
regional setting use ; as arguments separator.

the formula should look like this:

=MAX(INDIRECT(ADDRESS(3,1) & ":" & ADDRESS(9,1)))

HTH
Fabio Coatis
 
THANK YOU so much !

That worked perfect. I nested the VLOOKUP function inside the first
ADDRESS function and replaced the second 9,1 with 131,4 in the second
ADDRESS function.

So now my formula looks like this:
=MAX(INDIRECT(ADDRESS(VLOOKUP(J6,D32:F131,3,FALSE),4) & ":" &
ADDRESS(131,4)))

This allows the first cell address, in this case D38, to be dynamic.
When my data updates and is shifted up one row, the ADDRESS VLOOKUP
follows it and then returns D37. I kept the second cell address static
( 131,4 ) is obviously D131.

I have to admit, I never sen or heard of using & ":" & in a formula
before.

I really appreciate all your help Fabio. Just for further reference
when I write formulas, could you explain the formula syntax to me?

Do the " & " signs in the formula mean the same thing as AND ?

Can I only use " & " in the INDIRECT function?

Why is there quotation marks around the colon? i.e., ":"

Thanks again for all the help,

Terra S.
 
You could probably do it faster using offset and match without involving vlookup and indirect.
Take a look at offset in help
 
Using the CONCATENATE function is the equivalent of using the "&" character
to assemble larger strings from smaller strings.

Use the INDIRECT function to find out the contents of a cell from its
reference.

When you pass ADDRESS(3,1) & ":" & ADDRESS(9,1) to the INDIRECT function you
are passing the string "$A$3:$A$9" to the INDIRECT function.
Try selecting just ADDRESS(3,1) & ":" & ADDRESS(9,1) in the formula bar and
click F9.

Best regards.
 
Back
Top