 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / May 2008Average every third entry
Hi there, thanks for all your help in advance. I am looking for a formula that will average every 3rd entry on a row of data. I would like to set it up so that it ignores empty cells. chris Use this array formula(must be entered using ctrl+shift+enter) to average <> " " =AVERAGE(IF(H2:H10<>" ",H2:H10))  SignatureDon Guillett SalesAid Software dguillett1@austin.rr.com > Hi there, thanks for all your help in advance. I am looking for a formula > that will average every 3rd entry on a row of data. I would like to set > it > up so that it ignores empty cells. chris Thanks, will give it a go. > Use this array formula(must be entered using ctrl+shift+enter) to average <> > " " [quoted text clipped - 4 lines] > > it > > up so that it ignores empty cells. chris I gave it a go, and it worked, but it did not calculate every third entry, how do I adapt it to do that. > Thanks, will give it a go. > [quoted text clipped - 6 lines] > > > it > > > up so that it ignores empty cells. chris try this =AVERAGE(IF(MOD(ROW(H2:H10)-1,3)=0,H2:H10)) or =AVERAGE(IF(MOD(ROW($H$2:$H$10),3)=1,IF(ISNUMBER($H$2:$H$10)<>"",$H$2:$H$10))) SignatureDon Guillett SalesAid Software dguillett1@austin.rr.com >I gave it a go, and it worked, but it did not calculate every third entry, > how do I adapt it to do that. [quoted text clipped - 12 lines] >> > > it >> > > up so that it ignores empty cells. chris One way (array-entered:CTRL-SHIFT-ENTER or CMD-RETURN): =AVERAGE(IF(MOD(COLUMN(rng),3)=MOD(COLUMN(INDEX(rng,1)),3), IF(rng<>"", rng))) where "rng" is the range of data in your row. This assumes you want the 1st, 4th, 7th... entry. If you want the 2nd, 5th, 8th...entry, change INDEX(rng,1) to INDEX(rng,2), etc. > Hi there, thanks for all your help in advance. I am looking for a formula > that will average every 3rd entry on a row of data. I would like to set it > up so that it ignores empty cells. chris Wow thanks guys, I will give them both a go and see. Thanks a lot !! > One way (array-entered:CTRL-SHIFT-ENTER or CMD-RETURN): > [quoted text clipped - 10 lines] > > that will average every 3rd entry on a row of data. I would like to set it > > up so that it ignores empty cells. chris Perfect. Thank you all very much. Job done !!!!! > Wow thanks guys, I will give them both a go and see. Thanks a lot !! > [quoted text clipped - 12 lines] > > > that will average every 3rd entry on a row of data. I would like to set it > > > up so that it ignores empty cells. chris I would like to use this formula, however is there a way to alter it so I take the average of every 3rd number in multiple ranges? ex: take every 3rd number in the following ranges Details!C8:C161,Details!C165:C318,Details!C322:C475,Details!C479:C632,Details!C636:C798 > One way (array-entered:CTRL-SHIFT-ENTER or CMD-RETURN): > [quoted text clipped - 10 lines] > > that will average every 3rd entry on a row of data. I would like to set it > > up so that it ignores empty cells. chris Try this: With A1:O1 containing numbers or blanks These formulas average every 3rd non-blank cell, beginning with cell A1 A2: =AVERAGE(IF((A1:O1<>"")*(MOD(COLUMN(A1:O1),3)=1),A1:O1)) beginning with cell B1 A2: =AVERAGE(IF((A1:O1<>"")*(MOD(COLUMN(A1:O1),3)=2),A1:O1)) beginning with cell C1 A2: =AVERAGE(IF((A1:O1<>"")*(MOD(COLUMN(A1:O1),3)=0),A1:O1)) Is that something you can work with? *********** Regards, Ron XL2002, WinXP > Hi there, thanks for all your help in advance. I am looking for a formula > that will average every 3rd entry on a row of data. I would like to set it > up so that it ignores empty cells. chris |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.