Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
averaging less than values
How do I average a column of numbers where less than values are calculated as
that number? 2 <2 3 4 <2 5 In the average I want the<2 to = 2. So the answer should be 3 -- JD |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
averaging less than values
Assuming source data as posted in A2:A7
Paste this in say, B2, then press CTRL+SHIFT+ENTER to array-enter the formula: =AVERAGE(SUBSTITUTE(A2:A7,"<","")+0) Adapt the ranges to suit -- Max Singapore http://savefile.com/projects/236895 Downloads:17,500 Files:358 Subscribers:55 xdemechanik --- "JD" wrote: How do I average a column of numbers where less than values are calculated as that number? 2 <2 3 4 <2 5 In the average I want the<2 to = 2. So the answer should be 3 -- JD |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
averaging less than values
Paste this in say, B2,
should have read as: Paste this in say, B2's formula bar If you did the CTRL+SHIFT+ENTER confirmation (the "array-enter") properly, you should see Excel wrap curly braces around the formula in the formula bar, viz.: {=AVERAGE(SUBSTITUTE(A2:A7,"<","")+0)} If you don't see the curlies, just re-click inside the formula bar, re-do the CTRL+SHIFT+ENTER confirmation Then look again that the curlies are there If the formula is not array-entered, it will not return the correct result -- Max Singapore http://savefile.com/projects/236895 Downloads:17,500 Files:358 Subscribers:55 xdemechanik --- |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
averaging less than values
It works if there are values in every cell of the column (A2:A7). What if
some cells do not have values to average? -- JD "Max" wrote: Paste this in say, B2, should have read as: Paste this in say, B2's formula bar If you did the CTRL+SHIFT+ENTER confirmation (the "array-enter") properly, you should see Excel wrap curly braces around the formula in the formula bar, viz.: {=AVERAGE(SUBSTITUTE(A2:A7,"<","")+0)} If you don't see the curlies, just re-click inside the formula bar, re-do the CTRL+SHIFT+ENTER confirmation Then look again that the curlies are there If the formula is not array-entered, it will not return the correct result -- Max Singapore http://savefile.com/projects/236895 Downloads:17,500 Files:358 Subscribers:55 xdemechanik --- |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
averaging less than values
"JD" wrote:
It works if there are values in every cell of the column (A2:A7) Ah, but wasn't that exactly what you showed in your posting? Take a moment to press the "Yes" button in that response (like the ones below) What if some cells do not have values to average? This should work, array-entered: =AVERAGE(IF(A2:A7<"",SUBSTITUTE(A2:A7,"<","")+0)) -- Max Singapore http://savefile.com/projects/236895 Downloads:17,500 Files:358 Subscribers:55 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Averaging all values for each hour??? | Excel Discussion (Misc queries) | |||
Averaging last 25 non zero values. | Excel Discussion (Misc queries) | |||
HELP: Averaging values if they meet certain criteria | Excel Worksheet Functions | |||
Averaging Values in Auto Filter | Excel Worksheet Functions | |||
Averaging selected values | Excel Worksheet Functions |