How to get the average of the first 3 right non blank cells
Hi, I am having a dilemma on getting the average for the first 3 non blank cells.
This is how my data looks like :
excel average.jpg
I don't know any formula that will disregard the non blank cell and will get the average of the firs 3 non blank cells from the right.
Can anyone help me with this...
Last edited by Yunamiwaka; 04-25-2014 at 07:23 PM.
like this..
or you could also try in I2
copy paste below hold control and shift together and then hit enter to make it array formula
=SUM(MMULT(--(COLUMN(A2:G2)=TRANSPOSE(LARGE(IF(A2:G2<>"",COLUMN(A2:G2),0),{1,2,3}))),TRANSPOSE(IF(A2:G2="",0,A2:G2))))/3
then drag down
Last edited by hemesh; 04-26-2014 at 02:45 AM.
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST
More we learn about excel, more it shows us, how less we know about it.
for chemistry
https://www.youtube.com/c/chemistrybyshivaansh
Thank you so much!!! This really worked!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks