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

Problems with an If statement.

S

Shoebird

Member
First of all, thank you in advance for looking at this question because I feel silly posting it because I have done thousands of If statements in excel and this time I have had an error that is bizarre to me.
The following is a copied from the spreadsheet.

=IF($B8="","",IF(OR((C8-E8+(E8+D8-G8-F8)*(H8/I8)+(G8+F8)+AL8)/J8<($AM$4-3*$AM$1),(C8-E8+(E8+D8-G8-F8)*(H8/I8)+(G8+F8)+AL8)/J8>($AM$4+3*$AM$1)),$AM$4,(C8-E8+(E8+D8-G8-F8)*(H8/I8)+(G8+F8)+AL8)/J8))

When I step through the statement through Evaluate Formula, I get the anticipated false statements for the first two conditions and the evaluated True statement is 1.61371126062468. The weird thing is as I continue to step through I get the following:
IF(FALSE,#N/A,IF(FALSE,#N/A,1.61371126062468))

Continuing
IF(FALSE,#N/A,1.61371126062468)

And finally
0

Why when I run the script would it be "0" instead of "1.61371126062468".

Thanks all!
 
Additional information.
If I "F9" through the process starting with the False conditions, the equation works.

=IF($B8="","",IF(OR(FALSE,FALSE),1.62484510966994,1.61371126062468))
Stepping through
=IF($B8="","",1.61371126062468)
=1.61371126062468
 
Hi ,

Good for you that you have done this troubleshooting ; it looks like it is more a problem of the cell format than the IF statement itself.

However , before this conclusion can be reached , can you do one thing more ?

Since the segment :

(C8-E8+(E8+D8-G8-F8)*(H8/I8)+(G8+F8)+AL8)/J8

is used in multiple places within the formula , it is an easy candidate for a helper cell ; where ever your result is showing as 0 , just enter this segment in a separate column , and change your IF formula to :

=IF($B8="","",IF(OR(helpercell <($AM$4-3*$AM$1),helpercell >($AM$4+3*$AM$1)),$AM$4,helpercell))

and see what you get.

Narayan
 
Thank you Narayan for the tip but I have a quick question. When you say, "helpercell", are you suggesting that I create a name formula under Name Manager for this function?

If I do that, I will have an issue with the rest of the work sheet because I am trying to "clean up the data" and the 3*$AM$1 is a standard deviation that I am referencing. It is my intention to get this one cell to work and then double click in the corner to paste the function throughout the column. Currently, this equation would be performed about three thousand times.

If I do a name manager function, I don't know how to create a single name that would reference items based upon the current row.
 
Hi ,

helpercell was just a placeholder , since I did not know where you would be inserting this ; if your helper column is column BA , then use BA8 for the formula on row 8.

There is no need to use named ranges for this test purpose.

Narayan
 
Thank you again. Unfortunately, this is a simplified equation. Using the helpercell function is the only way that I could have tackled this problem otherwise, the equation would have well over a hundred components in the numerator and denominator each.

Thank you again for sharing your brilliance Narayan!
 
Hi ,

I was not suggesting that you change all your formulae to take the helper column into consideration.

I wanted to know what is causing the problem ; changing the formula would give us an idea of what the intermediate result is ; also , by simplifying the IF statement , it would probably help us understand what is going on.

Obviously , if we understand the problem , the solution will have nothing to do with using a helper column.

Narayan
 
Thank you again Narayan.

I have worked through this problem a number of different ways, tried other cells as well and I am finding that I am having similar issues with other cells. Let me ask a few questions and see if it stimulates some thoughts:
  • Is there a limit to how many calculations that should be performed after a <,>, or =
When I substitute
=IF($B8="","",IF(OR((C8-E8+(E8+D8-G8-F8)*(H8/I8)+(G8+F8)+AL8)/J8<($AM$4-3*$AM$1),(C8-E8+(E8+D8-G8-F8)*(H8/I8)+(G8+F8)+AL8)/J8>($AM$4+3*$AM$1)),$AM$4,(C8-E8+(E8+D8-G8-F8)*(H8/I8)+(G8+F8)+AL8)/J8))

With

=IF($B8="","",IF(OR((C8-E8+(E8+D8-G8-F8)*(H8/I8)+(G8+F8)+AL8)/J8<1.5,C8-E8+(E8+D8-G8-F8)*(H8/I8)+(G8+F8)+AL8)/J8>2.0),$AM$4,(C8-E8+(E8+D8-G8-F8)*(H8/I8)+(G8+F8)+AL8)/J8))

There is no issue. The problem seems to be with ($AM$4-3*$AM$1) or ($AM$4+3*$AM$1). Even when I evaluate the equation, it seems to work fine until the final resultant. But substituting gives me no issue.

Perhaps it is either gremlins in the machine or most likely operator error.

Thank you again Narayan
 
Hi ,

It would have been nice to know what values are present in AM4 and AM1.

In case these two cells have formulae in them , do not go by what Excel displays in the cells ; place the cursor in each cell , press F2 and F9 to see what is the final result of the formula in that cell ; press ESC to escape from EDIT mode.

Narayan
 
Okay. I found the problem but I am no closer to the answer. Here we go.

Cell AM1 is the calculated standard deviation ST.DEVP=0.1014
Cell AM4 is the average of the cell range 1.6248

When I try to calculate +/- 3 deviations, I am using
=AM4-(3*AM1) Which translates to 1.6248-3*0.1014. When I runt the script though, it returns 0. Why?
 
Hi ,

Which means your calculation mode in MANUAL ?

If so , this is very dangerous ! Never change the calculation mode to MANUAL , unless you are doing it using code , in which case you should be restoring it to AUTOMATIC very soon thereafter.

Narayan
 
You must log in or register to reply here.
Back
Top