Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? Thanks in advance |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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*(K3M3)) 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) Adjust for your needs. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want to copy a formula n Excel but skip any blank cells | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
How can I make the graph omit blank cells in the data set? | Charts and Charting in Excel | |||
REPOST: How can I make the graph omit blank cells in the data set? | Charts and Charting in Excel |