Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 66
Default Nested IF that will ignore blank (empty) cells

Hi,
I have a simple nested IF function (Excel 2003) which compares numbers in
two cells, then returns a word depending on whether one number is greater
than, equal to, or less than the other:

=IF(F3G3,"Yes",IF(F3=G3,"Equal",IF(F3<G3,"No")))

Either (or both) of the compared cells might contain the number 0 (zero) in
which case I want the IF function to display the appropriate word.

However if the compared cells are blank (have no data in them), my
unsophisticated IF function thinks that F3=G3 and returns the word "Equal".

How can I get the IF function to remain blank when my compared cells (F3 &
G3) are blank?

Cheers,
Steve Sharpe.
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1
Default Nested IF that will ignore blank (empty) cells


Struggling in Sheffield;221336 Wrote:
Hi,
I have a simple nested IF function (Excel 2003) which compares numbers
in
two cells, then returns a word depending on whether one number is
greater
than, equal to, or less than the other:

=IF(F3G3,"Yes",IF(F3=G3,"Equal",IF(F3<G3,"No")))

Either (or both) of the compared cells might contain the number 0
(zero) in
which case I want the IF function to display the appropriate word.

However if the compared cells are blank (have no data in them), my
unsophisticated IF function thinks that F3=G3 and returns the word
"Equal".

How can I get the IF function to remain blank when my compared cells
(F3 &
G3) are blank?

Cheers,
Steve Sharpe.


Try=if(and(g3="",f3=""),"",IF(F3G3,"Yes",IF(F3=G3 ,"Equal",IF(F3<G3,"No")))


--
Pecoflyer

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows file
upload -faster and better answers

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=61004

  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 144
Default Nested IF that will ignore blank (empty) cells

does this do what you want

=IF(OR(F3="",G3=""),"",IF(F3G3,"Yes",IF(F3=G3,"Eq ual",IF(F3<G3,"No"))))

if any of the cells are blank, it will remain blank.

HTH

--
Pls provide your feedback by clicking the Yes button below if this post have
help you. This will help others to search the archives for result better.


Thank You

cheers, francis



"Struggling in Sheffield" wrote:

Hi,
I have a simple nested IF function (Excel 2003) which compares numbers in
two cells, then returns a word depending on whether one number is greater
than, equal to, or less than the other:

=IF(F3G3,"Yes",IF(F3=G3,"Equal",IF(F3<G3,"No")))

Either (or both) of the compared cells might contain the number 0 (zero) in
which case I want the IF function to display the appropriate word.

However if the compared cells are blank (have no data in them), my
unsophisticated IF function thinks that F3=G3 and returns the word "Equal".

How can I get the IF function to remain blank when my compared cells (F3 &
G3) are blank?

Cheers,
Steve Sharpe.

  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 66
Default Nested IF that will ignore blank (empty) cells

Hi,
Sorry but this doesn't work.

"Pecoflyer" wrote:


Struggling in Sheffield;221336 Wrote:
Hi,
I have a simple nested IF function (Excel 2003) which compares numbers
in
two cells, then returns a word depending on whether one number is
greater
than, equal to, or less than the other:

=IF(F3G3,"Yes",IF(F3=G3,"Equal",IF(F3<G3,"No")))

Either (or both) of the compared cells might contain the number 0
(zero) in
which case I want the IF function to display the appropriate word.

However if the compared cells are blank (have no data in them), my
unsophisticated IF function thinks that F3=G3 and returns the word
"Equal".

How can I get the IF function to remain blank when my compared cells
(F3 &
G3) are blank?

Cheers,
Steve Sharpe.


Try=if(and(g3="",f3=""),"",IF(F3G3,"Yes",IF(F3=G3 ,"Equal",IF(F3<G3,"No")))


--
Pecoflyer

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows file
upload -faster and better answers

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=61004


  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 66
Default Nested IF that will ignore blank (empty) cells

Hi,
Sorry but this doesn't work.

"xlmate" wrote:

does this do what you want

=IF(OR(F3="",G3=""),"",IF(F3G3,"Yes",IF(F3=G3,"Eq ual",IF(F3<G3,"No"))))

if any of the cells are blank, it will remain blank.

HTH

--
Pls provide your feedback by clicking the Yes button below if this post have
help you. This will help others to search the archives for result better.


Thank You

cheers, francis



"Struggling in Sheffield" wrote:

Hi,
I have a simple nested IF function (Excel 2003) which compares numbers in
two cells, then returns a word depending on whether one number is greater
than, equal to, or less than the other:

=IF(F3G3,"Yes",IF(F3=G3,"Equal",IF(F3<G3,"No")))

Either (or both) of the compared cells might contain the number 0 (zero) in
which case I want the IF function to display the appropriate word.

However if the compared cells are blank (have no data in them), my
unsophisticated IF function thinks that F3=G3 and returns the word "Equal".

How can I get the IF function to remain blank when my compared cells (F3 &
G3) are blank?

Cheers,
Steve Sharpe.



  #6   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 5,934
Default Nested IF that will ignore blank (empty) cells

Can you describe "doesn't work" for us? What did you have in the cells, what
did the formula produce and what did you think the formula should have
produced? Telling us that would be far more helpful than just saying
"doesn't work".

--
Rick (MVP - Excel)


"Struggling in Sheffield"
wrote in message ...
Hi,
Sorry but this doesn't work.

"xlmate" wrote:

does this do what you want

=IF(OR(F3="",G3=""),"",IF(F3G3,"Yes",IF(F3=G3,"Eq ual",IF(F3<G3,"No"))))

if any of the cells are blank, it will remain blank.

HTH

--
Pls provide your feedback by clicking the Yes button below if this post
have
help you. This will help others to search the archives for result better.


Thank You

cheers, francis



"Struggling in Sheffield" wrote:

Hi,
I have a simple nested IF function (Excel 2003) which compares numbers
in
two cells, then returns a word depending on whether one number is
greater
than, equal to, or less than the other:

=IF(F3G3,"Yes",IF(F3=G3,"Equal",IF(F3<G3,"No")))

Either (or both) of the compared cells might contain the number 0
(zero) in
which case I want the IF function to display the appropriate word.

However if the compared cells are blank (have no data in them), my
unsophisticated IF function thinks that F3=G3 and returns the word
"Equal".

How can I get the IF function to remain blank when my compared cells
(F3 &
G3) are blank?

Cheers,
Steve Sharpe.


  #7   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 66
Default Nested IF that will ignore blank (empty) cells

Hi Rick,
The present nested IF I'm using:

=IF(F3G3,"Yes",IF(F3=G3,"Equal",IF(F3<G3,"No")))

works OK, it compares two numbers (in F3 & G3) and returns the word "Yes",
"Equal", or "No" depending on whether the number in F3 is greater than, equal
to, or less than the number in G3.

The problem with it is that if F3 & G3 have no data in them, it still
compares F3 & G3 to gives me the word "Equal". I need it to remain blank when
F3 or G3 is blank.

When I type in either of the two functions suggested:

=IF(OR(F3="",G3=""),"",IF(F3G3,"Yes",IF(F3=G3,"Eq ual",IF(F3<G3,"No"))))
&
=IF(AND(F3="",G3=""),"",IF(F3G3,"Yes",IF(F3=G3,"E qual",IF(F3<G3,"No"))))

Both functions just sit in the cell like I'd entered some text, the
spreadsheet doesn't seem to recognise them as functions. I don't even get a
function error message.

Cheers,
Steve.




"Rick Rothstein" wrote:

Can you describe "doesn't work" for us? What did you have in the cells, what
did the formula produce and what did you think the formula should have
produced? Telling us that would be far more helpful than just saying
"doesn't work".

--
Rick (MVP - Excel)


"Struggling in Sheffield"
wrote in message ...
Hi,
Sorry but this doesn't work.

"xlmate" wrote:

does this do what you want

=IF(OR(F3="",G3=""),"",IF(F3G3,"Yes",IF(F3=G3,"Eq ual",IF(F3<G3,"No"))))

if any of the cells are blank, it will remain blank.

HTH

--
Pls provide your feedback by clicking the Yes button below if this post
have
help you. This will help others to search the archives for result better.


Thank You

cheers, francis



"Struggling in Sheffield" wrote:

Hi,
I have a simple nested IF function (Excel 2003) which compares numbers
in
two cells, then returns a word depending on whether one number is
greater
than, equal to, or less than the other:

=IF(F3G3,"Yes",IF(F3=G3,"Equal",IF(F3<G3,"No")))

Either (or both) of the compared cells might contain the number 0
(zero) in
which case I want the IF function to display the appropriate word.

However if the compared cells are blank (have no data in them), my
unsophisticated IF function thinks that F3=G3 and returns the word
"Equal".

How can I get the IF function to remain blank when my compared cells
(F3 &
G3) are blank?

Cheers,
Steve Sharpe.



  #8   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 5,934
Default Nested IF that will ignore blank (empty) cells

That could happen if the cells were formatted as Text before you put the formulas in the cell. I'm still not entirely clear which formula you actually need (although I think it is the AND one), but try this to correct the problem. Put the formula into the cell, then Format the cell as General, then click into the formula in the FORMULA BAR (not the cell) and hit the Enter key. Doing that should convert the cell back to a formula which should then do what you want.

--
Rick (MVP - Excel)


"Struggling in Sheffield" wrote in message ...
Hi Rick,
The present nested IF I'm using:

=IF(F3G3,"Yes",IF(F3=G3,"Equal",IF(F3<G3,"No")))

works OK, it compares two numbers (in F3 & G3) and returns the word "Yes",
"Equal", or "No" depending on whether the number in F3 is greater than, equal
to, or less than the number in G3.

The problem with it is that if F3 & G3 have no data in them, it still
compares F3 & G3 to gives me the word "Equal". I need it to remain blank when
F3 or G3 is blank.

When I type in either of the two functions suggested:

=IF(OR(F3="",G3=""),"",IF(F3G3,"Yes",IF(F3=G3,"Eq ual",IF(F3<G3,"No"))))
&
=IF(AND(F3="",G3=""),"",IF(F3G3,"Yes",IF(F3=G3,"E qual",IF(F3<G3,"No"))))

Both functions just sit in the cell like I'd entered some text, the
spreadsheet doesn't seem to recognise them as functions. I don't even get a
function error message.

Cheers,
Steve.




"Rick Rothstein" wrote:

Can you describe "doesn't work" for us? What did you have in the cells, what
did the formula produce and what did you think the formula should have
produced? Telling us that would be far more helpful than just saying
"doesn't work".

--
Rick (MVP - Excel)


"Struggling in Sheffield"
wrote in message ...
Hi,
Sorry but this doesn't work.

"xlmate" wrote:

does this do what you want

=IF(OR(F3="",G3=""),"",IF(F3G3,"Yes",IF(F3=G3,"Eq ual",IF(F3<G3,"No"))))

if any of the cells are blank, it will remain blank.

HTH

--
Pls provide your feedback by clicking the Yes button below if this post
have
help you. This will help others to search the archives for result better.


Thank You

cheers, francis



"Struggling in Sheffield" wrote:

Hi,
I have a simple nested IF function (Excel 2003) which compares numbers
in
two cells, then returns a word depending on whether one number is
greater
than, equal to, or less than the other:

=IF(F3G3,"Yes",IF(F3=G3,"Equal",IF(F3<G3,"No")))

Either (or both) of the compared cells might contain the number 0
(zero) in
which case I want the IF function to display the appropriate word.

However if the compared cells are blank (have no data in them), my
unsophisticated IF function thinks that F3=G3 and returns the word
"Equal".

How can I get the IF function to remain blank when my compared cells
(F3 &
G3) are blank?

Cheers,
Steve Sharpe.



  #9   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 66
Default Nested IF that will ignore blank (empty) cells

Hi Rick,
I've had another look at this and found it's the cell being formatted for
"text" rather than "general" that's the issue. The expressions work when I
reformat the cell to general.
Many thanks for all your inputs.
Steve Sharpe.

"Rick Rothstein" wrote:

Can you describe "doesn't work" for us? What did you have in the cells, what
did the formula produce and what did you think the formula should have
produced? Telling us that would be far more helpful than just saying
"doesn't work".

--
Rick (MVP - Excel)


"Struggling in Sheffield"
wrote in message ...
Hi,
Sorry but this doesn't work.

"xlmate" wrote:

does this do what you want

=IF(OR(F3="",G3=""),"",IF(F3G3,"Yes",IF(F3=G3,"Eq ual",IF(F3<G3,"No"))))

if any of the cells are blank, it will remain blank.

HTH

--
Pls provide your feedback by clicking the Yes button below if this post
have
help you. This will help others to search the archives for result better.


Thank You

cheers, francis



"Struggling in Sheffield" wrote:

Hi,
I have a simple nested IF function (Excel 2003) which compares numbers
in
two cells, then returns a word depending on whether one number is
greater
than, equal to, or less than the other:

=IF(F3G3,"Yes",IF(F3=G3,"Equal",IF(F3<G3,"No")))

Either (or both) of the compared cells might contain the number 0
(zero) in
which case I want the IF function to display the appropriate word.

However if the compared cells are blank (have no data in them), my
unsophisticated IF function thinks that F3=G3 and returns the word
"Equal".

How can I get the IF function to remain blank when my compared cells
(F3 &
G3) are blank?

Cheers,
Steve Sharpe.



  #10   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 66
Default Nested IF that will ignore blank (empty) cells

Hi,
Thanks for your help, couldn't get it to work at first as the cell was
formatted for "text", not "general", so it thought I was entering text and
not a function.
Flippin' hard work this Excel business!
Thanks again,
Steve Sharpe.




"Pecoflyer" wrote:


Struggling in Sheffield;221336 Wrote:
Hi,
I have a simple nested IF function (Excel 2003) which compares numbers
in
two cells, then returns a word depending on whether one number is
greater
than, equal to, or less than the other:

=IF(F3G3,"Yes",IF(F3=G3,"Equal",IF(F3<G3,"No")))

Either (or both) of the compared cells might contain the number 0
(zero) in
which case I want the IF function to display the appropriate word.

However if the compared cells are blank (have no data in them), my
unsophisticated IF function thinks that F3=G3 and returns the word
"Equal".

How can I get the IF function to remain blank when my compared cells
(F3 &
G3) are blank?

Cheers,
Steve Sharpe.


Try=if(and(g3="",f3=""),"",IF(F3G3,"Yes",IF(F3=G3 ,"Equal",IF(F3<G3,"No")))


--
Pecoflyer

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows file
upload -faster and better answers

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=61004




  #11   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 66
Default Nested IF that will ignore blank (empty) cells

Hi,
Thanks for your help, I couldn't get it to work at first as the cell was
formatted for "text" and not "general", so it thought I was entering text and
not a function.
I don't call myself 'Struggling in Sheffield' for nothing.
Thanks again.

"xlmate" wrote:

does this do what you want

=IF(OR(F3="",G3=""),"",IF(F3G3,"Yes",IF(F3=G3,"Eq ual",IF(F3<G3,"No"))))

if any of the cells are blank, it will remain blank.

HTH

--
Pls provide your feedback by clicking the Yes button below if this post have
help you. This will help others to search the archives for result better.


Thank You

cheers, francis



"Struggling in Sheffield" wrote:

Hi,
I have a simple nested IF function (Excel 2003) which compares numbers in
two cells, then returns a word depending on whether one number is greater
than, equal to, or less than the other:

=IF(F3G3,"Yes",IF(F3=G3,"Equal",IF(F3<G3,"No")))

Either (or both) of the compared cells might contain the number 0 (zero) in
which case I want the IF function to display the appropriate word.

However if the compared cells are blank (have no data in them), my
unsophisticated IF function thinks that F3=G3 and returns the word "Equal".

How can I get the IF function to remain blank when my compared cells (F3 &
G3) are blank?

Cheers,
Steve Sharpe.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Ignore blank cells Angyl Excel Discussion (Misc queries) 4 October 30th 07 10:42 PM
Can a formula ignore empty cells? Melissa Excel Worksheet Functions 1 March 31st 06 06:24 PM
Trendline to ignore empty cells Kara Charts and Charting in Excel 0 June 9th 05 05:39 PM
Trendline to ignore empty cells Kara Charts and Charting in Excel 2 June 7th 05 05:30 PM
Trendlines to ignore empty cells Hoochi Coochi Man Charts and Charting in Excel 7 January 14th 05 02:31 PM


All times are GMT +1. The time now is 06:44 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"