Formul to compute average and no of years up to last item in Col

[Howardc1001]

I would like to compute the average from B2 up to the last item in Col B where there is Data.

I have used

Please Login or Register  to view this content.

I also want to compute the number of years in Col A (This Column contains dates) from A2 up to last item in Col A

My formula for this is

Please Login or Register  to view this content.

[Estevaoba]

The values in column B are not all percentage, so I converted in % to column D those that are not % with this formula:

Please Login or Register  to view this content.

For the average I use this formula:

Please Login or Register  to view this content.

For the count of years and months:

Please Login or Register  to view this content.

Whenever possible, avoid using whole column references like A:A, which accounts for over a million rows.
Maybe you can use A2:A1000, for example, depending on your data size.

Please check file.

Good luck!

[Bo_Ry]

Please try
Average Interest
=AVERAGE(INDEX(--TEXT(B2:INDEX(B:B,MATCH(99,B:B)),"[>1]0.00\%;0.00%"),))

No of Year
=ROUND(COUNT(A:A)/12,2)

or
=YEARFRAC(A2,LOOKUP(9^9,A:A))

[kvsrinivasamurthy]

For number of years
ARRAY formula.
=SUM(1/(MMULT(0+(YEAR($A$2:$A$60)=TRANSPOSE(YEAR($A$2:$A$60))),0+(ROW($A$2:$A$60)>0))))

To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

[Howardc1001]

thanks for the help. Formulas work perfectly

[kvsrinivasamurthy]

Glad to hear that problem is solved. Pl mark the thread solved.

[Estevaoba]

You're welcome. Glad to help.
Thanks for the feedback and the rep points added.

[Howardc1001]

Thanks for the help, Kvsrinivasamurthy


Please advise how your formula works