|
Notes:
The font
colour option is used to change the colour of the text in columns D & E
Select the “format font” icon and choose the
colour that you want
D18
& E18 are calculated by using the SUM function to add D5 to D16 and E5
to E16
SUM(D5:D16)
F5 to
F16 are calculated as Win, Lost or Draw depending on our team scores by
using the IF statement
IF(D5>E5,"Win",IF(D5<E5,"Lost",IF(AND(D5<>0,E5<>0),"Draw","")))
Inserting text depending on a condition
Use the IF statement to evaluate the contents of
cells and then put the answer in this cell
The format
is IF(condition,true,false)
You can have nested IF statements so if the
condition is not true you can ask another question
IF (condition,true,if(condition,true,false),false))
You can have up to 64 nested IF statements but you
will then have a formula that is so complex that is will be almost
impossible to understand.
In this example the AND statement is used so that
the word “Draw” is only inserted if the scores are equal and greater than
zero. (The symbols <> mean “Not equal to”)
The
effective rate is our team score as a percentage of the total score
IF(OR(D5>0,E5>0),D5/(D5+E5),"")
The
calculation is only performed if either D5 or E5 is greater than zero
The
number of wins for our team is calculated from the results column using the
COUNTIF statement
IF(AND(D18=0,E18=0),"",IF(COUNTIF(F5:F16,"Win")=1,COUNTIF(F5:F16,"Win")&"
Win", COUNTIF(F5:F16,"Win")&" Wins"))
The
efficiency is calculated as the total home match score as a percentage of
the total match score
The same
technique is used in cell H20 to add the characters "% Eff" after the calculated figure
IF(AND(D18=0,E18=0),"",ROUND((D18/(D18+E18)*100),0)&"%
Eff")
The
colours of the results are calculated by using CONDITIONAL FORMATTING
Similarly
the colour of the cell for the success rate is calculated by looking to see
if there is information in cell H20
|