Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sum array with #N/A
Hi,
For some reason I can't get the following to work. I have got an array of numbers in which some observations are #N/A (the equivalent of NA() ). 3 6 8 #N/A 4 #N/A 5 Normally I'd just sum this array by submitting {=SUM(A1:A7*ISNUMBER(A1:A7))}, but the (arrayed) formula returns #N/A. What do I need to do in order for the formula to return the value 26? Thanks for your help. |
#2
|
|||
|
|||
=SUM(IF(NOT(ISERROR(a1:a7)),a1:a7))
array entered works for me if that helps "Henrik" wrote in message ... Hi, For some reason I can't get the following to work. I have got an array of numbers in which some observations are #N/A (the equivalent of NA() ). 3 6 8 #N/A 4 #N/A 5 Normally I'd just sum this array by submitting {=SUM(A1:A7*ISNUMBER(A1:A7))}, but the (arrayed) formula returns #N/A. What do I need to do in order for the formula to return the value 26? Thanks for your help. |
#3
|
|||
|
|||
Fair enough, but I guess I need to add another dimension to this (as to why I
didn't choose that approach in the first place). Let's say that sum the numbers in column A when the value in columb B is greater than 9 and omit observations with #N/A. 3 2 6 8 8 10 #N/A 12 4 14 #N/A 4 5 14 I propose the following approach (which doesn't work) even though, I thought it did in the past: {=SUM(A1:A7*ISNUMBER(A1:A7)*(B1:B79))}, As far as I know, there is no way to solve this using a sum(if()) combination. "N Harkawat" wrote: =SUM(IF(NOT(ISERROR(a1:a7)),a1:a7)) array entered works for me if that helps "Henrik" wrote in message ... Hi, For some reason I can't get the following to work. I have got an array of numbers in which some observations are #N/A (the equivalent of NA() ). 3 6 8 #N/A 4 #N/A 5 Normally I'd just sum this array by submitting {=SUM(A1:A7*ISNUMBER(A1:A7))}, but the (arrayed) formula returns #N/A. What do I need to do in order for the formula to return the value 26? Thanks for your help. |
#4
|
|||
|
|||
Hi
change the formula which create #NA. e.g. =IF(ISNA(your_formula),"",your_formula) -- Regards Frank Kabel Frankfurt, Germany "Henrik" schrieb im Newsbeitrag ... Hi, For some reason I can't get the following to work. I have got an array of numbers in which some observations are #N/A (the equivalent of NA() ). 3 6 8 #N/A 4 #N/A 5 Normally I'd just sum this array by submitting {=SUM(A1:A7*ISNUMBER(A1:A7))}, but the (arrayed) formula returns #N/A. What do I need to do in order for the formula to return the value 26? Thanks for your help. |
#5
|
|||
|
|||
Henrik wrote... . . . Let's say that sum the numbers in column A when the value in columb B is greater than 9 and omit observations with #N/A. 3 2 6 8 8 10 #N/A 12 4 14 #N/A 4 5 14 I propose the following approach (which doesn't work) even though, I thought it did in the past: {=SUM(A1:A7*ISNUMBER(A1:A7)*(B1:B79))} As far as I know, there is no way to solve this using a sum(if()) combination. ... Well, not if you confuse IF(x,y) with x*y. Try using a *REAL* IF(). =SUM(IF(ISNUMBER(A1:A7)*(B1:B79),A1:A7)) -- hgrove ------------------------------------------------------------------------ hgrove's Profile: http://www.excelforum.com/member.php...o&userid=11432 View this thread: http://www.excelforum.com/showthread...hreadid=318871 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Where is the bug in my array? | Excel Discussion (Misc queries) | |||
Working with array equations | Excel Discussion (Misc queries) | |||
Transpose into a _working_ transposed array | Excel Discussion (Misc queries) | |||
What instead of an array formula part 2 | Excel Discussion (Misc queries) | |||
VBA Import of text file & Array parsing of that data | Excel Discussion (Misc queries) |