Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works

# Formula problem, Excel

### #1 bagman Posted 07 October 2005 - 07:43 PM

bagman

New Member

• Member
• 3 posts
I have a spread sheet for a weekly college footbal contest where the players do not pick the winner, the pick the final scores for each team. (the person closest, on each game is the winner) The problem I am having is coming up with an IF statement that will make all the numbers positive. For instance, it does not matter if you miss the score on the high or low end, it is just how far off you are.
BTW, no money is exchanged, only pride.

I set the spread sheet as follows:
Line 1 has all the teams playing, paired.
Line 2 has the final score
Line 3 is each individuals guess
Cell A Is the players name
Cell B is Team A
cell C is team B
Line 4 is a merged cell (A&B) which is in need of the formula.

I'm pretty green, not real well versed in excel, just enough to get in trouble! Initially I tried =(B2-B3)+(C2-C3). That's fine if you always chose a score lower than the actual. If you don't you get a negative numer, which skews the total difference in how far off you were on each game!

It's probably a simple solution, but not for this simpleton.

Edited by bagman, 07 October 2005 - 08:51 PM.

• 0

### #2 dsm Posted 08 October 2005 - 06:19 AM

dsm

Member

• Member
• 98 posts
Gidday Bagman,
If you decide to attach files to your posts you will need to zip them first.

You may find the "abs" absolute function useful

eg.
=abs(44) >> result = 44
=abs(-44) >> result = 44

b2 = 20, c2 = 15
=abs(b2 - c2) >> result = 5
=abs(c2 - b2) >> result = 5

Hope this helps
David
• 0

### #3 bagman Posted 08 October 2005 - 10:19 AM

bagman

New Member

• Topic Starter
• Member
• 3 posts
David, et al:
Does this help? Cells aligned as below: I'm sorry I'm unable to make it clearer:

Games Team A Team B
Actual Score 13 38
Player 1 7 44
difference ____________*

cell A = Games; Actual Score; Player 1; difference
cell B = Team A ; 13 ; 7
cell C = Team B 38; 44
Line 4 has the Merged cell B&C in need of the equation.

Again, in need of the equation for the difference (all positive numbers) between actual score, and guess.

*(cell in need of the equation is a merged cell to combine difference in total game {teamA &B})

Clear as the Mississippi on a spring day???
• 0

### #4 dsm Posted 10 October 2005 - 04:34 AM

dsm

Member

• Member
• 98 posts
Bagman,
The absolute "abs" function I mentioned in my previous post should help.

eg.
change =(B2-B3)+(C2-C3) to =abs(B2-B3)+abs(C2-C3)

Example attached

HTH

• 0

### #5 bagman Posted 10 October 2005 - 08:22 AM

bagman

New Member

• Topic Starter
• Member
• 3 posts
Gidday to you David:

That smile means IT WORKED! I had no doubt that one of you studs could point me in the right direction. I appreciate you assistance. This will make tracking this SO MUCH EASIER~!!
• 0

### Similar Topics

#### 0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users