Welcome Guest
HeliTorque
  
User Control Panel

Security Code: : Security Code
Type Security Code Here: :
 
Register Here
Lost Password?

Online Stats:
Visitors: 28
Members: 0
Total: 28

Membership:
New Today: 0
New Yesterday: 0
Registering: 0
Members: 6662
Latest: chrisw

Most Ever Online
Visitors: 447
Members: 10
Total: 457


HeliTorque :: View topic - Microsoft Excel Help needed please
Forum FAQ
Forum FAQ
Search
Search
Memberlist
Memberlist
Usergroups
Usergroups
Profile
Profile
Contact Manager
Contact Manager
Log in
Log in
Log in to check your private messages
Log in to check your private messages
HeliTorque Forum Index » Anything Goes!

Post new topic   Reply to topic All times are GMT
Microsoft Excel Help needed please
View previous topic :: View next topic  
Author Message
oi_martin
H Addict
H Addict


Offline
Joined: May 06, 2005
Posts: 671
Location: Didcot


uk.gif

PostPosted: Mon Oct 26, 2009 9:01 pm    Post subject: Microsoft Excel Help needed please Reply with quote

Dear All

Are there any whizz's with MS EXcel formulas.

I am trying to create a spreadsheet for entering results in a sports game or serious of games

I have generated the sheets with the named cells, that was the easy part

I have generated cells to enter the score per player, ie shots for and shots against and a cell for the resulting number of points.
In each game it is 2 points for a win, 1 for a draw and 0 for a loss.

I have generated the following formula

=IF(C14>D14,"2",IF(C14=D14,"1",IF(C14<D14,"0")))

where C14 is shots for and D14 is shots against.

This works fine when the scores are entered ie the correct number of points appears in the correct cell, however the cell shows 1 point for a draw when there is nothing in the for and against column.

I am guessing that as each of the for and against has nothing in ie zero it thinks its a draw.

Can anyone please help me by amending the formula so if no scores are entered the points cells remian blank.

Hope i've explained it ok

Many thanks for any help

Martin
Back to top
View user's profile Send private message
rjc
High Flying 'Torquer
High Flying 'Torquer


Offline
Joined: Jul 11, 2006
Posts: 189
Location: Cambridge


uk.gif

PostPosted: Mon Oct 26, 2009 9:29 pm    Post subject: Reply with quote

Ah, yes, the no score draw is triggering your score rule.

Does the following do what you want?

=IF(C14=D14,IF(AND(C14=0,D14=0),"0","1"),IF(C14>D14,"2","0"))

I am using office for a Mac, but it seems to do the trick for me...
Back to top
View user's profile Send private message
rjc
High Flying 'Torquer
High Flying 'Torquer


Offline
Joined: Jul 11, 2006
Posts: 189
Location: Cambridge


uk.gif

PostPosted: Mon Oct 26, 2009 9:36 pm    Post subject: Reply with quote

Thinking, to be more "correct" the following may be a better solution...

=IF(C14=D14,IF(AND(ISBLANK(C14),ISBLANK(D14)),"0","1"),IF(C14>D14,"2","0"))

...it should so the right thing, but just caters for the blanks.
Back to top
View user's profile Send private message
oi_martin
H Addict
H Addict


Offline
Joined: May 06, 2005
Posts: 671
Location: Didcot


uk.gif

PostPosted: Mon Oct 26, 2009 10:26 pm    Post subject: Reply with quote

rjc

Many thanks, that seems to do the trick, it just leaves a zero in the points cell.

Thanks

Martin
Back to top
View user's profile Send private message
rjc
High Flying 'Torquer
High Flying 'Torquer


Offline
Joined: Jul 11, 2006
Posts: 189
Location: Cambridge


uk.gif

PostPosted: Mon Oct 26, 2009 10:30 pm    Post subject: Reply with quote

oi_martin wrote:
rjc

Many thanks, that seems to do the trick, it just leaves a zero in the points cell.

Thanks

Martin


Well, suppression of that wasn't in the spec!

How about...

=IF(C14=D14,IF(AND(ISBLANK(C14),ISBLANK(D14)),"","1"),IF(C14>D14,"2",""))

...OK?
Back to top
View user's profile Send private message
oi_martin
H Addict
H Addict


Offline
Joined: May 06, 2005
Posts: 671
Location: Didcot


uk.gif

PostPosted: Wed Oct 28, 2009 7:34 am    Post subject: Reply with quote

Hi rjc

I copied that one into the points cell and it seemed to take over the first cell to the right and not allow anything to be entered in that cell.

I have for the moment used the previous formula.

Many thanks for your help already , i'm still on basic single stage formulas. Its amazing what Excel can do. Think i need to get some practice with excel

Martin
Back to top
View user's profile Send private message
James T Lowe
Moderator
Moderator


Offline
Joined: Jul 27, 2004
Posts: 2575
Location: Leicester


uk.gif

PostPosted: Wed Oct 28, 2009 8:05 am    Post subject: Reply with quote

Can I simplify this a little?

The original query you posted, Martin, if I understand correctly, was that you didn't want a score to appear when the cells were blank. Otherwise, there wasn't anything else wrong with your formula?


Well, rather than adjust your formula, just embed it in another condition

This one will return a blank points cell, if both For and Against are blank.
Code:
=IF(AND(ISBLANK(C14),ISBLANK(D14)),"",IF(C14>D14,"2",IF(C14=D14,"1",IF(C14<D14,"0"))))



If you just want one of the for or against cells to trigger a points return (0,1,2) then use either of these:
Code:
=IF(ISBLANK(C14),"",IF(C14>D14,"2",IF(C14=D14,"1",IF(C14<D14,"0"))))
=IF(ISBLANK(D14),"",IF(C14>D14,"2",IF(C14=D14,"1",IF(C14<D14,"0"))))


Does that help, any?
_________________
J.
Back to top
View user's profile Send private message Visit poster's website
oi_martin
H Addict
H Addict


Offline
Joined: May 06, 2005
Posts: 671
Location: Didcot


uk.gif

PostPosted: Thu Oct 29, 2009 7:42 am    Post subject: Reply with quote

James, thats great thanks for your help

Martin
Back to top
View user's profile Send private message
Catwoman
H Addict
H Addict


Offline
Joined: Jul 27, 2004
Posts: 1680
Location: Radway, Warwickshire


uk.gif

PostPosted: Thu Oct 29, 2009 5:31 pm    Post subject: Reply with quote

Shocked Shocked Shocked CW completely confused Shocked

I use EXCEL, but only when it has been set up for me. I can add and copy stuff - but what you lot are talking about is like a foreign language Rolling Eyes
Back to top
View user's profile Send private message Send e-mail
WindSwept
H Addict
H Addict


Offline
Joined: Sep 12, 2008
Posts: 500


uk.gif

PostPosted: Fri Oct 30, 2009 7:06 pm    Post subject: Reply with quote

I wish i could remeber excel.

Although to be honest in my current role i dont need it.

I completed my A-level IT course on excel, i had to design a stock control system. Normally done in Access but was mucho fun in excel.
Back to top
View user's profile Send private message Skype Name
WhirlyGuy
Administrator
Administrator


Offline
Joined: Jul 17, 2004
Posts: 742
Location: United Kingdom | Birmingham


uk.gif

PostPosted: Sat Oct 31, 2009 1:33 pm    Post subject: Reply with quote

Eeeekkk!!!

Access, Excel!!!

Agggghhh!!!

I can programme in Excel no problem but would rather not. Give me MySQL and PHP any day over those offerings!! Smile

WhirlyGuy
Back to top
View user's profile Send private message Visit poster's website Skype Name
Display posts from previous:   
Post new topic   Reply to topic    HeliTorque Forum Index » Anything Goes! All times are GMT

 
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Sponsors


Billund Air Center

Visit HeliTorque!