Here is a link to a sample of my sheet.

Columns B and C work well, despite how clunky the functions are. I would like to make the functions in any cell B2:C7 more dynamic, and am using column D as a testing column. I am not a fan of how INDIRECT() needs to be manually changed when dragging the cells across and am looking for a simpler solution.

Specifically looking at the INDEX() function in cell D2.

I am trying to run a counta(D7:the row with today's date) in cell D2.

When I do

=(counta(D7:D13))/365

I get the correct result 6/365 = 1.644.

Now, when I use the INDEX() to call on the value in CELL D3 as shown here:

=(counta(D7:INDEX(D:D,3,)))/365

I get a value which would match the 4/365 = 1.096

When I highlight "INDEX(D:D,3,)" the tool tip pops up and shows the correct value of cell D3 which is "D13"

Another user provided a working solution using INDIRECT() and SUBSTITUTE, but I am specifically curious about why INDEX() acts this way as it seems to be much simpler to read/understand. Any reason why its off by 2 values?

Thanks,

CodePudding user response：

UPDATED: After understanding the question a little better...

Here is a formula to show the percent complete that can be dragged sideways, you can find it on the new tab called MK.Help:

=COUNTIFS($A7:$A,"<="&TODAY(),B7:B,"<>")/365