• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

zero or no zero

A

ahhhmed

Member
Hi,

I have this formula:

=IF(AND(C1>=0;C1<5);A1;"")

It works fine for me but there is a minor setback: It calculates the empty cells as zeros.

How can I adjust it so that it won't calculate the empty cells as zeros? Only when I put a zero (0) in a cell, it should be calculated.
 
=IF(LEN(C1)>0,IF(AND(C1>=0,C1<5),A1,""),"")


or


=IF(AND(C1>=0,C1<5,LEN(C1)>0),A1,"")
 
Thanks dear,

the formulas did not do the required task. They returned the numbers that are less than 5 to 0
 
As an alternative to checking the length of the string, you can also use the ISNUMBER formula:


=IF(AND(C1>=0,C1<5,ISNUMBER(C1)),A1,"")


Based on your most recent response, it looks like you do not want C1<5 to be a condition? Based on your original post, that's included, which is why vaskov17 put it in there


If what you're truly trying to do is "Return A1 when C1 is 0; otherwise return blank", then this should work for you:

=IF(AND(C1=0,ISNUMBER(C1)),A1,"")
 
One of the easiest solutions to this issue I have found is to use a nested if function:


=IF(C1="","",IF(AND(C1>=0,C1<5),A1,""))


Or using durheim's soultion above you could nest the ISNUMBER() function in the first if and then your condition afterwards:


=IF(ISNUMBER(C1),IF(AND(C1>=0,C1<5),A1,""),"")


Hopefully this helps.
 
Dear all,

All ideas are great, however I think that my problem is much more simpler than this:

Column D has the formula of =sum(A:C)

This returns the value of 0 if the cells A:C are empty.

I need to have the average at the end of col. D, but the program counts all the cells that have 0 resulting from empty A:C

I want it to only calculate the cells that result from numbers even if these numbers are 0's , but nut from empty cells.

I hope it is clearer now.

I appreciate your concern and cooperation
 
Hi ahhhmed, sounds like all you need to use in column D is the averageif formula. This works in a very similar to the countif, sumif formulas, e.g. try =AVERAGEIF(A1:C1, ">0", A1:C1). This will only take into account the values that are greater than zero...does this resolve your problem?


Prem
 
Or u can use a counta(A:C). CountA counts the blanks. SO you could use Sum(A:C)/(CountA(A:C)-Count(A:C))
 
You must log in or register to reply here.
Back
Top