# Two complex formulas returning incorrect values

#### Ironman

##### Board Regular
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.

<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Workbook Defined Names</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">All_Bike_Dates</th><td style="text-align:left">='Exercise Bike'!\$A\$11:INDEX(<font color="Blue">'Exercise Bike'!\$A\$11:\$A\$9997,COUNTA(<font color="Red">'Exercise Bike'!\$A\$11:\$A\$9997</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">All_Bike_Miles</th><td style="text-align:left">='Exercise Bike'!\$D\$11:INDEX(<font color="Blue">'Exercise Bike'!\$D\$11:\$D\$9997,COUNTA(<font color="Red">'Exercise Bike'!\$A\$11:\$A\$9997</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">All_Bike_Times</th><td style="text-align:left">='Exercise Bike'!\$B\$11:INDEX(<font color="Blue">'Exercise Bike'!\$B\$11:\$B\$9997,COUNTA(<font color="Red">'Exercise Bike'!\$A\$11:\$A\$9997</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">LastYTD_Bike_Miles</th><td style="text-align:left">=INDEX(<font color="Blue">All_Bike_Miles,BikeLastYearTopIndex</font>):INDEX(<font color="Blue">All_Bike_Miles,BikeLastYTDBottomIndex</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">LastYTD_Bike_Times</th><td style="text-align:left">=INDEX(<font color="Blue">All_Bike_Times,BikeLastYearTopIndex</font>):INDEX(<font color="Blue">All_Bike_Times,BikeLastYTDBottomIndex</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">LogYear</th><td style="text-align:left">='Training Log'!\$C\$4</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">LogYear_Bike_Dates</th><td style="text-align:left">=INDEX(<font color="Blue">All_Bike_Dates,IFERROR(<font color="Red">MATCH(<font color="Green">DATE(<font color="Purple">LogYear-1,12,31</font>),All_Bike_Dates,1</font>),0</font>)+1</font>):INDEX(<font color="Blue">All_Bike_Dates,ROWS(<font color="Red">All_Bike_Dates</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">LogYear_Bike_Miles</th><td style="text-align:left">=INDEX(<font color="Blue">All_Bike_Miles,IFERROR(<font color="Red">MATCH(<font color="Green">DATE(<font color="Purple">LogYear-1,12,31</font>),All_Bike_Dates,1</font>),0</font>)+1</font>):INDEX(<font color="Blue">All_Bike_Miles,ROWS(<font color="Red">All_Bike_Dates</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">LogYear_Bike_Times</th><td style="text-align:left">=INDEX(<font color="Blue">All_Bike_Times,IFERROR(<font color="Red">MATCH(<font color="Green">DATE(<font color="Purple">LogYear-1,12,31</font>),All_Bike_Dates,1</font>),0</font>)+1</font>):INDEX(<font color="Blue">All_Bike_Times,ROWS(<font color="Red">All_Bike_Dates</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">LogYear_Bike_Watts</th><td style="text-align:left">=INDEX(<font color="Blue">All_Bike_Watts,IFERROR(<font color="Red">MATCH(<font color="Green">DATE(<font color="Purple">LogYear-1,12,31</font>),All_Bike_Dates,1</font>),0</font>)+1</font>):INDEX(<font color="Blue">All_Bike_Watts,ROWS(<font color="Red">All_Bike_Dates</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">VBADaysGone</th><td style="text-align:left">='Training Log'!\$A\$4</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">VBADaysLeft</th><td style="text-align:left">='Training Log'!\$A\$6</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">x</th><td style="text-align:left">=OFFSET(<font color="Blue">LastLogDate,0,0,-90</font>)</td></tr></tbody></table></td></tr></table><br />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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### Ironman

##### Board Regular
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:

Last edited:

#### RasGhul

##### Well-known Member
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;

#### RasGhul

##### Well-known Member

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)

#### Ironman

##### Board Regular
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:

#### RasGhul

##### Well-known Member

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...

#### Ironman

##### Board Regular
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:

#### Ironman

##### Board Regular
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:

#### RasGhul

##### Well-known Member
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)

Replies
5
Views
467
Replies
12
Views
219
Replies
4
Views
424
Replies
2
Views
210
Replies
13
Views
798