CODEWITHSUNDEEP
exceldateexcel-formula
Siva
Siva

Reputation: 302

Compare today's data with same day/time but previous week's data

For the given sample data, I need a formula to find the difference between E2-E7 which is comparing today's hourly data with exact time of the previous week.

P.S. 3/27 11.00 AM has to be compared with 3/20 11.00 AM not with 11.00 PM.

       C         D         E       F      G       H
Row-1 Date  Time           X1      X2     X3      X4
Row-2 3/27  11:00 AM    760197  408260  359812  77.55%
Row-3 3/27  10:00 AM    758371  369609  326323  78.00%
Row-4 3/27   9:00 AM    756123  313521  278248  78.31%
Row-5 3/27   8:00 AM    753097  232591  206849  78.26%
Row-6 3/21  11:00 PM    760197  408260  359812  77.55%
Row-7 3/21  11:00 AM    760197  408260  359812  77.55%

Upvotes: 2

Views: 3189

Answers (2)

MrSimpleMind
MrSimpleMind

Reputation: 8587

This is an illustration of a solution, and could for sure be simplified.

But maybe you can use it...

enter image description here

As you see, the formulas used are:

=C2&D2  
=C2-DAY(7)   
=J2&D2  
=MATCH(K2,I:I,0)    
=E2-INDIRECT("E"&L2)

Hope it helps.

Upvotes: 0

pnuts
pnuts

Reputation: 59440

Combine Date and Time (say in ColumnA) and in Row2 use:

=IFERROR(E2-VLOOKUP(A2-7,A:E,5,FALSE),"")

Copy down as required.

Upvotes: 2

Related Questions