Highest high & lowest low

C

cranmax

New Member
Joined
Jul 25, 2016
Messages
16
Hello everybody,

The excel must obtain the max value of a list of values, the range of values where it looks change depending on a constant.

Exercise: a list of 30 random values in column A; on C1 a "constant" like 3, 5, 10, 15. On column B the MAX value of 3 (A1:A3), 5 (A1:A5), 10 (A1:A10) and 15 (A1:A15) previous values. So theoretically the "constant" 3 (looks from A3 to A1) must start at B3 but I prefer to start since the first value at A1.
The MIN, I suppose is the same but changing a little bit the formula.
Ask me any questions.

Thanks.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
cranmax said:
Hello everybody,

The excel must obtain the max value of a list of values, the range of values where it looks change depending on a constant.

Exercise: a list of 30 random values in column A; on C1 a "constant" like 3, 5, 10, 15. On column B the MAX value of 3 (A1:A3), 5 (A1:A5), 10 (A1:A10) and 15 (A1:A15) previous values. So theoretically the "constant" 3 (looks from A3 to A1) must start at B3 but I prefer to start since the first value at A1.
The MIN, I suppose is the same but changing a little bit the formula.
Ask me any questions.

Thanks.
Click to expand...

I've done this using an additional column that evaluates whether the number is to be included, then using the max min functions on that column. So if the random values are in column A, in column B it would either show or hide the value in A depending on whether it meets the criteria of the constant.
 
Upvote 0
=MAX(INDEX(A:A,1):INDEX(A:A,C1))

which gives the maximum of the computet range in column A. C1 tells the end of the range starting from the first cell.
 
Upvote 0
Aladin Akyurek said:
=MAX(INDEX(A:A,1):INDEX(A:A,C1))

which gives the maximum of the computet range in column A. C1 tells the end of the range starting from the first cell.
Click to expand...

Well, say C1 or "constant" is 5 then the formula outputs at B1 is looking forward, it looks for the five first values from A1:A5, I want that it looks just backwards so it have to start at A5 or to calculate the maximum of constant 1 at B1, 2,3,4 and 5 and then use just constant 5. So at B6 the formula with constant 5 looks A2:A6, at B7 looks A3:A7and gives the maximum, and compute the formula until the last value.
Hope this could clarify, thanks.
 
Upvote 0
cranmax said:
Well, say C1 or "constant" is 5 then the formula outputs at B1 is looking forward, it looks for the five first values from A1:A5, I want that it looks just backwards so it have to start at A5 or to calculate the maximum of constant 1 at B1, 2,3,4 and 5 and then use just constant 5. So at B6 the formula with constant 5 looks A2:A6, at B7 looks A3:A7and gives the maximum, and compute the formula until the last value.
Hope this could clarify, thanks.
Click to expand...

Would you care to post a small sample along with the desired results? Attachments lists a few methods for posting sample exhibits which do not require retyping and the add-in https://app.box.com/s/soezox25h3w0q5s4rcyl is also a method for posting an exhibit (Note that an image or a link to a storage site like dropbox or onedrive etc. does not provide a permanent record. Mind you a forum without a permanent record is not of much value.).
 
Upvote 0
Aladin Akyurek said:
Would you care to post a small sample along with the desired results? Attachments lists a few methods for posting sample exhibits which do not require retyping and the add-in https://app.box.com/s/soezox25h3w0q5s4rcyl is also a method for posting an exhibit (Note that an image or a link to a storage site like dropbox or onedrive etc. does not provide a permanent record. Mind you a forum without a permanent record is not of much value.).
Click to expand...

Sorry but I'm posting this from Internet mobile,I can't access to Internet now. I have explained it on the photo.

 
Upvote 0
I have used MrExcel HTML Maker developed by Richard Schollar.
Here is:

Book1
ABCDEFGHI
1RANDOM VALUESMAXCONSTANTRANDOM VALUESMAXCONSTANTTHIS ARE 2 EXAMPLES; WHAT I WANT IS A FORMULA TO PUT ON COLUMN "MAX" THAT COMPUTES
225210THE MAX OF THE N PREVIOUS VALUES, WHERE N IS THE CONSTANT.
366SO WHEN I CHANGE THE VALUE OF THE CONSTANT, IT USES OTHER RANGE OF VALUES.
400
599
6494
7292
8999
9898
10999
1110101010
12810810
13010010
14710710
15110110
1688810
1738310
1848410
1968610
2018110
212628
223638
235658
248888
258888
2610101010
27410410
28310310
29610610
30110110
3116110
Hoja1
Cell Formulas
RangeFormula
B6=MAX(A2:A6)
B7=MAX(A3:A7)
B8=MAX(A4:A8)
B9=MAX(A5:A9)
B10=MAX(A6:A10)
B11=MAX(A7:A11)
B12=MAX(A8:A12)
B13=MAX(A9:A13)
B14=MAX(A10:A14)
B15=MAX(A11:A15)
B16=MAX(A12:A16)
B17=MAX(A13:A17)
B18=MAX(A14:A18)
B19=MAX(A15:A19)
B20=MAX(A16:A20)
B21=MAX(A17:A21)
B22=MAX(A18:A22)
B23=MAX(A19:A23)
B24=MAX(A20:A24)
B25=MAX(A21:A25)
B26=MAX(A22:A26)
B27=MAX(A23:A27)
B28=MAX(A24:A28)
B29=MAX(A25:A29)
B30=MAX(A26:A30)
B31=MAX(A27:A31)
F11=MAX(E2:E11)
F12=MAX(E3:E12)
F13=MAX(E4:E13)
F14=MAX(E5:E14)
F15=MAX(E6:E15)
F16=MAX(E7:E16)
F17=MAX(E8:E17)
F18=MAX(E9:E18)
F19=MAX(E10:E19)
F20=MAX(E11:E20)
F21=MAX(E12:E21)
F22=MAX(E13:E22)
F23=MAX(E14:E23)
F24=MAX(E15:E24)
F25=MAX(E16:E25)
F26=MAX(E17:E26)
F27=MAX(E18:E27)
F28=MAX(E19:E28)
F29=MAX(E20:E29)
F30=MAX(E21:E30)
F31=MAX(E22:E31)
 
Upvote 0
[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[TD]
D​
[/TD]
[TD]
E​
[/TD]
[TD]
F​
[/TD]
[TD]
G​
[/TD]
[TD]
H​
[/TD]
[TD]
I​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD]RANDOM VALUES[/TD]
[TD]MAX[/TD]
[TD]CONSTANT[/TD]
[TD][/TD]
[TD]RANDOM VALUES[/TD]
[TD]MAX[/TD]
[TD]CONSTANT[/TD]
[TD][/TD]
[TD]THIS ARE 2 EXAMPLES; WHAT I WANT IS A FORMULA TO PUT ON COLUMN "MAX" THAT COMPUTES[/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD]
5​
[/TD]
[TD][/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD]
10​
[/TD]
[TD][/TD]
[TD]THE MAX OF THE N PREVIOUS VALUES, WHERE N IS THE CONSTANT.[/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]
6​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
6​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SO WHEN I CHANGE THE VALUE OF THE CONSTANT, IT USES OTHER RANGE OF VALUES.[/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]
0​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
0​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD]
9​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
9​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
6​
[/TD]
[TD]
4​
[/TD]
[TD]
=MAX(A2:A6)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
4​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
7​
[/TD]
[TD]
2​
[/TD]
[TD]
=MAX(A3:A7)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
8​
[/TD]
[TD]
9​
[/TD]
[TD]
=MAX(A4:A8)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
9​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
9​
[/TD]
[TD]
8​
[/TD]
[TD]
=MAX(A5:A9)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
8​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
10​
[/TD]
[TD]
9​
[/TD]
[TD]
=MAX(A6:A10)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
9​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
11​
[/TD]
[TD]
10​
[/TD]
[TD]
=MAX(A7:A11)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
10​
[/TD]
[TD]
=MAX(E2:E11)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
12​
[/TD]
[TD]
8​
[/TD]
[TD]
=MAX(A8:A12)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
8​
[/TD]
[TD]
=MAX(E3:E12)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
13​
[/TD]
[TD]
0​
[/TD]
[TD]
=MAX(A9:A13)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
0​
[/TD]
[TD]
=MAX(E4:E13)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
14​
[/TD]
[TD]
7​
[/TD]
[TD]
=MAX(A10:A14)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
7​
[/TD]
[TD]
=MAX(E5:E14)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
15​
[/TD]
[TD]
1​
[/TD]
[TD]
=MAX(A11:A15)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD]
=MAX(E6:E15)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
16​
[/TD]
[TD]
8​
[/TD]
[TD]
=MAX(A12:A16)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
8​
[/TD]
[TD]
=MAX(E7:E16)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
17​
[/TD]
[TD]
3​
[/TD]
[TD]
=MAX(A13:A17)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
3​
[/TD]
[TD]
=MAX(E8:E17)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
18​
[/TD]
[TD]
4​
[/TD]
[TD]
=MAX(A14:A18)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
4​
[/TD]
[TD]
=MAX(E9:E18)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
19​
[/TD]
[TD]
6​
[/TD]
[TD]
=MAX(A15:A19)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
6​
[/TD]
[TD]
=MAX(E10:E19)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
20​
[/TD]
[TD]
1​
[/TD]
[TD]
=MAX(A16:A20)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD]
=MAX(E11:E20)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
21​
[/TD]
[TD]
2​
[/TD]
[TD]
=MAX(A17:A21)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
2​
[/TD]
[TD]
=MAX(E12:E21)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
22​
[/TD]
[TD]
3​
[/TD]
[TD]
=MAX(A18:A22)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
3​
[/TD]
[TD]
=MAX(E13:E22)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
23​
[/TD]
[TD]
5​
[/TD]
[TD]
=MAX(A19:A23)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
5​
[/TD]
[TD]
=MAX(E14:E23)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
24​
[/TD]
[TD]
8​
[/TD]
[TD]
=MAX(A20:A24)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
8​
[/TD]
[TD]
=MAX(E15:E24)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
25​
[/TD]
[TD]
8​
[/TD]
[TD]
=MAX(A21:A25)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
8​
[/TD]
[TD]
=MAX(E16:E25)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
26​
[/TD]
[TD]
10​
[/TD]
[TD]
=MAX(A22:A26)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
10​
[/TD]
[TD]
=MAX(E17:E26)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
27​
[/TD]
[TD]
4​
[/TD]
[TD]
=MAX(A23:A27)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
4​
[/TD]
[TD]
=MAX(E18:E27)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
28​
[/TD]
[TD]
3​
[/TD]
[TD]
=MAX(A24:A28)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
3​
[/TD]
[TD]
=MAX(E19:E28)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
29​
[/TD]
[TD]
6​
[/TD]
[TD]
=MAX(A25:A29)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
6​
[/TD]
[TD]
=MAX(E20:E29)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
30​
[/TD]
[TD]
1​
[/TD]
[TD]
=MAX(A26:A30)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD]
=MAX(E21:E30)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
31​
[/TD]
[TD]
1​
[/TD]
[TD]
=MAX(A27:A31)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD]
=MAX(E22:E31)​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This also seems to be an image but the idea is on there.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Highlighting cells based on criteria
Replies
0
Views
418
rahildhody
R
A
  • Question Question
Sorting from the lowest to highest value
Replies
1
Views
248
dermie_72
D
H
  • Question Question
Vba to fill HIGHEST & LOWEST values
Replies
4
Views
473
hsandeep
H
I
  • Question Question
Find the second highest attribute based on criteria. I know its a combo of index(match(large, but I just can't get it to work correctly.
Replies
1
Views
568
StephenCrump
S
M
  • Solved
Adding a NR to a formula & changing an IF to an IFS statement.
Replies
3
Views
293
Marvo
M

Forum statistics

Threads
1,223,888
Messages
6,175,212
Members
452,618
Latest member
Tam84

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top