Array Formula Counts Blanks as Zeroes
Posted by SJC on February 21, 2001 11:43 AM
I'm using an array formula to calculate daily averages for agents' sales for each month. Here's a snippet of my daily sales data:
Agent Feb-1 Feb-2 Feb-3 Feb-4
A_______100_blank___220___250
B______1500___900___900__1000
C_______700___500___600___800
Note that A got a blank for Feb-2 because A didn't try selling anything so we can't consider A got zero for Feb-2.
If I calculate averages for A, B, C using the usual AVERAGE function, I'd get 190, 1075, and 650 for A, B, C respectively. In this case, Excel "ignores" A for Feb-2, and that's what I want.
If I use array formula to calculate averages, I get 142.5 for A. In this case, Excel substitutes the blank with a zero for A for Feb-2, and I don't want that.
Is there anyway to make the array formula return 190 instead of 142.5? I've tried using the combination array SUM divided by array COUNT but COUNT in this case also counts the blank. Thanks in advance for your help!
Posted by Aladin Akyurek on February 21, 2001 12:14 PM
Array-enter:
=SUM(IF(ISNUMBER(B3:E3),1)*(B3:E3))/SUM(IF(ISNUMBER(B3:E3),1,0))
Aladin
Posted by SJC on February 21, 2001 2:25 PM
Thanks. How can I use this without having to specify the range B3:E3? If in column B was sales for Jan-31, I don't want column B included in the calculation because column contains Jan sales. This is what I've tried but the count part returns zero: SUM(IF(AND(MONTH(B2:IV2)=MONTH(TODAY()), ISNUMBER(B3:IV3)),1,0))
Sorry I wasn't clearer on my first post.
Posted by Aladin Akyurek on February 21, 2001 3:04 PM
: I'm using an array formula to calculate daily averages for agents' sales for each month. Here's a snippet of my daily sales data: : Agent Feb-1 Feb-2 Feb-3 Feb-4 : A_______100_blank___220___250 : B______1500___900___900__1000 : C_______700___500___600___800
No problem.
Put in some cell the criterial month number and expand the array-formula I suggested as follows:
=SUM(IF(ISNUMBER(B2:E2),1)*IF(MONTH(B1:E1)=G1,1,0)*(B2:E2))/SUM(IF(ISNUMBER(B2:E2),1,0))
G1 contains 2==february (following your example data). B2:E2 contains sales numbers for Agent A.
Aladin
Posted by SJC on February 21, 2001 3:40 PM
:
Thanks! I've also thought of another way: in the blank cells my data, I replaced the blanks with non-number values (like x). The averages then worked fine. Do you foresee any potential problem with this besides the fact that my file size will increase?
Posted by Aladin Akyurek on February 21, 2001 3:54 PM
No, I don't think so, besides forgetting those x's. BTW, you should also expand the denominator in my formula with IF that deals with the month question. Isn't this formula a bit more flexible than yours?
Aladin
