Solved: IF(IsNumber

[bluetide76]

I know the basics of using this in functions to weed out dividing by zeroes as well as making sure there is a number there or you can leave that cell blank. What about if you call upon a cell that has a function value in it.
Example:
say cell C1 has the formula =IF(ISNUMBER(Sheet1!B2:K2),SUM(Sheet1!B2:K2),"")
and D1 has the formula=IF(ISNUMBER(Sheet1!B2:K2),COUNT(Sheet1!B2:K2),"")

Then how can you make a IF(ISNUMBER for the calculation of C1/D1?
When I try =IF(ISNUMBER(C11),C1/D1,"")
I get a blank cell even when there are values in C1 and D1.
Any idea why this is and how I can alter the expression to make it work?

[Digita]

When you include a range (ie a group of cells) in the function ISNUMBER, you need to enter the formula by pressing Ctrl, Shift & ENTER at the same time to make it an array formula.

I guess you are trying to calculate the average. If so try this array formula:

[vba]=IF(ISNUMBER(Sheet1!B2:K2),AVERAGE(Sheet1!B2:K2),"")
[/vba]Don't forget to hit Ctrl, Shift & ENTER at the end.

Alternately, use this slightly more efficient formula:

[vba]=IF(ISNA(AVERAGE(Sheet1!B2:K2)),"",AVERAGE(Sheet1!B2:K2))[/vba]

Hope this helps.

[Bob Phillips]

Doesn't this do it

=IF(SUM(B2:K2)=0,"",AVERAGE(B2:K2))

[rahullambe]

=IF(ISNUMBER(B2:K2),(SUM(B2:K2)/COUNT(B2:K2)))

[bluetide76]

Oops yeah I figured it out yesterday. I used basically the same formula as rahullambe posted. I was grabbin data from another sheet and doing an average calculation with the possibility that the data may not be there. Thanks.

[Bob Phillips]

Seeing as how AVERAGE ignores blanks and text, can you explain to me why you don't use AVERAGE?

[bluetide76]

Because I was taking the average of a row that may or may not have data in it. If you take an average of a blank row it gives you a D/V error. I wanted to make sure it was a N/A error so it wouldnt graph the point. There might be other ways around it, I'm very new to it all but thats the method I chose. I also was taking range for another column.

[Bob Phillips]

Yes, I understand you want to test if it has ANY data, but once that is determined, why use SUM and COUNT instead of AVERAGE. You said you figured out the same formula as rahullambe, namely

=IF(ISNUMBER(B2:K2),(SUM(B2:K2)/COUNT(B2:K2)))

but that is the same as

=IF(ISNUMBER(B2:K2),AVERAGE(B2:K2))

which is better aesthetically, efficiency and as documentary.