Excel if function question 1

[esu4edp]

Im having problem with the IF function to return a value or 1 or 0. If a certain cell has a value no matter what the value I want it to be "1" if that cell has nothing typed in it I want the value to be "0". I tried several things but nothing works or I get an error.

Basically I'm hiding a column to be counted. I want it to count 1 if a column beside it has any value at all. If nothing is typed in to that column I want it turn a value of 0 and then I will use the 1's & 0's to make another count.

 

[RoyVidar]

Hi!

Have you also tried something like this:

[tt]=If(A1<>&quot;&quot;,1,0)[/tt]

HTH Roy-Vidar

 

[TonyJollans]

Hi esu4edp,

The IF function will return whatever values you tell it to dependent upon the evaluation of the condition you specify.

If you want to test cell A1 as you describe, this should do it ..

=IF(A1=&quot;&quot;,0,1)

But, if what you really want is a count of cells (let's say in column A) with contents it would be much easier just to put this where you want the answer ..

=COUNTA(A:A)

Enjoy,
Tony

 

[BenRowe]

I think Tony meant to say:

=COUNT(range) NOT COUNTA

 

[esu4edp]

i tried this one, it works, but if you take away the value of the cell then the 1 doesnt change to a 0 =If(A1<>&quot;&quot;,1,0)

I'll try to explain this better.

I have a spredsheat with vacant apartments on it, it shows the address and how much it is and what work has been done on it, etc. There are 4 categories on the sheet; vacant not assigned to a new person, vacant assigned to a new person, on notice to vacate, currently occupied but already rented when they leave. Each category has 20 columns to list apartments. So from one day to the next things change. If there is info in column &quot;B&quot; for example, I want a 1 in my hidden column(A) and far as counting the number of vacants. If that vacant it rented and I take it off the list, i want it to show 0 in my hidden column because there is no info typed in. Hope this explains better?

 

[RoyVidar]

Hi!

After changing a value (and hitting ENTER or TAB), it should recalculate.

Try hitting F9 if not, which should perform a &quot;manual&quot; recalculation.

If the latter, then you could perhaps check the calculation options in Tools | Options - and the Calculation tab, se that &quot;Automatic&quot; is selected.

HTH Roy-Vidar

 

[esu4edp]

I've tried all of these, and a few of my own, basically it comes down if its null or blank i want a 0, i even tried a &quot;if null&quot; but that was an error. if something is typed in then a 1

 

[TonyJollans]

Hi BenRowe,

I DID mean COUNTA (which counts non-blank cells), not COUNT (which counts cells with numeric values)

esu4edp,

I'm afraid your explanation doesn't really help. Are your cells really empty, or do they perhaps contain spaces? If so, this change might do the trick ..

=IF(TRIM(A1)=&quot;&quot;,0,1)


Enjoy,
Tony

-----------------------------------------------
A very Merry Christmas to all Tek-Tippers
----------------------------------------------- [santa2]

 

[Bowers74]

You might want to try this:

=IF(ISBLANK(A1),0,1)

Although TonyJollans' solution with the =COUNTA(A:A) is better because it will count all of the cells containing values in column A. Then you could use it for all of your columns. I'm not quite sure I understood how you have your workbook set up, but with a little work it will do what you want.



Happy Ho Ho!!! [Cheers]

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[esu4edp]

=IF(TRIM(A1)=&quot;&quot;,0,1)

im not sure what i was doing, but his works, thanks tony

 

[Bowers74]

If the cells contain spaces, they may look blank, but Excel doesn't &quot;count&quot; them as blank, so my suggestion with =IF(ISBLANK(A1),0,1) won't work in that case either.

I would use Tony's anyway!



Happy Ho Ho!!! [Cheers]

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[KenWright]

Hi esu4edp - Hopefully you are a happy bunny now that it works, and if so then you may want to consider hitting the &quot;Thank TonyJollans..... post&quot; link, which will not only give Tony a message of thanks, but will also flag the post for other users that may have a similar problem, and let them know that it worked. I'll give Tony a Star for you anyway though, just in case.

Best Wishes
Ken................

----------------------------------------------------------------------------
Seasons Greetings and Very Best wishes to all

----------------------------------------------------------------------------

 

[TonyJollans]

Thanks, Ken


Enjoy,
Tony

-----------------------------------------------
A very Merry Christmas to all Tek-Tippers
----------------------------------------------- [santa2]