# Thread: If Function Trouble (Office 97)

1. ## If Function Trouble (Office 97)

I use an IF function to display a constant depending on a value in another cell being ">0". That value is derived from another IF statement in another (second) cell. The first cell always displays a value, even if there is no value in the second cell. If I delete the IF statement in the second cell, the first cell then displays the correct results. Why is the IF statement in the second cell interpreted as "a value greater than zero"?

2. ## Re: If Function Trouble (Office 97)

What are the values in the cells and what are the IFs.
Is the second cell text - (null string ("") is text)?
If you compare different variable types, text always > a number (it compares both as text),
a blank cell = 0 (so is not ">0")

Steve

3. ## Re: If Function Trouble (Office 97)

The first cell is a constant; happens to be "miles". The second cell is "dollars" entered as a result of the IF statement in that cell. I just changed the first cell IF statement to call not for the constant but rather just the value displayed in the second cell. That resulted in a correct response in that there was no value displayed where there was no value in the second cell, and a (\$)value displayed where there was a value in the second cell. But I don't want the dollar value, I just want a fixed number specified in the IF statement in the first cell.

4. ## Re: If Function Trouble (Office 97)

I'm sorry but I don't follow your explanation at all. Could you post the formulas and what values you expect?

Steve

5. ## Re: If Function Trouble (Office 97)

Could you upload a sample workbook showing the problem. I can follow your explanation well enough to understand what the problem is.

6. ## Re: If Function Trouble (Office 97)

Steve
I will attach a sample of what I am doing. The number "6" in the "RX Miles" I want to appear only when a "\$" value is shown in a cell in the "Drug" column.
Thanks
John

7. ## Re: If Function Trouble (Office 97)

Legare
Can you pick up the sample of my problem from my response to skckapr (Steve)?
John

9. ## Re: If Function Trouble (Office 97)

Sorry about that. Here it is again.

10. ## Re: If Function Trouble (Office 97)

Change the formula in column I to:

<pre>=IF(G3<>" ",6,"")
</pre>

11. ## Re: If Function Trouble (Office 97)

I anddition to Legare's suggestion, another suggestion to eliminate the need to have a separate formula in each column:
Insert a new row 2 (row can be hidden)
In D2 - G2 enter the values for the comparison in the column (d, e, i, g, respectively)

Then in D4 enter:
=IF(\$B4=D\$2,\$C4," ")
This formula may then be copied from D4:G11.

Steve

12. ## Re: If Function Trouble (Office 97)

It works. Thank you all. One question though. What is the difference between quotation marks with and without a space between them as Legare's suggestion contains?
John

13. ## Re: If Function Trouble (Office 97)

The length of a string with no space = 0, the length of the string with 1 space = 1. When you compare "" to " " they are not equal since they have different lengths. The "null string" ("") is a string that has no characters so is not the same as a string composed of a space (" ").

Steve

14. ## Re: If Function Trouble (Office 97)

I put the space between the quotes because your formula in column G has a space between the quotes. Your formula in column put a string with one space into the cell in G. That is not the same and not equal to a null string (""). If you want to check for any null or any number of blanks, then you could use:

<pre>=IF(TRIM(G3)<>"",6,"")
</pre>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•