Login Page - Create Account

Support Board


Date/Time: Thu, 28 Mar 2024 15:51:27 +0000



[Programming Help] - Spreadsheet Programing - MAX function question

View Count: 952

[2021-11-29 19:22:38]
User183724 - Posts: 183
Spreadsheet MAX function question

Can MAX accept calculated ROW numbers? I have a calculated ROW values in columns BM3 and BN3

I'd like to get the MAX high value of C column (highs) using the values in BM3 and BN3

I've tried:

=MAX(C(BM3):C(BN3))

=MAX(INDIRECT(CONCATENATE("C",BM3)):INDIRECT(CONCATENATE("C",BN3)))

no luck... any assistance appreciated.
[2021-11-29 23:23:13]
Sawtooth - Posts: 3952
Have a look at this example, using the MAX(INDEX...MATCH...):INDEX(...MATCH...)) function combination:
https://www.sawtoothtrade.com/example-10.html

Here's another example. Look in cell S3 in the pic:
https://www.sawtoothtrade.com/free-stuff-3.html

You might also be able to use a modification of this example:
=MAX(OFFSET(C3,BM3-1,0):OFFSET(C3,BN3-1,0))
[2021-11-30 03:10:18]
User183724 - Posts: 183
I cant get any of those to work... I get a #REF! error like i do with everything else ive tried. either my software is broken or MAX wont accept it. thx anyway.
[2021-11-30 03:55:08]
Sawtooth - Posts: 3952
either my software is broken or MAX wont accept it.
What you want to do is possible. The examples prove it because they are doing the same thing.

So it's just a matter of giving MAX what it needs.

Whatever you're doing in BM3/BN3 might need to be rethought.
MATCH gives INDEX the row so it can return its value, which when used with MAX becomes one side of its range.
Repeat this for the other side of MAX's range.

The key here is getting MATCH to give the correct offsets to INDEX.
So start with just the MATCH and see if you can get it to give you what you need for INDEX.

As for the MAX(...OFFSET(... combination example, giving it BM3 and BN3 might not be what it needs either.
[2021-11-30 03:59:27]
User183724 - Posts: 183
I got this to do what I want but I'm not sure its what I was after... any port in a storm i guess...


=MAX(INDEX (A1:CN1002,(BW3-P3)+3,3):INDEX (A1:CN1002,(BW3-O3)+3,3))

where

a1:cn1002 is the entire sheet...could trim this down
BW3 is the bar# study...current bar value
P3 is a previously stored bar# of an event we want to keep
the subtracted difference gives the ROW we need
+3 is to position INDEX on cell 3 of the column
,3 is column C ... HIGH of the bar

repeat for O3

so, by storing the bar# and going though this mess, I got the overall result i was wanting... thx

To post a message in this thread, you need to log in with your Sierra Chart account:

Login

Login Page - Create Account