Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Averages excluding #N/A
I have a column of data, some of which are #N/A. In the next column I want to
calculate te running average of 5 points in that data. The problem is that if any one of those 5 points is #N/A, my running average value is also #N/A. Can anyone suggest a formula that will exclude a value in my average caluculation if it is #N/A? Thanks in advance. |
#2
|
|||
|
|||
One way ..
Try in say B1, array-entered (CTRL+SHIFT+ENTER): =AVERAGE(IF(ISNUMBER(A1:A5),A1:A5)) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "rmellison" wrote in message ... I have a column of data, some of which are #N/A. In the next column I want to calculate te running average of 5 points in that data. The problem is that if any one of those 5 points is #N/A, my running average value is also #N/A. Can anyone suggest a formula that will exclude a value in my average caluculation if it is #N/A? Thanks in advance. |
#3
|
|||
|
|||
Thanks! Actually, I had already tried that formula (or something very
similar), but foolishly had not tried it array-entered! "Max" wrote: One way .. Try in say B1, array-entered (CTRL+SHIFT+ENTER): =AVERAGE(IF(ISNUMBER(A1:A5),A1:A5)) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "rmellison" wrote in message ... I have a column of data, some of which are #N/A. In the next column I want to calculate te running average of 5 points in that data. The problem is that if any one of those 5 points is #N/A, my running average value is also #N/A. Can anyone suggest a formula that will exclude a value in my average caluculation if it is #N/A? Thanks in advance. |
#4
|
|||
|
|||
Glad you got it sorted out <g !
-- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "rmellison" wrote in message ... Thanks! Actually, I had already tried that formula (or something very similar), but foolishly had not tried it array-entered! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trouble Sorting Averages of Randomly Generated Numbers | Excel Discussion (Misc queries) | |||
calculating averages | Excel Discussion (Misc queries) | |||
Pivot table (Running Averages) | Excel Worksheet Functions | |||
Difference between Averages in a Pivot Table | Excel Worksheet Functions | |||
Converting Weekly Data into Monthly Averages | Excel Discussion (Misc queries) |