Add a Value to A Column Based on a Result From Another Column

C

calii

New Member
Joined
Jun 2, 2021
Messages
7
Office Version
  1. 365
Platform
  1. MacOS
I'm attempting to make a soccer simulation and a key component of this is calculating wins and losses. I was wondering if there was a way to calculate a result based on 2 numbers and then add it to a win/draw/loss column in another sheet. Something along the lines of "If cell1 > cell2, +1 to the wins column" and then have this add up for all of the wins, draws and losses across a 38 game season.


here is the standings sheet with the win/loss/draw columns
New Complete EuroCup Template.xlsx
H
22
Standings


Here is the scores sheet with one score implemented in it
New Complete EuroCup Template.xlsx
B
4
Scores


Im not sure if these are formatted correctly, let me know if there is an easier way to do it.

Calii
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
calii said:
I'm attempting to make a soccer simulation and a key component of this is calculating wins and losses. I was wondering if there was a way to calculate a result based on 2 numbers and then add it to a win/draw/loss column in another sheet. Something along the lines of "If cell1 > cell2, +1 to the wins column" and then have this add up for all of the wins, draws and losses across a 38 game season.


here is the standings sheet with the win/loss/draw columns
New Complete EuroCup Template.xlsx
H
22
Standings


Here is the scores sheet with one score implemented in it
New Complete EuroCup Template.xlsx
B
4
Scores


Im not sure if these are formatted correctly, let me know if there is an easier way to do it.

Calii
Click to expand...
Just Realized I have to select the whole sheet for it to show up.

Here is the one with the scores:
New Complete EuroCup Template.xlsx
ABCDEFGHIJKLMNOPQRSTU
1Week 1Week 2Week 3Week 4Week 5Week 6Week 7
2Serbia3FIreland0FNetherlands0FDenmark0FPortugal0FSpain0FUkraine0F
3Germany2Ukraine0Poland0Switzerland0Spain0England0Belgium0
4
5Netherlands0FRussia0FScotland0FTurkey0FNetherlands0FRussia0FPortugal0F
6Belgium0England0Spain0Wales0Italy0Ireland0Germany0
7
8Portugal0FBelgium0FEngland0FRussia0FScotland0FDenmark0FSerbia0F
9Russia0Scotland0Denmark0Ukraine0Germany0Wales0Netherlands0
10
11Ukraine0FGermany0FWales0FIreland0FCroatia0FNorway0FTurkey0F
12Wales0Italy0Ireland0Serbia0Poland0Ukraine0Russia0
13
14Italy0FWales0FFrance0FNorway0FSerbia0FBelgium0FScotland0F
15Poland0Serbia0Germany0England0France0Switzerland0France0
16
17Scotland0FDenmark0FCroatia0FBelgium0FEngland0FTurkey0FEngland0F
18Norway0Portugal0Belgium0Portugal0Belgium0Serbia0Poland0
19
20England0FSpain0FSwitzerland0FSpain0FSwitzerland0FPoland0FWales0F
21Turkey0Netherlands0Russia0Croatia0Norway0Portugal0Norway0
22
23France0FTurkey0FSerbia0FPoland0FUkraine0FGermany0FCroatia0F
24Spain0Switzerland0Italy0Scotland0Denmark0Croatia0Italy0
25
26Croatia0FNorway0FPortugal0FGermany0FWales0FItaly0FSwitzerland0F
27Denmark0Croatia0Norway0Netherlands0Russia0Scotland0Spain0
28
29Switzerland0FPoland0FUkraine0FItaly0FIreland0FFrance0FIreland0F
30Ireland0France0Turkey0France0Turkey0Netherlands0Denmark0
Scores



Here is the one with the standings and the win loss columns
New Complete EuroCup Template.xlsx
ABCDEFGHIJKLMNOP
1TeamPWDLGFGAGDP
21?? Spain00000:000Immediate Qualification
32??Norway00000:000
43??Serbia00003:210
54?? Switzerland00000:000Qualification Playoffs
65???????Scotland00000:000
76?? Ireland00000:000
87?? Italy00000:000
98?? France00000:000
109???????England00000:000
1110?? Portugal00000:000
1211?? Germany00002:3-10
1312?? Croatia00000:000
1413??Poland00000:000
1514???????Wales00000:000
1615??Ukraine00000:000
1716?? Netherlands00000:000
1817?? Belgium00000:000
1918??Denmark00000:000
2019?? Russia00000:000
2120?? Turkey00000:000
Standings
Cell Formulas
RangeFormula
J2J2=Scores!B23+Scores!E21+Scores!H5+Scores!K21+Scores!N2+Scores!Q3+Scores!T26+Scores!W18+Scores!Z8+Scores!AC12+Scores!AF23+Scores!AI9+Scores!AL26+Scores!AO6+Scores!AR29+Scores!AU15+Scores!AX30+Scores!BA5+Scores!BD27+Scores!BG9+Scores!BJ26+Scores!BM6+Scores!BP29+Scores!BS14+Scores!BV30+Scores!BY5+Scores!CB27+Scores!CE8+Scores!CH24+Scores!CK11+Scores!CN21+Scores!CQ14+Scores!CT18+Scores!CW2+Scores!CZ3+Scores!DC20+Scores!DF12+Scores!DI23
K2:K21K2=H2-J2
L2:L21L2=$E2*3 + $F2
J3J3=Scores!B17+Scores!E27+Scores!H26+Scores!K15+Scores!N20+Scores!Q12+Scores!T20+Scores!W24+Scores!Z2+Scores!AF2+Scores!AI27+Scores!AL30+Scores!AO5+Scores!AR27+Scores!AC6+Scores!AU8+Scores!AX24+Scores!BA11+Scores!BD21+Scores!BG29+Scores!BJ6+Scores!BM30+Scores!BP5+Scores!BS27+Scores!BV8+Scores!BY24+Scores!CB11+Scores!CE21+Scores!CH14+Scores!CK18+Scores!CN17+Scores!CQ15+Scores!CT2+Scores!CW12+Scores!CZ23+Scores!DC9+Scores!DF26+Scores!DI3
J4J4=Scores!B3+Scores!E14+Scores!H24+Scores!K11+Scores!N15+Scores!Q17+Scores!T9+Scores!W20+Scores!Z30+Scores!AC29+Scores!AF30+Scores!AI26+Scores!AL24+Scores!AO20+Scores!AR18+Scores!AU14+Scores!AX12+Scores!BA8+Scores!BD6+Scores!BG20+Scores!BJ12+Scores!BM23+Scores!BP9+Scores!BS26+Scores!BV6+Scores!BY29+Scores!CB24+Scores!CE30+Scores!CH5+Scores!CK27+Scores!CN8+Scores!CQ3+Scores!CT11+Scores!CW21+Scores!CZ2+Scores!DC18+Scores!DF17+Scores!DI15
J5J5=Scores!B30+Scores!E23+Scores!H21+Scores!K2+Scores!N21+Scores!Q14+Scores!T27+Scores!W26+Scores!Z18+Scores!AC20+Scores!AF12+Scores!AI23+Scores!AL9+Scores!AO26+Scores!AR6+Scores!AU29+Scores!AX11+Scores!BA30+Scores!BD2+Scores!BG18+Scores!BJ17+Scores!BM15+Scores!BP20+Scores!BS12+Scores!BV23+Scores!BY9+Scores!CB26+Scores!CE6+Scores!CH2+Scores!CK29+Scores!CN30+Scores!CQ5+Scores!CT27+Scores!CW8+Scores!CZ24+Scores!DC2+Scores!DF21+Scores!DI14
J6J6=Scores!B18+Scores!E8+Scores!H6+Scores!K23+Scores!N9+Scores!Q26+Scores!T15+Scores!W11+Scores!Z29+Scores!AC3+Scores!AF5+Scores!AI3+Scores!AL8+Scores!AO24+Scores!AR11+Scores!AU21+Scores!AX14+Scores!BA18+Scores!BD17+Scores!BG12+Scores!BJ23+Scores!BM3+Scores!BP26+Scores!BS6+Scores!BV29+Scores!BY20+Scores!CB30+Scores!CE5+Scores!CH27+Scores!CK8+Scores!CN24+Scores!CQ11+Scores!CT3+Scores!CW14+Scores!CZ18+Scores!DC17+Scores!DF15+Scores!DI20
J7J7=Scores!B29+Scores!E3+Scores!H11+Scores!K12+Scores!N30+Scores!Q5+Scores!T30+Scores!W23+Scores!Z21+Scores!AC11+Scores!AF21+Scores!AI14+Scores!AL18+Scores!AO17+Scores!AR15+Scores!AU20+Scores!AX3+Scores!BA23+Scores!BD9+Scores!BG26+Scores!BJ3+Scores!BM29+Scores!BP12+Scores!BS30+Scores!BV5+Scores!BY27+Scores!CB8+Scores!CE24+Scores!CH11+Scores!CK21+Scores!CN14+Scores!CQ18+Scores!CT17+Scores!CW15+Scores!CZ20+Scores!DC3+Scores!DF23+Scores!DI9
J8J8=SUM(Scores!B15,Scores!E11,Scores!H23,Scores!K30,Scores!N5,Scores!Q27,Scores!T23,Scores!W3,Scores!Z5,Scores!AC21,Scores!AF14,Scores!AI18,Scores!AL17,Scores!AO15,Scores!AR20,Scores!AU12,Scores!AX23,Scores!BA3,Scores!BD26,Scores!BG8,Scores!BJ24,Scores!BM11,Scores!BP21,Scores!BS2,Scores!BV3,Scores!BY17,Scores!CB15,Scores!CE20,Scores!CH12,Scores!CK23,Scores!CN9,Scores!CQ26,Scores!CT6,Scores!CW29,Scores!CZ15,Scores!DC30,Scores!DF6,Scores!DI27)
J9J9=SUM(Scores!B24,Scores!E29,Scores!H15,Scores!K29,Scores!N14,Scores!Q30,Scores!T14,Scores!W30,Scores!Z23,Scores!AC24,Scores!AF11,Scores!AI21,Scores!AL14,Scores!AO18,Scores!AR2,Scores!AU3,Scores!AX20,Scores!BA12,Scores!BD23,Scores!BG21,Scores!BJ14,Scores!BM18,Scores!BP17,Scores!BS3,Scores!BV20,Scores!BY12,Scores!CB2,Scores!CE9,Scores!CH26,Scores!CK6,Scores!CN29,Scores!CQ23,Scores!CT29,Scores!CW5,Scores!CZ27,Scores!DC8,Scores!DF24,Scores!DI11)
J10J10=SUM(Scores!B21,Scores!E5,Scores!H9,Scores!K14,Scores!N18,Scores!Q2,Scores!T18,Scores!W8,Scores!Z6,Scores!AC23,Scores!AF9,Scores!AI2,Scores!AL6,Scores!AO29,Scores!AR17,Scores!AU30,Scores!AX5,Scores!BA27,Scores!BD8,Scores!BG2,Scores!BJ30,Scores!BM5,Scores!BP27,Scores!BS8,Scores!BV24,Scores!BY11,Scores!CB21,Scores!CE14,Scores!CH18,Scores!CK17,Scores!CN15,Scores!CQ20,Scores!CT12,Scores!CW23,Scores!CZ9,Scores!DC26,Scores!DF6,Scores!DI29)
J11J11=SUM(Scores!B9,Scores!E17,Scores!H27,Scores!K17,Scores!N3,Scores!Q20,Scores!T6,Scores!W2,Scores!Z24,Scores!AC26,Scores!AF6,Scores!AI29,Scores!AL23,Scores!AO30,Scores!AR5,Scores!AU27,Scores!AX8,Scores!BA24,Scores!BD11,Scores!BG14,Scores!BJ18,Scores!BM17,Scores!BP15,Scores!BS20,Scores!BV12,Scores!BY23,Scores!CB9,Scores!CE2,Scores!CH6,Scores!CK2,Scores!CN27,Scores!CQ30,Scores!CT5,Scores!CW27,Scores!CZ8,Scores!DC24,Scores!DF11,Scores!DI21)
J12J12=SUM(Scores!B2,Scores!E12,Scores!H14,Scores!K27,Scores!N8,Scores!Q24,Scores!T5,Scores!W9,Scores!Z17,Scores!AC18,Scores!AF17,Scores!AI15,Scores!AL20,Scores!AO12,Scores!AR23,Scores!AU9,Scores!AX26,Scores!BA6,Scores!BD29,Scores!BG27,Scores!BJ8,Scores!BM24,Scores!BP2,Scores!BS21,Scores!BV14,Scores!BY18,Scores!CB17,Scores!CE15,Scores!CH20,Scores!CK12,Scores!CN23,Scores!CQ9,Scores!CT26,Scores!CW6,Scores!CZ29,Scores!DC11,Scores!DF30,Scores!DI2)
J13J13=SUM(Scores!B27,Scores!E26,Scores!H18,Scores!K20,Scores!N12,Scores!Q23,Scores!T24,Scores!W29,Scores!Z15,Scores!AC2,Scores!AF29,Scores!AI30,Scores!AL5,Scores!AO27,Scores!AR8,Scores!AU24,Scores!AX2,Scores!BA21,Scores!BD14,Scores!BG5,Scores!BJ27,Scores!BM2,Scores!BP24,Scores!BS11,Scores!BV21,Scores!BY14,Scores!CB18,Scores!CE17,Scores!CH15,Scores!CK20,Scores!CN12,Scores!CQ2,Scores!CT9,Scores!CW26,Scores!CZ6,Scores!DC29,Scores!DF9,Scores!DI30)
J14J14=SUM(Scores!B14,Scores!E30,Scores!H2,Scores!K24,Scores!N11,Scores!Q21,Scores!T17,Scores!W27,Scores!Z26,Scores!AC15,Scores!AF20,Scores!AI12,Scores!AL2,Scores!AO9,Scores!AR26,Scores!AU6,Scores!AX29,Scores!BA9,Scores!BD30,Scores!BG6,Scores!BJ29,Scores!BM8,Scores!BP30,Scores!BS5,Scores!BV27,Scores!BY8,Scores!CB3,Scores!CE11,Scores!CH21,Scores!CK14,Scores!CN18,Scores!CQ17,Scores!CT15,Scores!CW20,Scores!CZ12,Scores!DC23,Scores!DF3,Scores!DI26)
J15J15=SUM(Scores!B11,Scores!E15,Scores!H12,Scores!K5,Scores!N27,Scores!Q8,Scores!T21,Scores!W5,Scores!Z9,Scores!AC14,Scores!AF18,Scores!AI17,Scores!AL15,Scores!AO2,Scores!AR12,Scores!AU23,Scores!AX9,Scores!BA26,Scores!BD3,Scores!BG3,Scores!BJ6,Scores!BM9,Scores!BP11,Scores!BS15,Scores!BV17,Scores!BY21,Scores!CB23,Scores!CE27,Scores!CH29,Scores!CK30,Scores!CN26,Scores!CQ24,Scores!CT20,Scores!CW18,Scores!CZ14,Scores!DC12,Scores!DF8,Scores!DI6)
J16J16=SUM(Scores!B12,Scores!E2,Scores!H30,Scores!K8,Scores!N24,Scores!Q11,Scores!T3,Scores!W17,Scores!Z27,Scores!AC17,Scores!AF15,Scores!AI20,Scores!AL12,Scores!AO23,Scores!AR9,Scores!AU26,Scores!AX6,Scores!BA29,Scores!BD5,Scores!BG15,Scores!BJ20,Scores!BM12,Scores!BP23,Scores!BS9,Scores!BV26,Scores!BY6,Scores!CB29,Scores!CE26,Scores!CH3,Scores!CK5,Scores!CN3,Scores!CQ8,Scores!CT24,Scores!CW11,Scores!CZ21,Scores!DC14,Scores!DF18,Scores!DI17)
J17J17=SUM(Scores!B6,Scores!E20,Scores!H3,Scores!K26,Scores!N6,Scores!Q29,Scores!T8,Scores!W12,Scores!Z14,Scores!AC27,Scores!AF8,Scores!AI24,Scores!AL11,Scores!AO3,Scores!AR14,Scores!AU18,Scores!AX17,Scores!BA15,Scores!BD20,Scores!BG30,Scores!BJ2,Scores!BM27,Scores!BP8,Scores!BS24,Scores!BV11,Scores!BY3,Scores!CB14,Scores!CE18,Scores!CH17,Scores!CK15,Scores!CN20,Scores!CQ12,Scores!CT23,Scores!CW9,Scores!CZ26,Scores!DC6,Scores!DF29,Scores!DI5)
J18J18=SUM(Scores!B5,Scores!E9,Scores!H17,Scores!K18,Scores!N17,Scores!Q15,Scores!T2,Scores!W6,Scores!Z20,Scores!AC9,Scores!AF26,Scores!AI6,Scores!AL29,Scores!AO21,Scores!AR30,Scores!AU5,Scores!AX27,Scores!BA2,Scores!BD24,Scores!BG23,Scores!BJ9,Scores!BM26,Scores!BP6,Scores!BS29,Scores!BV18,Scores!BY30,Scores!CB5,Scores!CE3,Scores!CH8,Scores!CK24,Scores!CN11,Scores!CQ21,Scores!CT14,Scores!CW3,Scores!CZ17,Scores!DC15,Scores!DF20,Scores!DI12)
J19J19=SUM(Scores!B26,Scores!E18,Scores!H8,Scores!K3,Scores!N23,Scores!Q9,Scores!T29,Scores!W15,Scores!Z11,Scores!AC30,Scores!AF3,Scores!AI5,Scores!AL27,Scores!AO8,Scores!AR24,Scores!AU11,Scores!AX21,Scores!BA14,Scores!BD18,Scores!BG24,Scores!BJ11,Scores!BM21,Scores!BP14,Scores!BS18,Scores!BV2,Scores!BY15,Scores!CB20,Scores!CE12,Scores!CH23,Scores!CK9,Scores!CN2,Scores!CQ6,Scores!CT29,Scores!CW17,Scores!CZ30,Scores!DC5,Scores!DF27,Scores!DI8)
J20J20=SUM(Scores!B8,Scores!E6,Scores!H20,Scores!K9,Scores!N26,Scores!Q6,Scores!T11,Scores!W21,Scores!Z12,Scores!AC5,Scores!AF27,Scores!AI8,Scores!AL3,Scores!AO11,Scores!AR21,Scores!AU2,Scores!AX18,Scores!BA17,Scores!BD15,Scores!BG11,Scores!BJ21,Scores!BM14,Scores!BP18,Scores!BS17,Scores!BV15,Scores!BY2,Scores!CB12,Scores!CE23,Scores!CH9,Scores!CK26,Scores!CN6,Scores!CQ29,Scores!CT21,Scores!CW30,Scores!CZ5,Scores!DC27,Scores!DF2,Scores!DI24)
J21J21=SUM(Scores!B20,Scores!E24,Scores!H29,Scores!K6,Scores!N29,Scores!Q18,Scores!T12,Scores!W14,Scores!Z3,Scores!AC8,Scores!AF24,Scores!AI11,Scores!AL21,Scores!AO14,Scores!AR3,Scores!AU17,Scores!AX15,Scores!BA20,Scores!BD12,Scores!BG17,Scores!BJ15,Scores!BM20,Scores!BP3,Scores!BS23,Scores!BV9,Scores!BY26,Scores!CB6,Scores!CE29,Scores!CH30,Scores!CK3,Scores!CN5,Scores!CQ27,Scores!CT8,Scores!CW24,Scores!CZ11,Scores!DC21,Scores!DF14,Scores!DI18)
D2:D21D2=E2+F2+G2
H2H2=SUMIF(Scores!A2:DI31,"Spain",Scores!B2:DJ31)
H3H3=SUMIF(Scores!A2:DI31,"Norway",Scores!B2:DJ31)
H4H4=SUMIF(Scores!A2:DI31,"Serbia",Scores!B2:DJ31)
H5H5=SUMIF(Scores!A2:DI31,"Switzerland",Scores!B2:DJ31)
H6H6=SUMIF(Scores!A2:DI31,"Scotland",Scores!B2:DJ31)
H7H7=SUMIF(Scores!A2:DI31,"Ireland",Scores!B2:DJ31)
H8H8=SUMIF(Scores!A2:DI31,"Italy",Scores!B2:DJ31)
H9H9=SUMIF(Scores!A2:DI31,"France",Scores!B2:DJ31)
H10H10=SUMIF(Scores!A2:DI31,"England",Scores!B2:DJ31)
H11H11=SUMIF(Scores!A2:DI31,"Portugal",Scores!B2:DJ31)
H12H12=SUMIF(Scores!A2:DI31,"Germany",Scores!B2:DJ31)
H13H13=SUMIF(Scores!A2:DI31,"Croatia",Scores!B2:DJ31)
H14H14=SUMIF(Scores!A2:DI31,"Poland",Scores!B2:DJ31)
H15H15=SUMIF(Scores!A2:DI31,"Wales",Scores!B2:DJ31)
H16H16=SUMIF(Scores!A2:DI31,"Ukraine",Scores!B2:DJ31)
H17H17=SUMIF(Scores!A2:DI31,"Netherlands",Scores!B2:DJ31)
H18H18=SUMIF(Scores!A2:DI31,"Belgium",Scores!B2:DJ31)
H19H19=SUMIF(Scores!A2:DI31,"Denmark",Scores!B2:DJ31)
H20H20=SUMIF(Scores!A2:DI31,"Russia",Scores!B2:DJ31)
H21H21=SUMIF(Scores!A2:DI31,"Turkey",Scores!B2:DJ31)
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Rank winning percentage for sport game
Replies
9
Views
690
Peter_SSs
Peter_SSs
C
  • Question
Excel - Countif by Cell Value AND Cell Color
Replies
1
Views
379
awoohaw
awoohaw
A
  • Solved
Calculate a sequence of results until a threshold is reached
Replies
5
Views
422
Anonymous1378
A
O
  • Solved
Icon Set Conditional Format from other cell value
Replies
3
Views
453
ollyhughes1982
O
S
  • Solved
Game Results
Replies
5
Views
564
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,790
Messages
6,121,608
Members
449,038
Latest member
apwr

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top