calculate X days high and low based on a sequence of values

**excelman223** · 04-18-2024, 10:50 AM

How to loop through excel table to find X days high and X days low. (X here is a variable)

For example:

If today's value is >= yesterday's value than +1 till find today's value < value.

I don't think formula will work, because it will need to loop through each date to find the X variable.

thanks.

**alansidman** · 04-18-2024, 02:30 PM

I cannot replicate your answer with my code. Not sure how you arrived at those numbers.
For High, here is my code. For low, change the If then formula to be less vs more

Please Login or Register  to view this content.

Also, move your low to a different sheet or this will not work.

**CK76** · 04-18-2024, 03:24 PM

I believe your X days low for nameB is wrong. Should it not be 5? As 4/13/2024 is lower than 46.55.

At any rate, you could use formula.
For x days high. Copy down.

Please Login or Register  to view this content.

For x days low. Copy down.

Please Login or Register  to view this content.

**bebo021999** · 04-18-2024, 09:56 PM

B3, high:

Please Login or Register  to view this content.

copy to B11, low, change > with <

Please Login or Register  to view this content.

**CK76** · 04-19-2024, 07:52 AM

Right, in my pursuit of trying to use LAMBDA, REDUCE etc to initially solve the problem I had forgotten that I didn't need extra steps

**excelman223** · 04-20-2024, 03:49 AM

Thanks everyone for the help, really appreciated. Didn't know that Match could use this way.

calculate X days high and low based on a sequence of values

LinkBack

Thread Tools

Rate This Thread

Display

calculate X days high and low based on a sequence of values

Re: calculate X days high and low based on a sequence of values

Re: calculate X days high and low based on a sequence of values

Re: calculate X days high and low based on a sequence of values

Re: calculate X days high and low based on a sequence of values

Re: calculate X days high and low based on a sequence of values

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How to calculate task schedule based on 4-days work and 4-days OFF including weekends

Autofill dates based on a sequence of days

[SOLVED] Calculate N-Day Percentage Change and N-Day High/Low based on N value in separate cell?

[SOLVED] calculate total number of days between two days based on 365 days year

How to calculate number of sick days taken in a rolling 365 days period based on colour

Copy X Number of Rows Based on Checkbox, and Calculate High Point?

Excel 2007 : calculate value based on multiple low, medium, high cells

Bookmarks

Bookmarks

Posting Permissions