ignoring blank cells

Tommy Tickle · May 3rd, 2002, 07:05 AM

Any help, greatly appreciated...Okay, I'm doing this sheet for the World Cup. Using the IF function, I have for e.g. said that if team A's score is more than team B then give team A 3 points and team B 0 points. This works fine and similarly if the two teams have the same score, to credit them with 1 point each. Here's my problem...before any games are played, the cells are obviously blank and therefore equal. This results in an allocation of 1 point each as though it was a draw. I just need it to understand that if there are no numbers in the cells, it shoulf not consider them for the calculations. Thanks in advance. Mr Tickle.

kinkyparamour · May 3rd, 2002, 07:13 AM

Tommy,

Seeing your formula would help but maybe try something like =if(a1<=0,""). Then add your score if more then 0........

Denny

[ This Message was edited by: kinkyparamour on 2002-05-03 06:14 ]

[ This Message was edited by: kinkyparamour on 2002-05-03 06:15 ]

George J · May 3rd, 2002, 07:16 AM

Add to the beginning of your formula:

if(A2>="","",(your formula)

I think this should do it, even for nil nil draws.

Let me know

crimlet · May 3rd, 2002, 07:16 AM

try using this
=IF(A4="","",IF(A4=B4,"1",IF(B4>A4,"B=3","A=3")))

A being team score
B being team B score
and the formula is place in any cell

Tommy Tickle · May 3rd, 2002, 07:58 AM

Thanks a lot. Put the formula offered by Crimlet and it works a treat. I assume it was the double set of "" that I wasn't familiar with...thanks folks.

Chris Davison · May 3rd, 2002, 01:32 PM

Tommy,

if you do a search on this forum for "World Cup", you'll find a few threads for excel sheets which already map out all possible permutations for this summer....

(although how anyone can even *think* England aren't going to win is totally beyond me...)

if you're doing this as a personal exercise, good for you... let us know how it turns out : I do leagues for all the English soccer and American Baseball, so am always keen to see others' ideas and how they approached them

3 lions on the shirt
Jules Rimet still gleaming...

May 3rd, 2002, 07:16 AM	#3
George J Board Regular Join Date: Feb 2002 Location: Edinburgh, Bonnie Scotland Posts: 820	Add to the beginning of your formula: if(A2>="","",(your formula) I think this should do it, even for nil nil draws. Let me know __________________ George J

May 3rd, 2002, 01:32 PM	#6
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	Tommy, if you do a search on this forum for "World Cup", you'll find a few threads for excel sheets which already map out all possible permutations for this summer.... (although how anyone can even think England aren't going to win is totally beyond me...) if you're doing this as a personal exercise, good for you... let us know how it turns out : I do leagues for all the English soccer and American Baseball, so am always keen to see others' ideas and how they approached them 3 lions on the shirt Jules Rimet still gleaming... __________________ :: Pharma Z - Family drugstore ::

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 3rd, 2002, 07:05 AM	#1
Tommy Tickle New Member Join Date: May 2002 Posts: 2	Any help, greatly appreciated...Okay, I'm doing this sheet for the World Cup. Using the IF function, I have for e.g. said that if team A's score is more than team B then give team A 3 points and team B 0 points. This works fine and similarly if the two teams have the same score, to credit them with 1 point each. Here's my problem...before any games are played, the cells are obviously blank and therefore equal. This results in an allocation of 1 point each as though it was a draw. I just need it to understand that if there are no numbers in the cells, it shoulf not consider them for the calculations. Thanks in advance. Mr Tickle.

May 3rd, 2002, 07:13 AM	#2
kinkyparamour Board Regular Join Date: Feb 2002 Location: Florida Posts: 82	Tommy, Seeing your formula would help but maybe try something like =if(a1<=0,""). Then add your score if more then 0........ Denny [ This Message was edited by: kinkyparamour on 2002-05-03 06:14 ] [ This Message was edited by: kinkyparamour on 2002-05-03 06:15 ]

May 3rd, 2002, 07:16 AM	#4
crimlet Board Regular Join Date: May 2002 Location: Ipswich, Suffolk, England Posts: 135	try using this =IF(A4="","",IF(A4=B4,"1",IF(B4>A4,"B=3","A=3"))) A being team score B being team B score and the formula is place in any cell

May 3rd, 2002, 07:58 AM	#5
Tommy Tickle New Member Join Date: May 2002 Posts: 2	Thanks a lot. Put the formula offered by Crimlet and it works a treat. I assume it was the double set of "" that I wasn't familiar with...thanks folks.