First Way: Using IF
| Number attending |
80 |
| Speaker system |
6 speakers |
| Speaker cost | =IF(B3=”4 speakers”,
IF(B1<50,equipment_hire_costs.csv!$B$3,equipment_hire_costs.csv!$C$3), IF(B1<50,equipment_hire_costs.csv!$B$4,equipment_hire_costs.csv!$C$4)) |
| Lighting system |
Coloured Lighting |
| Lighting cost | =IF(B6=”White lighting”,
IF(B1<50,equipment_hire_costs.csv!$B$8,equipment_hire_costs.csv!$C$8), IF(B1<50,equipment_hire_costs.csv!$B$9,equipment_hire_costs.csv!$C$9)) |
| Seating | plastic |
| Seating cost | =IF(B9=”plastic”,IF(B1>75,75,IF(B1>50,50,35)),
IF(B1>75,105,IF(B1>50,85,55))) |
| Security | 250 |
| Cost of event | =B4+B7+B10+B12 |
Second Way: Using If & Vlookup (column C is used instead B)
| Number attending |
80 |
| Speaker system |
6 speakers |
| Speaker cost | =IF(C1<=50,VLOOKUP(C3,equipment_hire_costs.csv!$A$3:$B$11,2,FALSE),
VLOOKUP(C3,equipment_hire_costs.csv!$A$3:$C$11,3,FALSE)) |
| Lighting system |
Coloured Lighting |
| Lighting cost | =IF(C1<=50,VLOOKUP(C6,equipment_hire_costs.csv!$A$3:$B$11,2,FALSE),
VLOOKUP(C6,equipment_hire_costs.csv!$A$3:$C$11,3,FALSE)) |
| Seating | plastic |
| Seating cost | =IF(C1>75,IF(C9=”plastic”,75,105),IF(C1>=50,IF(C9=”plastic”,50,85),
IF(C9=”plastic”,35,55))) |
| Security | 250 |
| Cost of event | =C4+C7+C10+C12 |
Third Way: Using If without referening to equipment_hire_costs.csv (column D is used instead B)
| Number attending |
88 |
| Speaker system |
6 speakers |
| Speaker cost | =IF(D3=”4 speakers”,IF(D1<50,150,175),IF(D1<50,225,250)) |
| Lighting system |
Coloured Lighting |
| Lighting cost | =IF(D6=”White lighting”,IF(D1<50,175,225),IF(D1<50,235,265)) |
| Seating | plastic |
| Seating cost | =IF(D1>75,IF(D9=”plastic”,75,105),IF(D1>=50,IF(D9=”plastic”,50,85),
IF(D9=”plastic”,35,55))) |
| Security | 250 |
| Cost of event | =D4+D7+D10+D12 |