excel IF statement

Hi all, im trying to do what i think is basic but cant seem to get it to blooming work.

So i want to make an IF statement that if E2 is blank then put N/A if it is not blank do nothing.

So i have =IF(E2="","N/A","")

From what i remember that's saying if e2=blank, then add N/A, else leave blank?

Or am i barking well up the wrong tree here.

That formula works in my Excel 2003

weird i get 0 ? im not sure can you put a formula and its outcome in the same cell?
because it works if i do =IF(E3="","NA","") because thats the cell below

Maybe insert a space as follows:

=IF(E2="","N/A"," ")

The problem with "" in a formula (or numerical cell) is that it is likely to be translated to 0, whereas you want a null text value.

Can i ask can i put this formula in the cell that im trying to display n/a?

Basically i am importing a .csv file with shipping addresses.
Some customers have filled out line 2 on shipping some haven't.
Now royal mail need to see something inside shipping column 2 being E.
So i was hoping to put an IF statement in the whole of column E with the above code, but i guess because the IF statement is about the E column it will bring an error because i guess you can have a IF statement in its own cell/column?

Hope that makes any kind of sense if not ill have to look at a company to make me a program or something.

Try:

=IF(ISBLANK(E2),"N/A","")

I get the following error.

Careful, we found one or more circular referances in your workbook that might cause your formulas to calculate incorrectly.
FYI: a circular referance can be a formula that refers to its own cell value, or refers to a cell dependent on its own cell value.

That's why im thinking i cant put an if statement in the cell/column i want changing?

Or what i can do is use B2 to hold the IF statement but to look at E column and change E not B?

Does the formula live inside the cell where you want the 'N/A' to be displayed? I may have misunderstood

Hobbsie said:

Can i ask can i put this formula in the cell that im trying to display n/a?

Basically i am importing a .csv file with shipping addresses.
Some customers have filled out line 2 on shipping some haven't.
Now royal mail need to see something inside shipping column 2 being E.
So i was hoping to put an IF statement in the whole of column E with the above code, but i guess because the IF statement is about the E column it will bring an error because i guess you can have a IF statement in its own cell/column?

Hope that makes any kind of sense if not ill have to look at a company to make me a program or something.

Click to expand...

Yes, you're getting the circular reference issue because you're trying to get a cell to refer to itself.

The best option might be to have a "Normalised Line 2" field and then use

=if(E2="","n/a",E2)

Where E2 is the cell with the .csv extract for Line 2.

This will basically create a new field that will have the Line 2 value from the extract, or "N/A".

You then use that field for your mail merge or whatever.

yes sorry the formula lives in the cell which i was trying to explain but im bad at explaining things. thanks ill give that ago. thanks all

Can i have a different cell house the formula? IE cell A2-10 will actully change cell E2-10?

This formula in E2 will display N/A or whatever you put in cell G2. I don't think you can put a formula into a box and then be able to add data to it and keep the formula

=IF(G2="","N/A",G2)

Thanks for all your help, just another question if i may!
Is there an easy way of changing text from in a cell.
What im after is if a cell says united kingdom to change it to GB and if a cell says france to change it to FR for example?
Is there any easy ways in doing this or do i need to go into coding?

Thanks

Either use CTRL-H (Find and Replace) to do it manually, or use an IF statement.

IF(E2="united kingdom","GB", if(E2="France","FR",E2))

Which will change the 2 values you mention above or leave the original values in place.

Any more specific values you want to change than that then you're probably best using a an ISERROR & VLOOKUP combination.

Exactly how you do that will depend on which Excel you're using as IFERROR was introduced in later Excel versions, but there are ways of doing it using IF and ISERROR in older Excels.

You could use a VLOOKUP to reference a 'helper table'

Don't use a null string (i.e. "") to check if a cell is blank, it won't work under some conditions. Instead use the ISBLANK() function someone posted earlier in the thread.

This is a wrong question.

You are trying to add data to a cell if the cell has no data. This is what setting a formula to set a value in a cell is attempting. As you might imagine, that's not going to work.

What you really want to do, is format the cell based on the data within it (in this case a blank). You've started down the road of setting values when you should really start down the road of how to do conditional formatting. Unfortunately, whilst I can get you close to what you want and in theory it ought to work, I can't quite get "N/A" to appear in the cells. I'll explain the process though as I've already tried to do this and might as well share how close I got.

Select one or more cells. Click on Conditional Formatting (I'm assuming a current version of Excel which if you have a subscription model you should have). What you want is a bit unusual so don't select any of the commonly suggested conditional formats, instead go straight to "New Rule" at the bottom of the list.

This brings up a dialogue where you can set formats as you wish. Let's do this in stages to avoid mistakes as there are a couple of places where you could easily go wrong. First select "Use a formula to determine which cells to format" as the rule type.

Find a blank cell in your spreadsheet that will always be blank. In this example, I'm just going to use $A$2.

For the formula enter the following: ISBLANK($A$2). Do NOT click Okay yet! You still have to define the formatting.

Click on the Format box or option to bring up the choice of how to format the cell. Go to "Custom" at the bottom of the list and enter the bolded following in the field at the top and nothing else: "NA" . Note that the quote marks are to be entered (which is why I have bolded them as well).

Click Apply and you should see NA appear in the selected cells. This is because the formula is evaluating to True and it's therefore using a custom number format for display. This is the key difference between what we're doing here and what you were trying in the first post: we are NOT entering a value into the field, we are setting a formatting rule that affects how the cell is displayed. The former can never work conceptually.

Regrettably, whilst it OUGHT to work changing the formula to the current cell (because this formula is used for formatting, not for entering data into the cell), when I do so, it wont display the text. It will still do other formatting so if you want the cell to turn blue or something when it doesn't have a value, you can do that. But something must be preventing it displaying text. Sorry.