Two complex formulas returning incorrect values

Ironman

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
1,069
Office Version
  1. 365
Platform
  1. 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.


Book1
ABCD
113 MAR 2019MILES/TIME THIS WK (WK 11)292hr 00m
2DAYS GONE THIS YEARLAST WK/YR/ROLL 365443hr 00m
372YEAR TO DATE/YEARS2019 TO DATESAME DATE 2018
4MAX EXERCISE BIKE DAYS LEFTCUMULATIVE MILES454277
5293CUMULATIVE HOURS3016
643,534.00435270.043520.0
7Furthest Distance (Miles Per Session) 2019 To Date ?Sat 9 Feb
8Greatest Power Output (Watts Per Session) 2019 To Date ?Sat 9 Feb
983
10DATETIME (h:mm)DIST. (km)DIST. (mls)
11Sat, 6 Jan 20180:3315.29.4
12Wed, 10 Jan 20180:4321.213.2
13Tue, 16 Jan 20180:4821.213.2
14Fri, 19 Jan 20180:5222.213.8
15Tue, 23 Jan 20180:5326.316.3
16Thu, 25 Jan 20180:4621.313.2
17Sat, 27 Jan 20180:4520.012.4
18Sun, 28 Jan 20180:4620.712.9
19Wed, 31 Jan 20180:5023.614.7
20Wed, 14 Feb 20180:4521.013.0
21Thu, 22 Feb 20180:4520.913.0
22Sun, 4 Mar 20180:4521.013.0
23Tue, 6 Mar 20180:4822.013.7
24Sat, 10 Mar 20180:4720.812.9
25Sat, 17 Mar 20181:0125.816.0
26Thu, 12 Apr 20180:4519.312.0
27Mon, 23 Jul 20180:3011.87.3
28Sat, 11 Aug 20180:3013.48.3
29Tue, 25 Sep 20180:3012.47.7
30Wed, 26 Sep 20180:4014.69.1
31Wed, 3 Oct 20180:3012.07.5
32Mon, 8 Oct 20180:3010.96.8
33Sun, 14 Oct 20180:4015.29.4
34Thu, 18 Oct 20180:3512.87.9
35Sun, 21 Oct 20181:0023.214.4
36Wed, 24 Oct 20180:3512.27.6
37Sat, 27 Oct 20180:4518.011.2
38Tue, 30 Oct 20181:0024.515.2
39Fri, 2 Nov 20181:0024.115.0
40Mon, 5 Nov 20181:0024.815.4
41Wed, 7 Nov 20180:4519.111.9
42Fri, 9 Nov 20181:0025.215.6
43Sun, 11 Nov 20181:0026.916.7
44Wed, 14 Nov 20181:0026.416.4
45Mon, 19 Nov 20180:4518.711.6
46Wed, 21 Nov 20181:0026.216.3
47Fri, 23 Nov 20181:0022.814.2
48Sun, 25 Nov 20181:0023.114.3
49Wed, 28 Nov 20181:0223.914.8
50Sat, 1 Dec 20181:0023.614.7
51Tue, 4 Dec 20181:0023.814.8
52Thu, 6 Dec 20181:0022.714.1
53Sat, 8 Dec 20181:0023.414.5
54Mon, 10 Dec 20181:0025.615.9
55Wed, 12 Dec 20181:0024.215.0
56Fri, 14 Dec 20181:0023.314.5
57Mon, 17 Dec 20181:0025.315.7
58Wed, 19 Dec 20181:0024.415.2
59Fri, 21 Dec 20181:0021.513.4
60Sun, 23 Dec 20181:0024.014.9
61Wed, 26 Dec 20181:0023.714.7
62Fri, 28 Dec 20181:0024.915.5
63Sun, 30 Dec 20181:0023.114.3
64Wed, 2 Jan 20191:0021.313.2
65Fri, 4 Jan 20191:0023.614.7
66Sun, 6 Jan 20191:0024.915.5
67Tue, 8 Jan 20191:0023.014.3
68Thu, 10 Jan 20191:0026.716.6
69Sat, 12 Jan 20191:0023.514.6
70Mon, 14 Jan 20191:0025.015.5
71Wed, 16 Jan 20191:0024.615.3
72Fri, 18 Jan 20191:0024.415.2
73Sun, 20 Jan 20191:0024.915.5
74Tue, 22 Jan 20191:0024.815.4
75Thu, 24 Jan 20191:0022.313.8
76Fri, 1 Feb 20191:0026.116.2
77Sun, 3 Feb 20191:0024.215.0
78Tue, 5 Feb 20191:0024.315.1
79Thu, 7 Feb 20191:0026.616.5
80Sat, 9 Feb 20191:0027.917.3
81Mon, 11 Feb 20191:0026.916.7
82Wed, 13 Feb 20191:0022.914.2
83Thu, 21 Feb 20191:0025.816.0
84Sat, 23 Feb 20191:0021.613.4
85Mon, 25 Feb 20191:0025.115.6
86Wed, 27 Feb 20191:0026.216.3
87Fri, 1 Mar 20191:0022.113.7
88Sun, 3 Mar 20191:0023.314.5
89Tue, 5 Mar 20191:0026.316.3
90Thu, 7 Mar 20191:0022.814.2
91Sat, 9 Mar 20191:0022.514.0
92Mon, 11 Mar 20191:0023.714.7
93Wed, 13 Mar 20191:0023.714.7
Exercise Bike
Cell Formulas
RangeFormula
A3=VBADaysGone
A5=VBADaysLeft
A6=A1-WEEKDAY(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=A6-7
B1="MILES/TIME THIS WK " & "(WK " & INT((A1-DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3)+WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3))+5)/7) & ")"
B9=COUNTA(OFFSET(A11, 0, 0, A1-36684))
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=B6-7
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
NameRefers ToCells
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(LogYear-1,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(LogYear-1,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(LogYear-1,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(LogYear-1,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)
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))=LogYear-1,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))=LogYear-1,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(LogYear-2,12,31+'Exercise Bike'!$A$5),All_Bike_Dates,1)

LogYear
='Training Log'!$C$4 (Cell value is 2019)
 
Last edited:
Sort by date Sort by votes
You could also use named ranges to make your formulas dynamic but you will need to clear the cells above each column due to circular reference.

ie the SUMIFS formula resides in D4 and the named formula gets confused when it tries to return your miles range
 
Upvote 0

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Brilliant, it works without the helper column - I just needed to format E5 as a number.

Thanks ever so much again for all your help RasGhul - have a really good day!

P.S. Sorry, I've just read your above post, which, as it turns out, isn't needed.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

skyh3ck
  • Question Question
1st match column header, 2nd lookup a value in same column, 3rd return corresponding value from the very first column ?? need help
Replies
2
Views
546
skyh3ck
skyh3ck
E
  • Solved
Formula not working
Replies
2
Views
316
esmarques
E
V
  • Question Question
Solve #VALUE errors of average formula, when the analyzed cell contains strings of length 0
Replies
1
Views
417
kvsrinivasamurthy
K
M
  • Question Question
suggest excel formula to calculate cumulative interest on no of days etc ......
Replies
1
Views
648
Peter_SSs
Peter_SSs
J
  • Solved
Convert the first negative value to months and 0 to subsequent negative value, else convert the next negative value to months
Replies
3
Views
550
jns1087
J

Forum statistics

Threads
1,223,894
Messages
6,175,250
Members
452,623
Latest member
Techenthusiast

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top