I eventually created a udf:

Function MyFunction(Councillors As Long, Employees As Long, WasteWater As
String, GeneralWaste As String, HazGoods As String, NoxiousPlants As String)
Dim YesNoString As String
Const WasteWaterRate = 1.05
Const GeneralWasteRate = 1.05
Const HazGoodsRate = 1.1
Const NoxiousPlantsRate = 1.025

YesNoString = WasteWater & GeneralWaste & HazGoods & NoxiousPlants

If YesNoString = "YYYY" Then
MyFunction = ((((((Councillors * 80) + (Employees * 55)) *
WasteWaterRate) * GeneralWasteRate) * HazGoodsRate) * NoxiousPlantsRate)
ElseIf YesNoString = "YYYN" Then
MyFunction = (((((Councillors * 80) + (Employees * 55)) *
WasteWaterRate) * GeneralWasteRate) * HazGoodsRate)
ElseIf YesNoString = "YYNN" Then
MyFunction = ((((Councillors * 80) + (Employees * 55)) *
WasteWaterRate) * GeneralWasteRate)
ElseIf YesNoString = "YNNN" Then
MyFunction = (((Councillors * 80) + (Employees * 55)) *
WasteWaterRate)
ElseIf YesNoString = "NNNN" Then
MyFunction = ((Councillors * 80) + (Employees * 55))
ElseIf YesNoString = "NNNY" Then
MyFunction = (((Councillors * 80) + (Employees * 55)) *
NoxiousPlantsRate)
ElseIf YesNoString = "NNYY" Then
MyFunction = ((((Councillors * 80) + (Employees * 55)) *
HazGoodsRate) * NoxiousPlantsRate)
ElseIf YesNoString = "NYYY" Then
MyFunction = (((((Councillors * 80) + (Employees * 55)) *
GeneralWasteRate) * HazGoodsRate) * NoxiousPlantsRate)
ElseIf YesNoString = "YNYN" Then
MyFunction = ((((Councillors * 80) + (Employees * 55)) *
WasteWaterRate) * HazGoodsRate)
ElseIf YesNoString = "YNYY" Then
MyFunction = (((((Councillors * 80) + (Employees * 55)) *
WasteWaterRate) * HazGoodsRate) * NoxiousPlantsRate)
ElseIf YesNoString = "NYNY" Then
MyFunction = ((((Councillors * 80) + (Employees * 55)) *
GeneralWasteRate) * NoxiousPlantsRate)
ElseIf YesNoString = "YYNY" Then
MyFunction = (((((Councillors * 80) + (Employees * 55)) *
WasteWaterRate) * GeneralWasteRate) * NoxiousPlantsRate)
ElseIf YesNoString = "NYYN" Then
MyFunction = ((((Councillors * 80) + (Employees * 55)) *
GeneralWasteRate) * HazGoodsRate)
ElseIf YesNoString = "YNNY" Then
MyFunction = ((((Councillors * 80) + (Employees * 55)) *
WasteWaterRate) * NoxiousPlantsRate)
ElseIf YesNoString = "NYNN" Then
MyFunction = (((Councillors * 80) + (Employees * 55)) *
GeneralWasteRate)
ElseIf YesNoString = "NNYN" Then
MyFunction = (((Councillors * 80) + (Employees * 55)) *
HazGoodsRate)
End If

End Function

There's no doubt a better way but it works!!
--
Thanks,
MarkN


"Dav" wrote:


vlookup is a way to go

you can concatenate the 4 responses d1&e1&f1&g1 and match using false
as the 4th parameter to ensure an exact match. without know what
formula needs to be returned for each of your 16 scenarios it is
impossbile to be more specific

If you provide more information, as to the other formulas i could be
more helpful

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=555910