IF formula problems with "0"

  • Thread starter Thread starter jg53
  • Start date Start date
J

jg53

I have been trying to create a formula that returns the following:

If(B4:b6<>0,sum(b4:b6)/3-36, sum(b4:b6)/2-36)

The calculations keeps getting confused by the 0 if there is actually
0 in the cell. for instance if the cells are:
b4: 0
b5:40
b6:48

the formula returns a value of of -7. The answer should actually be
since there was a 0 in one of the cells.
Can anyone help. this has been driving me craz
 
Hi jg!

Enter the formula as an array formula by pressing and holding down
Ctrl + Shift and then pressing Enter.
 
The issue is the first part, where you are testing

B4:B6<>0.

This returns an array of values such as TRUE,FALSE,FALSE
because you are testing each cell in the range to see if
is not equal to 0. But if you don't write it as an array
formula, it won't work. Fortunately, you don't even need
an array formula here. You could simply use:

=IF(COUNTIF(B4:B6,0)=0,....

But, if you want to use an array formula, you could use:

=IF(SUM(N(B4:B6<>0))=3,....

Since it's an array formula, you have to press
ctrl/shift/enter for it to work.

HTH
Jason
Atlanta, GA
 
I tried that too. I have tried it several different ways. When thi
formula is in the worksheet without the array, I get a ###### error.
 
is this the formula using countif?
=IF(COUNTIF(B4:B6,0)=0,SUM(B4:B6)/2-36,SUM(B4:B6)/3-36)

If so, the answer comes up as 2
 
Perfect! I'm so excited it works. I never thought to use a count in
formula. I have struggled with this for two weeks now. I had alread
purchased John Walkenbach's Excell 2003 formulas book and was poundin
through that and couldn't get anything to work. Of course, I neve
really understood the value of count, but I do now. Thank you ver
muc
 
The actual *fact* is that your formula *works*, when array entered,
*AND*,
when the data is keyed in, as I, and I'm sure the others did, when checking
your formula!

SO, the next question should be:
*WHAT TYPE* data is in the actual cells, and/or *HOW* (formulas, paste,
import) did it get there?
 
created the formula through the formula throught the insert functio
wizard. Not sure what you mean by using the *AND*. I'm not an exper
with excel and am just learning the deep parts (function uses, etc).
I'm used to simple calculations of sum, divide, etc. I just learne
about the arrays in a book I purchased last week so I was not eve
aware they existed before then. Sorry if I seem a little confusing
 
You're welcome.
Did it come with a CD, perhaps you can play it when
you're sleeping. <grin> Maybe John's next project.
 
It's a mute point now, since you have your solution.
The *AND* was meant to emphasize that probably all the testing that
validated your formula, was done with *true* numeric data, entered directly
from the keyboard.

Many problems (disagreements) arise when the actual data used in the
worksheet itself, is *not* the same type of data that was used in the
testing of the formulas and procedures by the folks in the NGs.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------


created the formula through the formula throught the insert function
wizard. Not sure what you mean by using the *AND*. I'm not an expert
with excel and am just learning the deep parts (function uses, etc).
I'm used to simple calculations of sum, divide, etc. I just learned
about the arrays in a book I purchased last week so I was not even
aware they existed before then. Sorry if I seem a little confusing.
 
Hi David!

Re: "Did it come with a CD"

All John's books come with a CD but I can't get them to work in my
Walkman. Pity! I hear that he's good with the guitar.
 
Back
Top