LinkBack URL
About LinkBacks
Rikuk 
Hi,
Could anyone help please I have a grid example below, I need to know the average for the last 3 numbered cells in each row? which will change weekly
Week 1 Week 2 Week 3 Week 4 Week 5 Week 6
A 157 621 142 458
B 150 155 90
C 456 568 584 452
Hi,
Could anyone help please I have a grid example below, I need to know the average for the last 3 numbered cells in each row? which will change weekly
Week 1 Week 2 Week 3 Week 4 Week 5 Week 6
A 157 621 142 458
B 150 155 90
C 456 568 584 452
One way...
where A1:Z1 is your first row range... you can replace with 1:1 if you want to include entire row.
=AVERAGE(INDEX(A1:Z1,MATCH(9.9999999E+307,A1:Z1)),INDEX(A1:Z1,MATCH(9.9999999E+307,A1:Z1)-1),INDEX(A1:Z1,MATCH(9.9999999E+307,A1:Z1)-2))
formula can be copied down.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Thanks works a treat
I prefer the OFFSET functionChemistB=AVERAGE(OFFSET(A2,,COUNT(2:2)-3,,3))
That's a possibility...with the following caveat:Originally Posted by ChemistB
I prefer the OFFSET functionChemistB=AVERAGE(OFFSET(A2,,COUNT(2:2)-3,,3))
The OFFSET function is volatile, so every cell containing OFFSET recalculates
whenever *any* cell in the workbook recalcs. There is a threshhold where
the workbook becomes annoyingly sluggish. Consequently, I only use
OFFSET sparingly and only when necessary.
This is my latest list of volatile functions:
RAND, NOW, TODAY, OFFSET, CELL, INDIRECT
Thanks Ron, I will definitely remember that when working with Offset in the future. Question: What if Offset is used in a defined name? Does it have the same problem?
Thanks
ChemistB
With your posted data in A1:G4
This formula returns the average of the last 3 consecutive values in B2:G2:
Is that something you can work with?H2: =AVERAGE(INDEX(B2:G2,MATCH(10^99,B2:G2)):INDEX(B2:G2,MATCH(10^99,B2:G2)-2))
With your posted data in A1:G4
This formula returns the average of the last 3 consecutive values in B2:G2:
Is that something you can work with?H2: =AVERAGE(INDEX(B2:G2,MATCH(10^99,B2:G2)):INDEX(B2:G2,MATCH(10^99,B2:G2)-2))
oOOPS.
Don't know why I didn't think of using a range instead of 3 arguments...
Mine would then be:
=AVERAGE(INDEX(A1:Z1,MATCH(9.9999999E+307,A1:Z1)-2):INDEX(A1:Z1,MATCH(9.9999999E+307,A1:Z1)))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks