LinkBack URL
About LinkBacks
Hello:
I am brand new to the forum and would greatly appreciate your assistance.
For a data contained in a row (e.g. A2..Z2), I need to calculate the average of the previous 3 entries excluding any blank cells, but including 0 (zeros).
Thank you!
Average if last 3 entries skiping the final entry and excluding blanks
Hello:
I am brand new to the forum and would greatly appreciate your assistance.
For a data contained in a row (e.g. A2..Z2), I need to calculate the average of the previous 3 entries excluding any blank cells, but including 0 (zeros).
Thank you!
Hi and welcome to the forum
A regular =AVERAGE() will include zero's but ignore blank cells in the range
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thanks for your response.
Much like a moving average, I need the formula to skip the last entry and average the next 3 entries (cells that contain numbers) it finds while skipping any blank cells. Am I expaining it a little better?
welcome to the forum, HAEG. it's always clearer when you upload a sample excel file & follow my guidelines here:
http://www.excelforum.com/tips-and-t...d-answers.html
my guess is you can use this array formula:
=AVERAGE(IF(COLUMN(A2:Z2)=LARGE(IF(A2:Z2<>"",COLUMN(A2:Z2)),{1;2;3}),A2:Z2))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you might get an error or a clearly incorrect answer. Press F2 on that cell and try again.
if you want to skip the last entry, change {1;2;3} to {2;3;4}
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
Thank you, benishiryo!
I retested, and the formula does work. However, It did not work if there were less than 3 entries in the row. How do I adjust the formula to correct those errors?
Can anyone else help with the last part of this formula? What if I have less than 3 entries in the row?
Much like a moving average, I need the formula to skip the last entry in the row and average the next 3 or (less) entries (i.e. cells that contain numbers) it finds while skipping any blank cells.
array formula:
=AVERAGE(IF(COLUMN(A2:Z2)=LARGE(IF(A2:Z2<>"",COLUMN(A2:Z2)),{1;2;3}),A2:Z2))
if you want to skip the last entry, change {1;2;3} to {2;3;4}
you're welcome. here's 1 way:
=IFERROR(AVERAGE(IF(COLUMN(A2:Z2)=LARGE(IF(A2:Z2<>"",COLUMN(A2:Z2)),{1;2;3}),A2:Z2)),AVERAGEIF(A2:Z2,"<>"))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks