I need to calculate the average for some groups of figures and some of the columns contain figures lower than or greater than
For instance, one set reads: ≤ 60, ≤ 60, ≤ 60, ≤ 60, 67, ≤ 60, 176, 99, ≤ 60, 74, ≤ 60, 154, ≤ 60, ≤ 60, 114. Is there a formula that I can use ?
Array-enter (enter using Ctrl-Shift-Enter) a formula like this:
=AVERAGE(VALUE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2:P2,"≤",""),">",""),"<",""),"XXXX",""))))
Replace the XXXX with the >= character - I'm not sure how to enter it off-hand - and expand the formula with more substitute functions to remove any other special characters in the data.
Bernie Deitrick
Excel MVP 2000-2010
This should find the average of the example for you.
...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 will get an error or a clearly incorrect answer. Press F2 on that cell and try again.Formula:
=AVERAGE(SUBSTITUTE(A1:O1,"≤","")*1)
To include < and > this array formula will cover having all three symbols.
Formula:=AVERAGE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1:O1,"≤",""),"<",""),">","")*1)
Last edited by newdoverman; 11-26-2014 at 11:53 AM.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks