I have an array that starts at 9am (9) and goes to 4pm (16) based on 24 hour time format. Depending on the current time (I query Excel for the current hour) I want to look back 1,2,3,4 hours etc. Obviously if it's 4pm (16) then going back is easy, but if it is 9am I want to go to the 4pm, 3pm,2pm etc. from the previous day (higher value in the array 16,15,14 etc.). Array is set up like this:
9 100
10 110
11 50
12 200
13 250
14 10
15 150
16 130
For example, if I want it to look back 2 hours, at 4pm (16) minus 2pm (14) would be 130-10=120. For 9am minus 3pm the previous day (15) would be 100-150=-50.
There's probably some logic with difference or algebra that I'm not getting. I've thought about index() but that could get tricky. Any help would be appreciated.
CodePudding user response：
I've had to make some assumptions about your question. I assume that by array, you mean a range of cells, say A1:B8, containing the values you listed. If that's the case then the task could be solved fairly trivially using the modulo operator (see 
then the formula in cell B3 would simply be:
=INDEX($B$6:$B$13,MOD($B$1-9-$B$2,8) 1)-INDEX($B$6:$B$13,MATCH($B$1,$A$6:$A$13,0))
One of the issues is that we don't know what or where your array is - it could be a range, a table, a named range, a hard-coded array, etc. Obviously, this answer will only apply to a range but you can adapt it for any other type of array. We also don't know what you've tried already, which is usually a requirement for a question before we answer it. That would have revealed what kind of array this is, for example.
You asked if it was a tough nut to crack? The answer is no, but the lack of details in your question sure has put a hard shell around that nut. And if you're after a really fast response, then you need to provide as much detail as you possibly can. It's probably true that this is the best board to post to for answers, but a good answer depends on a good question.