I have a spreadsheet that I am trying to tweak, I'm not a coder so stay away from VB and so far can do most things I want to excel logic.
In the range P2:AB35 I have a series of numbers, each representing a year from 2013 onwards, what I want is the average of the last five years, to do this I have the following
(Cell P1) =YEAR(TODAY())
(Q1) P1-1
(R1) Q1-1
(S1) R1-1
(T1) S1-1
This gives me the current year and the last 4 that I can reference.
I then use HLOOKUP to search a range for the values that refer to those years
(the 5 refers to the 5th row down under that year, the next time is 6 so is the sixth row down etc)
This works perfectly, for those conditions with 5 years of entries, if you only have 1 year it will include the blanks and give you the average of year 1 + 4 years of zero, which works kinda but not ideal as it gives me a 5th of one year rather than the average of one year which is the same as one year.
I want to try and ignore any years with blanks, this works perfectly,
But won't limit myself to the last 5 years when it has enough data point.
I can't seen to work out how to nest the two, is it even possible?
In the range P2:AB35 I have a series of numbers, each representing a year from 2013 onwards, what I want is the average of the last five years, to do this I have the following
(Cell P1) =YEAR(TODAY())
(Q1) P1-1
(R1) Q1-1
(S1) R1-1
(T1) S1-1
This gives me the current year and the last 4 that I can reference.
I then use HLOOKUP to search a range for the values that refer to those years
Code:
=AVERAGE((HLOOKUP((P1),$P2:$AB35,5)),(HLOOKUP((Q1),$P2:$AB35,5)),(HLOOKUP((R1),$P2:$AB35,5)),(HLOOKUP((S1),$P2:$AB35,5)),(HLOOKUP((T1),$P2:$AB35,5)))
This works perfectly, for those conditions with 5 years of entries, if you only have 1 year it will include the blanks and give you the average of year 1 + 4 years of zero, which works kinda but not ideal as it gives me a 5th of one year rather than the average of one year which is the same as one year.
I want to try and ignore any years with blanks, this works perfectly,
Code:
=AVERAGEIF(P2:AB2,"<>")
I can't seen to work out how to nest the two, is it even possible?
Comment