Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating basic averages...a question
Hi all First post! I hope this is the right section! I need to calculate a simple average (arithmetic and median) of a set of numbers, but excluding certain outlying points. I'm not by any means a newbie to coding so I do know how I'd do this generally in a programming language or "pseudo code" (so no need to waste your time on long explanations!) but I am completely new to Excel so have no idea of the syntax and don't really have the time to mess around with the "guess the useful phrase" help function. Thanks in advance...I hope... -- stew1901 ------------------------------------------------------------------------ stew1901's Profile: http://www.excelforum.com/member.php...o&userid=29164 View this thread: http://www.excelforum.com/showthread...hreadid=488880 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating basic averages...a question
Hi!
To average values that are 25 but <50: Array entered using the key combo of CTRL,SHIFT,ENTER: =AVERAGE(IF(A1:A1025,IF(A1:A10<50,A1:A10))) Biff "stew1901" wrote in message ... Hi all First post! I hope this is the right section! I need to calculate a simple average (arithmetic and median) of a set of numbers, but excluding certain outlying points. I'm not by any means a newbie to coding so I do know how I'd do this generally in a programming language or "pseudo code" (so no need to waste your time on long explanations!) but I am completely new to Excel so have no idea of the syntax and don't really have the time to mess around with the "guess the useful phrase" help function. Thanks in advance...I hope... -- stew1901 ------------------------------------------------------------------------ stew1901's Profile: http://www.excelforum.com/member.php...o&userid=29164 View this thread: http://www.excelforum.com/showthread...hreadid=488880 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating basic averages...a question
You could do this with array formulas. Instead of hitting Enter to
finish the formula, use Ctrl-Shift-Enter to add the curly braces to the array formula. If the range of values is in cells A1 through A10 and you want to disregard all values equal to or greater than 25, use: {=AVERAGE(IF(A1:A10<25,A1:A10,""))} {=MEDIAN(IF(A1:A10<25,A1:A10,""))} For more info on array formulas, see http://www.cpearson.com/excel/array.htm Hope that helps. - John www.JohnMichl.com |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating basic averages...a question
Thanks a lot! That is SO FAR off what I was expecting you have probably saved me an hour of frustration! -- stew1901 ------------------------------------------------------------------------ stew1901's Profile: http://www.excelforum.com/member.php...o&userid=29164 View this thread: http://www.excelforum.com/showthread...hreadid=488880 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating basic averages...a question
stew1901 -
The median is relatively insensitive to the values of the extremes, so maybe you don't have to exclude them. For the arithmetic mean, you might try the TRIMMEAN worksheet function. - Mike www.mikemiddleton.com "stew1901" wrote in message ... Hi all First post! I hope this is the right section! I need to calculate a simple average (arithmetic and median) of a set of numbers, but excluding certain outlying points. I'm not by any means a newbie to coding so I do know how I'd do this generally in a programming language or "pseudo code" (so no need to waste your time on long explanations!) but I am completely new to Excel so have no idea of the syntax and don't really have the time to mess around with the "guess the useful phrase" help function. Thanks in advance...I hope... -- stew1901 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating basic averages...a question
Good point on the median Mike. I was going to exclude it as I didn't see an immediate way of using similar techniques (and it's not that vital...this isn't anything particularly scientific) TRIMMEAN I don't think would work...what I basically have is a list of exam scores, but with a few absences (recorded as zero) and a very few low scores from people who have since quit. No doubt there are more sensible ways I could have organised the data! But at least I've got a very small bit of experience with worksheet functions now. The CTRL SHIFT ENTER thing in particular I had no idea about and don't see how I'd have stumbled across by accident. -- stew1901 ------------------------------------------------------------------------ stew1901's Profile: http://www.excelforum.com/member.php...o&userid=29164 View this thread: http://www.excelforum.com/showthread...hreadid=488880 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Basic Pivot Table Question (using 97) | Excel Discussion (Misc queries) | |||
basic question on typing names on spreadsheet- problem | Excel Discussion (Misc queries) | |||
calculating averages | Excel Discussion (Misc queries) | |||
Very basic question - how long can a list be? | Excel Discussion (Misc queries) | |||
basic pie chart question | Charts and Charting in Excel |