LinkBack URL
About LinkBacks
Hello,
Martindwilson provided this formula on a different thread:
=AVERAGE(OFFSET(P3,,MATCH(1E+100,P3:AAD3)-A1,1,A1))
It almost works for me but I need it to ignore cells that are empty.
Can some one help me out please?
Many Thanks
C.
Hello,
Martindwilson provided this formula on a different thread:
=AVERAGE(OFFSET(P3,,MATCH(1E+100,P3:AAD3)-A1,1,A1))
It almost works for me but I need it to ignore cells that are empty.
Can some one help me out please?
Many Thanks
C.
Hi Cryptic,
welcome to the forum.
And those n number range is including blanks for example - last 20 numbers to be averaged in a range of A1:A100 so will your consider A80:A100 or A75:A100 if 5 are blank cells in both the cases ?
Regards,
DILIPandey
<click on below * if this helps>
DILIPandey, Excel rMVP
+919810929744 (India), +971528225509 (Dubai), dilipandey@gmail.com
Thanks DILIPandy
In a range of A1 to A100 where there are 5 blank cells I need the average of the last 20 numbers.
So, if A81 and A82 are blank the range to be averaged would be A78 to A100 with the total still being divided by 20 and not 22.
Regards
H Cryptic
See the attached file where I have used a defined name "data" having logic as:-
Formula:
Please Login or Register to view this content.
It will always count last 20 values - ignoring blanks. You can change 20 to your choice OR reference that from a cell.
average of last n ignoring blanks.xlsx
Regards,
DILIPandey
<click on below * if this helps>
You could try this CSE array Confirm with Ctrl+Shift+Enter not just Enter
Formula:Please Login or Register to view this content.
To avoid the CSE entry you can use a defined name
Name:= "DataRows"
Refers to:=
Formula:Please Login or Register to view this content.
Then
Formula:Please Login or Register to view this content.
Change 10 to suit
Last edited by Marcol; 06-02-2013 at 04:49 AM.
If you need any more information, please feel free to ask.
However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....
Also
اس کی مدد کرتا ہے اگر
شکریہ کہنے کے لئے سٹار کلک کریں
If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.
Thanks Marcol
Run out of time to look at this properly tonight and the server is really slow.
I will try again tomorrow.
Thanks again
Hello again,
I have tried unsuccessfully to modify some of your suggestions to work on my spread sheet.
I have attached a sample (which is, of course, what I should have done in the first place).
I need to find the average of the last n numbers(A1)in each row while ignoring the blank cells.
Thanks for helping again.
I'm getting really peed off with this site crashing.
See if this workbook does as you need.
Column AI averages the last n elements if there are at least n elements in the row, if not "" is returned.
Column AK averages the last n elements if there are less than n elements the whole row is averaged.
Thanks very much Marcol,
This is exactly what I needed. I don't fully understand how it works but you have made me look a like genius!
Thanks again
C
Happy to have helped.
Be careful, they will just load more work on you ...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks