If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 how to make a formula ignore blank cells
 Author Name Remember Me? Password
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## how to make a formula ignore blank cells

 Thread Tools Display Modes
#1
June 6th 06, 01:20 AM posted to microsoft.public.excel.worksheet.functions
 Snap external usenet poster Posts: n/a
how to make a formula ignore blank cells

I'm trying to create a league table but the formula I'm using recognises
blank cells as zero - before any results are input.

My formula (for a tied game) is =if(k3=l3,"1") awarding 1 point for a tie /
draw.

However, my league table shows a tie / draw before the game is played! How
do I get the formula to ignore the cell until data is input?

#2
June 6th 06, 01:29 AM posted to microsoft.public.excel.worksheet.functions
 Pete_UK external usenet poster Posts: n/a
how to make a formula ignore blank cells

Try this, assuming the scores are placed in K3 and I3:

=IF(OR(K3="",I3=""),"",IF(K3=I3,"1"))

This gives a blank if either K3 or I3 are blank. Your formula does not
check for K3 being larger or smaller than I3.

Hope this helps.

Pete

Snap wrote:
> I'm trying to create a league table but the formula I'm using recognises
> blank cells as zero - before any results are input.
>
> My formula (for a tied game) is =if(k3=l3,"1") awarding 1 point for a tie /
> draw.
>
> However, my league table shows a tie / draw before the game is played! How
> do I get the formula to ignore the cell until data is input?
>
> Thanks in advance

#3
June 6th 06, 01:32 AM posted to microsoft.public.excel.worksheet.functions
 Paul B external usenet poster Posts: n/a
how to make a formula ignore blank cells

Snap, one way,

=IF(K3="","",IF(K3=L3,"1",""))

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Snap" > wrote in message
...
> I'm trying to create a league table but the formula I'm using recognises
> blank cells as zero - before any results are input.
>
> My formula (for a tied game) is =if(k3=l3,"1") awarding 1 point for a tie

/
> draw.
>
> However, my league table shows a tie / draw before the game is played! How
> do I get the formula to ignore the cell until data is input?
>
> Thanks in advance

#4
June 6th 06, 01:38 AM posted to microsoft.public.excel.worksheet.functions
 RJ external usenet poster Posts: n/a
how to make a formula ignore blank cells

Sounds like you need to add some logic to your formula for the case where the
cells are still blank. The following formula should help with that.

=IF(AND(k3=l3,AND(k3<>"",l3<>"")),1,"")

This will leave the cell blank in the case that k3 and l3 are not updated
yet and are still blank.
I do question your wanting to put a value of "1" if there is a tie. By
putting the one in quotes the field will be recognized as text in excel and
will not be helpful if you are planning on doing some calculations using
these numbers later. If you are planning on doing some calculations then I
would not include the quotes.

"Snap" wrote:

> I'm trying to create a league table but the formula I'm using recognises
> blank cells as zero - before any results are input.
>
> My formula (for a tied game) is =if(k3=l3,"1") awarding 1 point for a tie /
> draw.
>
> However, my league table shows a tie / draw before the game is played! How
> do I get the formula to ignore the cell until data is input?
>
> Thanks in advance

#5
June 6th 06, 01:44 AM posted to microsoft.public.excel.worksheet.functions
 RJ external usenet poster Posts: n/a
how to make a formula ignore blank cells

I see you approved one of the prior solutions, but I would try and avoid the
"or" statement if there is a chance one of the two fields you are comparing
can be blank. By having an "And" statement then both fields being blank must
be satisfied.

"Snap" wrote:

> I'm trying to create a league table but the formula I'm using recognises
> blank cells as zero - before any results are input.
>
> My formula (for a tied game) is =if(k3=l3,"1") awarding 1 point for a tie /
> draw.
>
> However, my league table shows a tie / draw before the game is played! How
> do I get the formula to ignore the cell until data is input?
>
> Thanks in advance

#6
June 6th 06, 02:11 AM posted to microsoft.public.excel.worksheet.functions
 Snap external usenet poster Posts: n/a
how to make a formula ignore blank cells

Thanks, Pete, but this still seems to assume the blank cell is zero - if I
put a space in between "". If I don't put a space, a Value error appears. My

=Sum(IF(K3=M3,"1")+IF(K5=M5,"1")+IF(K7=M7,"1"))

- giving an answer of 3 even if cells are blank. I need the answer to be 0
until scores are input. Any further suggestions?

Thanks

"Pete_UK" wrote:

> Try this, assuming the scores are placed in K3 and I3:
>
> =IF(OR(K3="",I3=""),"",IF(K3=I3,"1"))
>
> This gives a blank if either K3 or I3 are blank. Your formula does not
> check for K3 being larger or smaller than I3.
>
> Hope this helps.
>
> Pete
>
> Snap wrote:
> > I'm trying to create a league table but the formula I'm using recognises
> > blank cells as zero - before any results are input.
> >
> > My formula (for a tied game) is =if(k3=l3,"1") awarding 1 point for a tie /
> > draw.
> >
> > However, my league table shows a tie / draw before the game is played! How
> > do I get the formula to ignore the cell until data is input?
> >
> > Thanks in advance

>
>

#7
June 8th 06, 12:54 AM posted to microsoft.public.excel.worksheet.functions
 MyVeryOwnSelf external usenet poster Posts: n/a
how to make a formula ignore blank cells

> Thanks, Pete, but this still seems to assume the blank cell is zero -
> if I put a space in between "". If I don't put a space, a Value error
> appears. My Full formula reads
>
> =Sum(IF(K3=M3,"1")+IF(K5=M5,"1")+IF(K7=M7,"1"))
>
> - giving an answer of 3 even if cells are blank. I need the answer to
> be 0 until scores are input. Any further suggestions?

It looks like maybe:
K3 = team score for game one
M3 = opponent score for game one
K5 & M5 likewise for game 2; and K7 and M7, for game 3

If so, maybe the following formulas will help
O3 contains points for win
=IF(K3&M3="","",2*(K3>M3))
and copy down to row 5

P3 contains points for tie
=IF(K3&M3="","",N(K3=M3))
and copy down to row 5

P10 contains total points
=SUM(O3:P7)

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post I want to copy a formula n Excel but skip any blank cells Ann Excel Worksheet Functions 1 December 12th 05 06:55 PM Match then lookup Tenacity Excel Worksheet Functions 9 December 3rd 05 05:30 AM Formula Problem - interrupted by #VALUE! in other cells!? Ted Excel Worksheet Functions 17 November 25th 05 05:18 PM How can I make the graph omit blank cells in the data set? easy Charts and Charting in Excel 3 March 17th 05 02:48 PM REPOST: How can I make the graph omit blank cells in the data set? easy Charts and Charting in Excel 2 March 17th 05 09:57 AM

All times are GMT +1. The time now is 09:41 AM.

 - Contact Us - ExcelBanter forum home - FAQ - Links - Privacy Statement - Top