# Formula to return a value using two variables

1. ## 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.

2. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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.

Exactly.....

10. ## 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. ## Re: Formula to return a value using two variables

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

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

#### 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