Jump to content

Welcome to Geeks to Go - Register now for FREE

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
Photo

Formula problem, Excel


  • Please log in to reply

#1
bagman

bagman

    New Member

  • Member
  • Pip
  • 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. :tazz:
Thanks for your help!

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

  • 0

Advertisements


#2
dsm

dsm

    Member

  • Member
  • PipPip
  • 98 posts
Gidday Bagman,
I am having trouble understanding what your spreadsheet looks like.
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

bagman

    New Member

  • Topic Starter
  • Member
  • Pip
  • 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??? :tazz:
  • 0

#4
dsm

dsm

    Member

  • Member
  • PipPip
  • 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

Attached Files


  • 0

#5
bagman

bagman

    New Member

  • Topic Starter
  • Member
  • Pip
  • 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~!! :tazz:
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP