IT 9626 NOV_17_P4_Q3A

 

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