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:

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
There's a further formula I omitted, which is as follows:

BikeLastYearTopMatch
=IFERROR(MATCH(DATE(LogYear-2,12,31),All_Bike_Dates,1),0)

If there's a simpler formula for the 2 solutions than the ones above, then I'm more than willing to use that instead.

Thanks again!
 
Last edited:
Upvote 0
(posting error, please delete)
 
Last edited:
Upvote 0
Hi Ironman,

So when I copied and pasted your dates into my mock up file I had to convert them to real dates (Column B helper) just formatted the same as yours with custom formatting "ddd, d mmm yyyy" so the SUMIFS can work;


Book1
ABCDE
114 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 MILES454184.7
5293CUMULATIVE HOURS3010.77
6Furthest Distance (Miles Per Session) 2019 To Date ?Sat 9 Feb
7Greatest Power Output (Watts Per Session) 2019 To Date ?Sat 9 Feb
8DATETIME (h:mm)DIST. (km)DIST. (mls)
9Sat, 6 Jan 2018Sat, 6 Jan 20180:3315.29.4
10Wed, 10 Jan 2018Wed, 10 Jan 20180:4321.213.2
11Tue, 16 Jan 2018Tue, 16 Jan 20180:4821.213.2
12Fri, 19 Jan 2018Fri, 19 Jan 20180:5222.213.8
13Tue, 23 Jan 2018Tue, 23 Jan 20180:5326.316.3
14Thu, 25 Jan 2018Thu, 25 Jan 20180:4621.313.2
15Sat, 27 Jan 2018Sat, 27 Jan 20180:452012.4
16Sun, 28 Jan 2018Sun, 28 Jan 20180:4620.712.9
17Wed, 31 Jan 2018Wed, 31 Jan 20180:5023.614.7
18Wed, 14 Feb 2018Wed, 14 Feb 20180:452113
19Thu, 22 Feb 2018Thu, 22 Feb 20180:4520.913
20Sun, 4 Mar 2018Sun, 4 Mar 20180:452113
21Tue, 6 Mar 2018Tue, 6 Mar 20180:482213.7
22Sat, 10 Mar 2018Sat, 10 Mar 20180:4720.812.9
23Sat, 17 Mar 2018Sat, 17 Mar 20181:0125.816
Sheet1
Cell Formulas
RangeFormula
A1=UPPER(TEXT(TODAY(),"d mmm yyyy"))
E4=SUMIFS($E$9:$E$91,$B$9:$B$91,">="&MIN($A$9:$A$91),$B$9:$B$91,"<="&DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))
E5=ROUND(SUMIFS($C$9:$C$91,$B$9:$B$91,">="&MIN($A$9:$A$91),$B$9:$B$91,"<="&DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))*24,2)
B9=DATEVALUE(RIGHT(A9,LEN(A9)-SEARCH(",",A9)-1))
 
Upvote 0
Sorry the MIN($A$9:$A$91) should be looking at my helper Column B to get the oldest Date ​MIN($B$9:$B$91)
 
Upvote 0
Hi RasGhul my friend, great to hear from you again!

Looks like you've done it again for me - thank you so much!

If I might point out a couple of small issue please?

I need to emphasise that the sheet expands each time an entry is added, so the range you've defined down to Row 91 would need to be say 5,000. I guess all I'd need to do is change that or will it cause the calc to slow down?

Also, is there a way to automate the helper column entries (which I'll be hiding)?

Many thanks to you once again, you're brilliant!
 
Last edited:
Upvote 0
That's ok mate, well we can't use table function cause that disrupts your saved views from memory so just extend your help column down and amend it to;

=IF(A9="","",DATEVALUE(RIGHT(A9,LEN(A9)-SEARCH(",",A9)-1)))

Then this will only calculate when a value appears in the related row and keep calculating speeds down.

The sumifs formula is pretty quick so you could expand this out to 5000 or 10k rows no problem without noticeable calculation time. Doesn't hurt to test though for yourself...:cool:
 
Upvote 0
Many thanks RasGhul -

I've just tried out your first solution in my sheet without a helper column and reduced the column refs by 1 letter and increased the rows to 5,000.

Your E4 (my D4) still works perfectly - thanks once again.
However, your E5 (my D5) returns a value of 1795 and I can't work out why.

Here's my amended formula for D5:

=ROUND(SUMIFS($B$11:$B$5000,$A$11:$A$5000,">="&MIN($A$11:$A$5000),$B$11:$B$5000,"<="&DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))*24,2)

Does this calc definitely still need a helper column?

Incidentally, I gave up using Saved Views (my, you've got a great memory!) because they kept reducing the size of the normal sheet views - I've no idea why, so I don't use them any more.
 
Last edited:
Upvote 0
Agh, I've just worked out that 1795 is today's date in mmm dd format so I guess I'll need to use the helper column after all for this cell to work.
 
Last edited:
Upvote 0
When you are in Formula edit mode click on each range and ensure that they are looking at the correct column,(when you click on a range within a formula Excel normally highlights the target column as the same color from the formula;

Syntax;
=ROUND(SUMIFS(Times,HelperDates,">="&MIN(HelperDates),HelperDates,"<="&DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))*24,2)
 
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