+ Reply to Thread
Results 1 to 11 of 11

Formula to return a value using two variables

  1. #1
    Registered User
    Join Date
    03-28-2010
    Location
    wokingham, England
    MS-Off Ver
    Excel 2003
    Posts
    29

    Formula to return a value using two variables

    hi

    i am trying and have been for most of the weekend to write a formula to enable me to find the total points gained per team per week played as a running total either home or away. I have attached a sample spreadsheet (test.xls) Spreadsheet contains two sheets: Data and League.

    i have tried IF functions, Vlookups etc to no avail.

    Basically what i am struggling with is, if for example Arsenal played 16th August and won i would like this to appear in the approiate cell (B3) on the 'league' sheet as 3pts. Then, they played again on 23rd August and drew. In the apporpiate cell (B6) on the 'league' sheet should read 4pts (3pts from previous and 1 pt for drawing.)

    I hope people can understand that waffle.

    Thanks in advance,
    Attached Files Attached Files
    Last edited by argegg26; 03-28-2010 at 07:13 PM. Reason: poor title

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Formula to return a value using two variables

    Thanks for changing the title. I'm not familiar with the points system. Which column has the point that you want to look up and add?

  3. #3
    Registered User
    Join Date
    03-28-2010
    Location
    wokingham, England
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: Formula to return a value using two variables

    Apologies.

    the home points gained are in Column G and Away points in column H.

  4. #4
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Formula to return a value using two variables

    But Arsenal for the 23/08 has 0 in column H. How do you arrive at wanting to add 1?

  5. #5
    Registered User
    Join Date
    03-28-2010
    Location
    wokingham, England
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: Formula to return a value using two variables

    arsenal played at home on 16th and won. this would return a 3 in the B3 cell but they drew away 23rd so in cell B6 on the 'league' sheet which is one point and in the cell should appear 4pts.

  6. #6
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Formula to return a value using two variables

    Where does that 1 come from? Cell H13 has a 0. Does that mean the formula should add a 1 when column H has a 0? Can you explain this a bit more?

  7. #7
    Registered User
    Join Date
    03-28-2010
    Location
    wokingham, England
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: Formula to return a value using two variables

    Sorry i'm seem quite bad at explaining myself.

    when Arsenal are at home (Column B) the value should come from Column G and when Away (Column C) the value should come from Column H.

  8. #8
    Registered User
    Join Date
    03-28-2010
    Location
    wokingham, England
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: Formula to return a value using two variables

    yep my mistake again, they didn't draw they lost. i apologise. this should return a value of zero and not 1 as previously mentioned.
    but the running total should be 3 from the earlier win on 16th.

  9. #9
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Formula to return a value using two variables

    Exactly.....

  10. #10
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Formula to return a value using two variables

    Try in B3 of the League sheet

    Please Login or Register  to view this content.
    Copy down and across.

    This will show the current points for each date, even if the team did not play on that date but the date before. Is that OK?

  11. #11
    Registered User
    Join Date
    03-28-2010
    Location
    wokingham, England
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: Formula to return a value using two variables

    That's perfect. Thanks for this and thank you for your patience.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1