Any excel experts?

[malvolio]

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

=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)))

(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,

=AVERAGEIF(P2:AB2,"<>")

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?

Without looking at the whole thing, can you not make the first expression the subject of the last one, i.e. replace the range P2:AB2 with that expression and count back all the brackets?

I had loads of fun making such complex interlaced calculations work. Sometimes you have to go one step further and use an array formula so it can keep track of itself. That may well be the case here.

[SimonMac]

Rather than using Average, could you just do a Sum and then divide by CountIf > 0

If you're happy to send me the file, I'll sort it for you. It's easier to look at it than work it out third hand.

Was easier than I thought, just needed an IF between the two options rather than trying to combine into one

=IF(COUNTA(P5:AB5)>5,AVERAGE((HLOOKUP((P1),$P2:$AB35,4)),(HLOOKUP((Q1),$P2:$AB35,4)),(HLOOKUP((R1),$P2:$AB35,4)),(HLOOKUP((S1),$P2:$AB35,4)),(HLOOKUP((T1),$P2:$AB35,4))),AVERAGE(P5:AB5))

[WTFH]

Rather than using Average, could you just do a Sum and then divide by CountIf > 0

[ladymuck]

If you're happy to send me the file, I'll sort it for you. It's easier to look at it than work it out third hand.

[SimonMac]

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

=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)))

(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,

=AVERAGEIF(P2:AB2,"<>")

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?