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
>
>