• 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.

If statement problem

S

Scouse

New Member
Hi,


I get a zero as the result of this formula when I was expecting the answer Yes (my test answer)


Here is the formula


Code: 
=(IF(C5<=5,(5),IF(B$46<0,"YES","No")))


Data

C5 = 6

B46 = -78


Now in the cell the formula is in, lets say B5 returns 0 if the first IF statement is met. When I was expecting it to say YES


If I check the answer using Function Argument the answer is returned as YES!!!!!
 
Hi,


Is this the answer you want?


=IF(AND(C5<=5,B46<0),"YES","NO")


or may be this


=IF(OR(C5<=5,B46<0),"YES","NO")
 
Thanks for your help, but am I still getting a zero in col B when I use the formula

'=IF(OR(C5<=5,B$46<0),"YES","NO")'

Here is the data. In column C I am using the following formula '=ROUND(SUM(F$3 * D5),0)' in order to multiply the weighting in col D X 150, the number I get in C is then used in B to work out the actual number of items. For my test instead of a figure I am using Yes and No, so if the total number is less than zero, which in this case is as its -78, I should get YES as the answer. But all I get is zero. Very confusing?

A B C D

Branch # Req Rounded Weighting

BI 0 6 0.04


Total -78
 
Hi Scouse,


Can you post a sample of your workbook?


http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Scouse

Do you have Calculation turned to Manual?

Press F9 does it recalculate?
 
Scouse

You failed to mention you had a Circular Reference error in your spreadsheet

Your Cell B46 is trying to add up the values in Column B, which in turn is reliant on cell B46.

Having the circular reference stops further processing of the formula and hence your result of Zero.

What your trying to do can't simply be done as you are trying to do it, your logic isn't quite right


If your Column B5 was
Code: 
=IF(C5<=5,"YES","NO")

and

B46: =F3 - COUNTIF(B5:B45,"Yes")

Does that do what you want ?


As you currently have it

B46 will never be less than 0 unless Provided is less than 41 and all the cells B5:B45 are No
 
Hui,


Thanks for explanation, now I can understand the problem I am having, no wonder it did not work correctly. Still new to excel formulas.
 
Hi Scouse,


It's good to hear that you're beginning to understand these things, if you want to learn more take a look at these sites


http://chandoo.org/forums/topic/share-your-favorite-excel-sites
 
You must log in or register to reply here.
Back
Top