Ironman
Wellknown Member
 Joined
 Jan 31, 2004
 Messages
 1,038
 Office Version

 365
 Platform

 Windows
Hi
I'm having trouble returning the correct values in the 2 cells shaded red in the below sheet.
My objective is to show the ongoing corresponding number of miles cycled up to the same date in the previous year. The correct value for the current YTD total is the sum of cells C64:C93, which is 454 and is shown in cell C4.
The corresponding value for 2018 should be the sum of D11:D23 but it's incorrectly returning the sum of D11:D34 (277) in D4.
Similarly, the current YTD total hours is the sum of cells B64:B93 (exactly 30 hours) and is shown in cell C5. The corresponding value for 2018 should be 10 hours 46 mins (rounded down or up if <>30 mins) but also sums a similar incorrect range of cells B11:B34 (16 hours 57 mins, shortened to 16  this should be rounded up to 17 but it isn't  help would be appreciated here as well).
I would be very grateful for a solution  thanks in advance.
Although there are several formulas shown above, the most relevant formulas are as follows:
Last ytd_bike_miles
INDEX(All_Bike_Miles,BikeLastYearTopIndex):INDEX(All_Bike_Miles,BikeLastYTDBottomIndex)
All_Bike_Miles
='Exercise Bike'!$D$11:INDEX('Exercise Bike'!$D$11:$D$9997,COUNTA('Exercise Bike'!$A$11:$A$9997))
BikeLastYearTopIndex
=IF(YEAR(INDEX(All_Bike_Dates,BikeLastYearTopMatch+1))=LogYear1,BikeLastYearTopMatch+1,NA())
All_Bike_Miles
='Exercise Bike'!$D$11:INDEX('Exercise Bike'!$D$11:$D$9997,COUNTA('Exercise Bike'!$A$11:$A$9997))
BikeLastYTDBottomIndex
=IF(YEAR(INDEX(All_Bike_Dates,BikeLastYTDBottomMatch))=LogYear1,BikeLastYTDBottomMatch,NA())
All_Bike_Dates
='Exercise Bike'!$A$11:INDEX('Exercise Bike'!$A$11:$A$9997,COUNTA('Exercise Bike'!$A$11:$A$9997))
BikeLastYTDBottomMatch
=MATCH(DATE(LogYear2,12,31+'Exercise Bike'!$A$5),All_Bike_Dates,1)
LogYear
='Training Log'!$C$4 (Cell value is 2019)
I'm having trouble returning the correct values in the 2 cells shaded red in the below sheet.
My objective is to show the ongoing corresponding number of miles cycled up to the same date in the previous year. The correct value for the current YTD total is the sum of cells C64:C93, which is 454 and is shown in cell C4.
The corresponding value for 2018 should be the sum of D11:D23 but it's incorrectly returning the sum of D11:D34 (277) in D4.
Similarly, the current YTD total hours is the sum of cells B64:B93 (exactly 30 hours) and is shown in cell C5. The corresponding value for 2018 should be 10 hours 46 mins (rounded down or up if <>30 mins) but also sums a similar incorrect range of cells B11:B34 (16 hours 57 mins, shortened to 16  this should be rounded up to 17 but it isn't  help would be appreciated here as well).
I would be very grateful for a solution  thanks in advance.
Book1  

A  B  C  D  
1  13 MAR 2019  MILES/TIME THIS WK (WK 11)  29  2hr 00m  
2  DAYS GONE THIS YEAR  LAST WK/YR/ROLL 365  44  3hr 00m  
3  72  YEAR TO DATE/YEARS  2019 TO DATE  SAME DATE 2018  
4  MAX EXERCISE BIKE DAYS LEFT  CUMULATIVE MILES  454  277  
5  293  CUMULATIVE HOURS  30  16  
6  43,534.00  43527  0.0  43520.0  
7  Furthest Distance (Miles Per Session) 2019 To Date ?  Sat 9 Feb  
8  Greatest Power Output (Watts Per Session) 2019 To Date ?  Sat 9 Feb  
9  83  
10  DATE  TIME (h:mm)  DIST. (km)  DIST. (mls)  
11  Sat, 6 Jan 2018  0:33  15.2  9.4  
12  Wed, 10 Jan 2018  0:43  21.2  13.2  
13  Tue, 16 Jan 2018  0:48  21.2  13.2  
14  Fri, 19 Jan 2018  0:52  22.2  13.8  
15  Tue, 23 Jan 2018  0:53  26.3  16.3  
16  Thu, 25 Jan 2018  0:46  21.3  13.2  
17  Sat, 27 Jan 2018  0:45  20.0  12.4  
18  Sun, 28 Jan 2018  0:46  20.7  12.9  
19  Wed, 31 Jan 2018  0:50  23.6  14.7  
20  Wed, 14 Feb 2018  0:45  21.0  13.0  
21  Thu, 22 Feb 2018  0:45  20.9  13.0  
22  Sun, 4 Mar 2018  0:45  21.0  13.0  
23  Tue, 6 Mar 2018  0:48  22.0  13.7  
24  Sat, 10 Mar 2018  0:47  20.8  12.9  
25  Sat, 17 Mar 2018  1:01  25.8  16.0  
26  Thu, 12 Apr 2018  0:45  19.3  12.0  
27  Mon, 23 Jul 2018  0:30  11.8  7.3  
28  Sat, 11 Aug 2018  0:30  13.4  8.3  
29  Tue, 25 Sep 2018  0:30  12.4  7.7  
30  Wed, 26 Sep 2018  0:40  14.6  9.1  
31  Wed, 3 Oct 2018  0:30  12.0  7.5  
32  Mon, 8 Oct 2018  0:30  10.9  6.8  
33  Sun, 14 Oct 2018  0:40  15.2  9.4  
34  Thu, 18 Oct 2018  0:35  12.8  7.9  
35  Sun, 21 Oct 2018  1:00  23.2  14.4  
36  Wed, 24 Oct 2018  0:35  12.2  7.6  
37  Sat, 27 Oct 2018  0:45  18.0  11.2  
38  Tue, 30 Oct 2018  1:00  24.5  15.2  
39  Fri, 2 Nov 2018  1:00  24.1  15.0  
40  Mon, 5 Nov 2018  1:00  24.8  15.4  
41  Wed, 7 Nov 2018  0:45  19.1  11.9  
42  Fri, 9 Nov 2018  1:00  25.2  15.6  
43  Sun, 11 Nov 2018  1:00  26.9  16.7  
44  Wed, 14 Nov 2018  1:00  26.4  16.4  
45  Mon, 19 Nov 2018  0:45  18.7  11.6  
46  Wed, 21 Nov 2018  1:00  26.2  16.3  
47  Fri, 23 Nov 2018  1:00  22.8  14.2  
48  Sun, 25 Nov 2018  1:00  23.1  14.3  
49  Wed, 28 Nov 2018  1:02  23.9  14.8  
50  Sat, 1 Dec 2018  1:00  23.6  14.7  
51  Tue, 4 Dec 2018  1:00  23.8  14.8  
52  Thu, 6 Dec 2018  1:00  22.7  14.1  
53  Sat, 8 Dec 2018  1:00  23.4  14.5  
54  Mon, 10 Dec 2018  1:00  25.6  15.9  
55  Wed, 12 Dec 2018  1:00  24.2  15.0  
56  Fri, 14 Dec 2018  1:00  23.3  14.5  
57  Mon, 17 Dec 2018  1:00  25.3  15.7  
58  Wed, 19 Dec 2018  1:00  24.4  15.2  
59  Fri, 21 Dec 2018  1:00  21.5  13.4  
60  Sun, 23 Dec 2018  1:00  24.0  14.9  
61  Wed, 26 Dec 2018  1:00  23.7  14.7  
62  Fri, 28 Dec 2018  1:00  24.9  15.5  
63  Sun, 30 Dec 2018  1:00  23.1  14.3  
64  Wed, 2 Jan 2019  1:00  21.3  13.2  
65  Fri, 4 Jan 2019  1:00  23.6  14.7  
66  Sun, 6 Jan 2019  1:00  24.9  15.5  
67  Tue, 8 Jan 2019  1:00  23.0  14.3  
68  Thu, 10 Jan 2019  1:00  26.7  16.6  
69  Sat, 12 Jan 2019  1:00  23.5  14.6  
70  Mon, 14 Jan 2019  1:00  25.0  15.5  
71  Wed, 16 Jan 2019  1:00  24.6  15.3  
72  Fri, 18 Jan 2019  1:00  24.4  15.2  
73  Sun, 20 Jan 2019  1:00  24.9  15.5  
74  Tue, 22 Jan 2019  1:00  24.8  15.4  
75  Thu, 24 Jan 2019  1:00  22.3  13.8  
76  Fri, 1 Feb 2019  1:00  26.1  16.2  
77  Sun, 3 Feb 2019  1:00  24.2  15.0  
78  Tue, 5 Feb 2019  1:00  24.3  15.1  
79  Thu, 7 Feb 2019  1:00  26.6  16.5  
80  Sat, 9 Feb 2019  1:00  27.9  17.3  
81  Mon, 11 Feb 2019  1:00  26.9  16.7  
82  Wed, 13 Feb 2019  1:00  22.9  14.2  
83  Thu, 21 Feb 2019  1:00  25.8  16.0  
84  Sat, 23 Feb 2019  1:00  21.6  13.4  
85  Mon, 25 Feb 2019  1:00  25.1  15.6  
86  Wed, 27 Feb 2019  1:00  26.2  16.3  
87  Fri, 1 Mar 2019  1:00  22.1  13.7  
88  Sun, 3 Mar 2019  1:00  23.3  14.5  
89  Tue, 5 Mar 2019  1:00  26.3  16.3  
90  Thu, 7 Mar 2019  1:00  22.8  14.2  
91  Sat, 9 Mar 2019  1:00  22.5  14.0  
92  Mon, 11 Mar 2019  1:00  23.7  14.7  
93  Wed, 13 Mar 2019  1:00  23.7  14.7  
Exercise Bike 
Cell Formulas  

Range  Formula  
A3  =VBADaysGone  
A5  =VBADaysLeft  
A6  =A1WEEKDAY(A1,2)  
A7  ="Furthest Distance (Miles Per Session) "& YEAR(NOW())&" To Date" & " ►"  
A8  ="Greatest Power Output (Watts Per Session) "& YEAR(NOW())&" To Date" & " ►"  
A1  =UPPER(TEXT(TODAY(),"d mmm yyyy"))  
B6  =A67  
B1  ="MILES/TIME THIS WK " & "(WK " & INT((A1DATE(YEAR(A1WEEKDAY(A11)+4),1,3)+WEEKDAY(DATE(YEAR(A1WEEKDAY(A11)+4),1,3))+5)/7) & ")"  
B9  =COUNTA(OFFSET(A11, 0, 0, A136684))  
C1  =SUMIF(All_Bike_Dates,">"&A6,All_Bike_Miles)  
C2  =SUMIF(All_Bike_Dates,">"&B6,All_Bike_Miles)C1  
C3  =YEAR(NOW()) & " TO DATE"  
C4  =SUM(LogYear_Bike_Miles)  
C5  =SUM(LogYear_Bike_Times)  
D1  =SUMIF(All_Bike_Dates,">"&A6,All_Bike_Times)  
D2  =SUMIF(All_Bike_Dates,">"&B6,All_Bike_Times)D1  
D3  ="SAME DATE " & YEAR(NOW())1  
D4  =IFERROR(SUM(LastYTD_Bike_Miles),0)  
D5  =IFERROR(SUM(LastYTD_Bike_Times),0)  
D6  =B67  
D8  =INDEX(LogYear_Bike_Dates,MATCH(2,INDEX(1/(LogYear_Bike_Watts=E8),0)))  
D11  =IF(B11>0,C11*0.621,"")  
D12  =IF(B12>0,C12*0.621,"")  
D13  =IF(B13>0,C13*0.621,"")  
D14  =IF(B14>0,C14*0.621,"")  
D15  =IF(B15>0,C15*0.621,"")  
D16  =IF(B16>0,C16*0.621,"")  
D17  =IF(B17>0,C17*0.621,"")  
D18  =IF(B18>0,C18*0.621,"")  
D19  =IF(B19>0,C19*0.621,"")  
D20  =IF(B20>0,C20*0.621,"")  
D21  =IF(B21>0,C21*0.621,"")  
D22  =IF(B22>0,C22*0.621,"")  
D23  =IF(B23>0,C23*0.621,"")  
D24  =IF(B24>0,C24*0.621,"")  
D25  =IF(B25>0,C25*0.621,"")  
D26  =IF(B26>0,C26*0.621,"")  
D27  =IF(B27>0,C27*0.621,"")  
D28  =IF(B28>0,C28*0.621,"")  
D29  =IF(B29>0,C29*0.621,"")  
D30  =IF(B30>0,C30*0.621,"")  
D31  =IF(B31>0,C31*0.621,"")  
D32  =IF(B32>0,C32*0.621,"")  
D33  =IF(B33>0,C33*0.621,"")  
D34  =IF(B34>0,C34*0.621,"")  
D35  =IF(B35>0,C35*0.621,"")  
D36  =IF(B36>0,C36*0.621,"")  
D37  =IF(B37>0,C37*0.621,"")  
D38  =IF(B38>0,C38*0.621,"")  
D39  =IF(B39>0,C39*0.621,"")  
D40  =IF(B40>0,C40*0.621,"")  
D41  =IF(B41>0,C41*0.621,"")  
D42  =IF(B42>0,C42*0.621,"")  
D43  =IF(B43>0,C43*0.621,"")  
D44  =IF(B44>0,C44*0.621,"")  
D45  =IF(B45>0,C45*0.621,"")  
D46  =IF(B46>0,C46*0.621,"")  
D47  =IF(B47>0,C47*0.621,"")  
D48  =IF(B48>0,C48*0.621,"")  
D49  =IF(B49>0,C49*0.621,"")  
D50  =IF(B50>0,C50*0.621,"")  
D51  =IF(B51>0,C51*0.621,"")  
D52  =IF(B52>0,C52*0.621,"")  
D53  =IF(B53>0,C53*0.621,"")  
D54  =IF(B54>0,C54*0.621,"")  
D55  =IF(B55>0,C55*0.621,"")  
D56  =IF(B56>0,C56*0.621,"")  
D57  =IF(B57>0,C57*0.621,"")  
D58  =IF(B58>0,C58*0.621,"")  
D59  =IF(B59>0,C59*0.621,"")  
D60  =IF(B60>0,C60*0.621,"")  
D61  =IF(B61>0,C61*0.621,"")  
D62  =IF(B62>0,C62*0.621,"")  
D63  =IF(B63>0,C63*0.621,"")  
D64  =IF(B64>0,C64*0.621,"")  
D65  =IF(B65>0,C65*0.621,"")  
D66  =IF(B66>0,C66*0.621,"")  
D67  =IF(B67>0,C67*0.621,"")  
D68  =IF(B68>0,C68*0.621,"")  
D69  =IF(B69>0,C69*0.621,"")  
D70  =IF(B70>0,C70*0.621,"")  
D71  =IF(B71>0,C71*0.621,"")  
D72  =IF(B72>0,C72*0.621,"")  
D73  =IF(B73>0,C73*0.621,"")  
D74  =IF(B74>0,C74*0.621,"")  
D75  =IF(B75>0,C75*0.621,"")  
D76  =IF(B76>0,C76*0.621,"")  
D77  =IF(B77>0,C77*0.621,"")  
D78  =IF(B78>0,C78*0.621,"")  
D79  =IF(B79>0,C79*0.621,"")  
D80  =IF(B80>0,C80*0.621,"")  
D81  =IF(B81>0,C81*0.621,"")  
D82  =IF(B82>0,C82*0.621,"")  
D83  =IF(B83>0,C83*0.621,"")  
D84  =IF(B84>0,C84*0.621,"")  
D85  =IF(B85>0,C85*0.621,"")  
D86  =IF(B86>0,C86*0.621,"")  
D87  =IF(B87>0,C87*0.621,"")  
D88  =IF(B88>0,C88*0.621,"")  
D89  =IF(B89>0,C89*0.621,"")  
D90  =IF(B90>0,C90*0.621,"")  
D91  =IF(B91>0,C91*0.621,"")  
D92  =IF(B92>0,C92*0.621,"")  
D93  =IF(B93>0,C93*0.621,"")  
D7  {=INDEX(LogYear_Bike_Dates,MATCH(2,INDEX(1/(LogYear_Bike_Miles=E7),0)))}  
Press CTRL+SHIFT+ENTER to enter array formulas. 
Named Ranges  

Name  Refers To  Cells 
All_Bike_Dates  ='Exercise Bike'!$A$11:INDEX('Exercise Bike'!$A$11:$A$9997,COUNTA('Exercise Bike'!$A$11:$A$9997))  
All_Bike_Miles  ='Exercise Bike'!$D$11:INDEX('Exercise Bike'!$D$11:$D$9997,COUNTA('Exercise Bike'!$A$11:$A$9997))  
All_Bike_Times  ='Exercise Bike'!$B$11:INDEX('Exercise Bike'!$B$11:$B$9997,COUNTA('Exercise Bike'!$A$11:$A$9997))  
LastYTD_Bike_Miles  =INDEX(All_Bike_Miles,BikeLastYearTopIndex):INDEX(All_Bike_Miles,BikeLastYTDBottomIndex)  
LastYTD_Bike_Times  =INDEX(All_Bike_Times,BikeLastYearTopIndex):INDEX(All_Bike_Times,BikeLastYTDBottomIndex)  
LogYear  ='Training Log'!$C$4  
LogYear_Bike_Dates  =INDEX(All_Bike_Dates,IFERROR(MATCH(DATE(LogYear1,12,31),All_Bike_Dates,1),0)+1):INDEX(All_Bike_Dates,ROWS(All_Bike_Dates))  
LogYear_Bike_Miles  =INDEX(All_Bike_Miles,IFERROR(MATCH(DATE(LogYear1,12,31),All_Bike_Dates,1),0)+1):INDEX(All_Bike_Miles,ROWS(All_Bike_Dates))  
LogYear_Bike_Times  =INDEX(All_Bike_Times,IFERROR(MATCH(DATE(LogYear1,12,31),All_Bike_Dates,1),0)+1):INDEX(All_Bike_Times,ROWS(All_Bike_Dates))  
LogYear_Bike_Watts  =INDEX(All_Bike_Watts,IFERROR(MATCH(DATE(LogYear1,12,31),All_Bike_Dates,1),0)+1):INDEX(All_Bike_Watts,ROWS(All_Bike_Dates))  
VBADaysGone  ='Training Log'!$A$4  
VBADaysLeft  ='Training Log'!$A$6  
x  =OFFSET(LastLogDate,0,0,90) 
Last ytd_bike_miles
INDEX(All_Bike_Miles,BikeLastYearTopIndex):INDEX(All_Bike_Miles,BikeLastYTDBottomIndex)
All_Bike_Miles
='Exercise Bike'!$D$11:INDEX('Exercise Bike'!$D$11:$D$9997,COUNTA('Exercise Bike'!$A$11:$A$9997))
BikeLastYearTopIndex
=IF(YEAR(INDEX(All_Bike_Dates,BikeLastYearTopMatch+1))=LogYear1,BikeLastYearTopMatch+1,NA())
All_Bike_Miles
='Exercise Bike'!$D$11:INDEX('Exercise Bike'!$D$11:$D$9997,COUNTA('Exercise Bike'!$A$11:$A$9997))
BikeLastYTDBottomIndex
=IF(YEAR(INDEX(All_Bike_Dates,BikeLastYTDBottomMatch))=LogYear1,BikeLastYTDBottomMatch,NA())
All_Bike_Dates
='Exercise Bike'!$A$11:INDEX('Exercise Bike'!$A$11:$A$9997,COUNTA('Exercise Bike'!$A$11:$A$9997))
BikeLastYTDBottomMatch
=MATCH(DATE(LogYear2,12,31+'Exercise Bike'!$A$5),All_Bike_Dates,1)
LogYear
='Training Log'!$C$4 (Cell value is 2019)
Last edited: