If function won't let me return the correct result-PLEASE HELP!!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I know the if function is written correctly.
I've tried changing the format of the cells.

I still can't get the if-statement to work properly. It is supposed to return " " if a cell is <1, but it keeps returning the number.

=If(k5<1," ",k3)

This is the simple function. The problem is when the cell IS <1, it STILL returns k3.

CAN ANYONE PLEASE HELP???
 
Your number in K5 is probably Text, either entered after the cell was
formatted as text, or the result of a text function, e.g.:

=IF(TRUE,"0",FALSE)

If the former, reformat the cell and reenter the value. If the latter,
remove the quotes from around the value.
 
Hi Tom!

Two immediate possibilities:

1) Is calculation set to automatic?

Tools > Options > Calculation Tab
Check "Automatic"
OK

2) Are your entries in K5 Text:

Check using:

=ISTEXT(K5)
It should return FALSE
 
Probably K5 contains a character string that only looks like a number.
If so, then =COUNT(K5) will return 0 instead of 1.

Reformatting K5 will only change the format, not the value (i.e. will
not convert from a string to a number). Either re-enter after
formatting as a number, or copy an empty cell and Edit|Paste Special|Add
into K5.

Jerry
 
I FOUND THE PROBLEM -- FOR SOME REASON, THE DATA I COPIED FROM ANOTHER SOFTWARE INSERTED A SPACE IN THE CELL. OF COURSE I DIDN'T SEE IT AND THAT'S WHY IT WOULDN'T WORK.

IF THERE SOME SORT OF SETTING (BESIDE DOING A "REPLACE" OF ALL THE CELLS) I CAN DO TO MAKE SURE NEXT TIME I COPY THE DATA INTO EXCEL, IT DOESN'T ENTER IN A SPACE?

THANKS FOR YOUR RESPONSES!!
 
Hi Tom!

Please don't use all upper case. It's considered "shouting" and is
more difficult to read especially if your first language isn't the one
used.

Rather than use REPLACE try:

Select an empty cell
Copy
Select the data to be converted
Edit > Paste Special > Add > OK
 
Thank you Norman.

I apologize. This is only my second time using this type of help or discussion group. Thank you very much for your help and for the information on the proper etiquette.

Can I ask, is this basically a place for anyone to go to and ask for help and for anyone to give help? There aren't people "assigned" to answer questions from Microsoft on this, is there? I think it's all just user based, correct?

Thanks again, Tom
 
Hi Tom!

No problems! This is very tolerant newsgroup compared with many.

It's known as a peer to peer newsgroup where anyone can submit
problems and / or solve or assist in solving. Although many of the
regulars have their specializations there is no assigning of
questions. If you think you can assist, just go ahead.

Microsoft "only" host the newsgroups and only rarely do we see their
participation.

For details that help new users see:

Chip Pearson:
http://www.cpearson.com/excel/newposte.htm

David McRitchie:
http://www.mvps.org/dmcritchie/excel/posting.htm
 
Back
Top