Excel Query

choyaa

Hi All,


I have two tabs in a spreadsheet, one called "cover sheet" and one called "goals".


Within goals I have three columns, column A is a rank formula to show the top 10 teams with the most goals, column B shows the team's name and column C shows the goals scored.


On the cover sheet there is a table that is formulated to pull across the team's name and goal numbers based on their rank, the ranks are manually listed from 1-10 within the table.


What I want to include is a check at the bottom to ensure that when I add the goals from these ten teams and minus it from the goals within the top 10 teams within the "goals" tab it equals 0.


Can this be done?

Le_Kirk

Notwithstanding that you are pulling data from one worksheet to another, if you put a formula in a cell (lets say A12) on the "Cover Sheet" that says =SUM(A1..A10) assuming your goals in cells A1 - A10 (if not just adjust formula) Then on "Goals" Sheet another formula in (lets say C12) that says =SUM(C1..C10). Finally on your "Cover Sheet" another formula (lets say in cell A14) that says
=IF(A12=Goals!C12,"Correct","Error").

OR =IF(A12-Goals!C12=0,"Correct","Error").

choyaa

Thanks for that response, I'll try to give some better detail.


In the goals tab there are 20 teams.


Column A is formula driven =RANK(C7,$C$7:$N$27)


B has the list of 20 teams.


C Has how many goals each teams scored.


Now within the coversheet I have the a formula to pull across the names and goals of the top ten scoring teams and an overall formula to calculate the goals.


However if my overall formula calculates the top ten teams to have scored 40, I want a new equation put in to take this away from what the top 10 teams have scored in the goals tab. I am hoping to get a 0 returned otherwise it alerts me that I need to double check things.

Le_Kirk

Do you want to do it for each individual team or for the top ten teams collectively? Presumably the top ten teams in Goals will be the same top ten teams in Cover Sheet?? The 40 goals, is that per team or collectively? If your formula for pulling the teams from Goals to Cover Sheet is correct, how can the goals total be different? Are you using a lookup table to pull the top ten teams from sheet to sheet?

choyaa

Le_Kirk wrote: »

Do you want to do it for each individual team or for the top ten teams collectively? Presumably the top ten teams in Goals will be the same top ten teams in Cover Sheet?? The 40 goals, is that per team or collectively? If your formula for pulling the teams from Goals to Cover Sheet is correct, how can the goals total be different? Are you using a lookup table to pull the top ten teams from sheet to sheet?

It's the top ten teams collectively.


Lets Look for example at the goals cover sheet


Rank Team Goals


2 Liverpool 4
1 Man Utd 6
20 Chelsea 0


In the coversheet I have used a formula to pull across all teams that are ranked between 1-10 and they are listed, at the bottom of this table is the total goals they have scored between them which is 40.


What I want this additional formula to use the 40 and minus all goals within the "goals" tab that relate to each ranking in the table which should return me with zero.

Bigphil1474

On the goals tab, create a formula in a cell to count the total number of goals for all the teams.
Create a formula in a second cell for the goals scored by teams ranked 11 to 20.
A third cell can be used to show the difference, which should be the same as the goals for the teams ranked 1 to 10. (you could format the cell on cover sheet to be green if it is the same as the value in that third cell).


As le_kirk says, if you are drawing the correct figures from the goals tab, then there shouldn't be any difference anyway.

Le_Kirk

I have been doing some work on this on an actual spread sheet (rather than in my head) and the first problem I came across was that of teams scoring an equal number of goals so using =RANK will give you teams of equal rank. How have you overcome this? What formula are you using to pull info from Goals to Cover?