Thanks to Richard Buttrey, just one more question [how does this complex formula work?]

[justdave7]

Hello again, everyone

In January, Richard solved a problem for me. My question was this:

Using web query i have a list of the last 400 days of dow closing numbers. I know how to find the highest closing and place result in a cell, but I need to find the lowest dow close SINCE the highest close occured using vba and place that number in a cell. I have been trying for several days to find the answer, but I now need help. Anyone?

Richard provided me with this formula which, after adapting it to my purpose worked just fine:

=MIN(OFFSET(A1,MATCH(MAX(A1:A400),A1:A400,FALSE),0,COUNTA(A:A)-MATCH(MAX(A1:A400),A1:A400,FALSE),1))

The reason for my post today is this...

It's easy to copy and paste a formula that is provided by an expert, but that is not always helping a person to learn Excel VBA. Although the formula works, I keep looking at it trying to dissect it so as to be able to fully understand it. My brain feels like it might explode as I try to analyze it piece by piece.

Could someone (perhaps Richard) please take a few minutes to explain how this formula works. I WANT TO LEARN !

Justdave

Moderator's note: Please reference the following thread: https://www.excelforum.com/excel-pro...e-highest.html --6StringJazzer

[benishiryo]

hi justdave7. took more than a few minutes, but hope this helps. i didnt use 400 cells to illustrate but just 20 cells so that it is clearer. just to clarify:
1) this is not VBA. it is just a formula
2) it does not need to be an array formula too. simply press ENTER to confirm the formula

Richard's formula
=MIN(OFFSET(A1,MATCH(MAX(A1:A400),A1:A400,FALSE),0,COUNTA(A:A)-MATCH(MAX(A1:A400),A1:A400,FALSE),1))

Say Max number in in A5. So you need to find Min between range of A6:A20 (based on 20 cells like i mentioned)

So how do you get the range of A6:A20 instead of A1:A20
OFFSET formula
=OFFSET(A1,5,0,15,1)
What does this formula above mean? Start from cell A1

=OFFSET(A1,5,0,15,1)
Move 5 rows down from cell A1. That means you are now at cell A6

=OFFSET(A1,5,0,15,1)
Move 0 columns to the right of cell A6. That means you still remain at column A.

=OFFSET(A1,5,0,15,1)
Range up to 15 rows from cell A6. That means you are ranging A6:A20

=OFFSET(A1,5,0,15,1)
Range up to 1 column from cell A6:A20. That means you are still ranging A6:A20

But how to make those numbers in red dynamic that finds the Max number no matter where it is?
To know which row the Max number is, Richard used MATCH(MAX(A1:A400),A1:A400,FALSE)
You are familiar with how MAX works. It gives you the max number in the range

MATCH determines the row number of where the MAX number is. In row 5. the FALSE in the argument simply means you want Excel to find an exact match. You can use 0 too.
=MATCH(MAX(A1:A400),A1:A400,FALSE)

How do you know how many rows to range from the Max number, after finding it?
Richard used COUNTA(A:A)-MATCH(MAX(A1:A400),A1:A400,FALSE)
COUNTA counts how many cells in the range are not empty. It returns 20. 20 cells are not empty.
=COUNTA(A:A)

He then uses the same formula to find the row number of where the Max number is
=MATCH(MAX(A1:A400),A1:A400,FALSE)

If you take the total cells which are not empty MINUS where the Max number is, you get the number of cells you need to range up.

[justdave7]

benishiryo

Thank you so much for taking the time to explain this formula. Putting it in the form of an .xlsx file was a much appreciated bonus.
I love this forum!!

Thanks again

justdave

[alansidman]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)