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

Applying"IF" to column in table

P

PipBoy808

Member
I'm trying to apply an "IF" function to a column in a table of figures. Essentially I want to say:

If the value of any of the cells in this column is 0, insert the word "Closed". If the value is anything other than 0, then leave the value unchanged.

So for cells in column O it should say =IF(O4:O15331=0,"Closed","") or something similar? I've been having trouble getting it to work.
 
Hi ,

Can you clarify whether you will use the formula in just one cell , or are you going to copy it down to every cell in the column , from row 4 through row 15331 ?

If it is only in one cell , then use the following :

=IF(OR(O4:O15331=0),"Closed","")

entered as an array formula , using CTRL SHIFT ENTER ; if any cell is blank , this will also be taken as 0 , and the resulting output will be Closed.

If it is a formula in row 4 , which will be copied down , till row 15331 , then use the following :

=IF(O4=0,"Closed","")

Each row formula will look at the corresponding cell in column O ; if the cell is either blank or has 0 in it , the resulting output will be Closed.

Narayan
 
I want to use the formula in all 15000 rows.

I entered the second formula in cell O4, then Copy > Paste Special > Formulas into the other 15000 rows and it turned them all to 0. Any reason that this might be happening?
 
Hi ,

There is some confusion !

The formula you initially posted referred to cells in column O ( O4:O15331 ) ; therefore , you cannot use this formula anywhere in column O , specifically in the range O4:O15331.

You can only use this formula in some other column.

In fact , you should have got a circular reference message.

Narayan
 
Hi PipBoy808,

The above formula mentioned by Narayan should work..

Can you check the formatting of the cells? are those numbers in text format? If so you will need to reformat them to numbers to make the formula work...
 
PipBoy808,

Maybe I overread your above statement...:(

You cannot use the formula in the formula range itself...This must be used in some other Column to trigger the impact and validate the If function...:)
 
Hi there,
Are the values in column O (O4:O15331) a result of another function? So for example in cell O4 something like:
Code: 
=SUM(A4:N4)

If this is the case, we can build on that function.

A sample file would help us :)
 
Yes I was getting a circular reference warning! OK, I think the best thing to do would to be to start over entirely. We can solve this! Allow me to rephrase my initial post:

I have a very large spreadsheet with 17 columns and 15000 rows. The entire thing has been organised into a table, so there are filters at the top of each column.

There is one column, column O, which contains values between 0 and 1 million over 15000 rows. I want to insert a function or conditional formatting or anything that will change the "0" values in column O to the word "Closed" and will leave other values exactly as they are. So, in column O, I'd like:

Cell value = 0 = change to "Closed"
Cell value = 235000 = do absolutely nothing
Cell value = 750000 = do absolutely nothing

I thought that the IF function might solve this, but perhaps conditional formatting would be best. I'm not sure. Your help is appreciated!
 
Hi ,

CF will not change the value itself ; it can change what the cell displays , that is all. Thus even if the value in the cell is 0 , you will see that the cell displays the text Closed ; is this acceptable ?

If you want the cell value itself to change , then you will have to use VBA.

Narayan
 
Hi,

I think you should give the below a shot:
1. In Col.P insert the formula =if(O4=0,"Remove","") and then drag this upto your data.
2. Now use autofilter and select the ones that have the value as "Remove"
3. Now select data in Col.O and select visible only by using ( Alt+ ; ) and use replace 0 with "Closed"
4. Then remove the autofilter and validate your results...

Hope this helps....:)
 
1. In Col.P insert the formula =if(O4=0,"Remove","") and then drag this upto your data.
Click to expand...

You lost me at this bit. What do you mean drag this up to your data? Other than that, I understand exactly what you mean and figure it might work. I'm using a dummy spreadsheet with fewer values as the other one was taking too long to compute. Now, column A has about 20 cells with values and column B contains the word 'remove' in every cell that is adjacent to a '0' in column A.
 
CF will not change the value itself ; it can change what the cell displays , that is all. Thus even if the value in the cell is 0 , you will see that the cell displays the text Closed ; is this acceptable ?
Click to expand...

Yes this is acceptable! So long as the word "Closed" is what is displayed, that's fine. What CF formula would allow this to happen?
 
You need to drag the data down to 20 rows in this case, or in other words you need to copy paste special formulas the same...:)​
Click to expand...

I don't think that will work. I'm getting a #REF! error because it's a circular reference.

What about conditional formatting changing the display to the word "Closed" even if the value is still 0? That would be acceptable.
 
Hi ,

The cell format to be applied can be something like this :

#,##0;-#,##0;"Closed"

The first section ( before the first semi-colon ) refers to the format to be applied to positive numbers ; the second section ( between the first semi-colon and the second ) refers to the format to be applied to negative numbers ; the third section refers to the format to be applied to zero values ; there is a fourth section which refers to the format to be applied to text data , but that is not applicable here since your data is entirely numeric.

Narayan
 
The cell format to be applied can be something like this :

#,##0;-#,##0;"Closed"
Click to expand...

I'm afraid that doesn't work. Am I meant to be putting in something to replace the '#' in the formula or would it be alright to paste it exactly?

I'm going to look for a VBA solution to all of this.
 
Hi ,

You are supposed to select the data range , right-click , and select Format Cells , select Custom , and enter this.

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