Let's suppose C1 contains the multiplied total of A1 and B1. D1, E1, F1 and
G1 contain either a "Y" or "N". If all four of these cells contain a "Y", I
need =((((C1+2%)+3%)+4%)+5%). Of course, there are 256 "Y" and "N"
combinations.

I would appreciate any suggestions or advice, but would love a workable
solution!!
--
Thanks,
MarkN

if(and(d1="Y",e1="Y",f1="Y",g1="Y"),((((C1+2%)+3%) +4%)+5%),"")

if you have only 4 Y/N options why are there 256 combinations? are
there not 2x2x2x2=16?

Or am I missing something!

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

I was desparately trying to get back to this before somebody pointed out that
there are only 16 possible results. Thanks for your prompt response, but I
need a formula that includes an answer for the other 15 scenarios. I am going
to do this with a VLOOKUP.
--
Thanks again,
MarkN


"Dav" wrote:


if(and(d1="Y",e1="Y",f1="Y",g1="Y"),((((C1+2%)+3%) +4%)+5%),"")

if you have only 4 Y/N options why are there 256 combinations? are
there not 2x2x2x2=16?

Or am I missing something!

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

How about

=C1*(1+((D1="Y")*2%)+((E1="Y")*3%)+((F1="Y")*4%)+( (G1="Y")*5%))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"MarkN" wrote in message
...
I was desparately trying to get back to this before somebody pointed out
that
there are only 16 possible results. Thanks for your prompt response, but I
need a formula that includes an answer for the other 15 scenarios. I am
going
to do this with a VLOOKUP.
--
Thanks again,
MarkN


"Dav" wrote:


if(and(d1="Y",e1="Y",f1="Y",g1="Y"),((((C1+2%)+3%) +4%)+5%),"")

if you have only 4 Y/N options why are there 256 combinations? are
there not 2x2x2x2=16?

Or am I missing something!

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

Nel post
*MarkN* ha scritto:

Let's suppose C1 contains the multiplied total of A1 and B1. D1, E1,
F1 and G1 contain either a "Y" or "N". If all four of these cells
contain a "Y", I need =((((C1+2%)+3%)+4%)+5%). Of course, there are
256 "Y" and "N" combinations.

I would appreciate any suggestions or advice, but would love a
workable solution!!

I'm not sure to have well understood what do you mean, but you can try this:

=((((C1+2%*(D1="Y"))+3%*(E1="Y"))+4%*(F1="Y"))+5%* (G1="Y"))

(and the combinationsof Y and N shoul be 2^4 so just 16... ;-) )


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

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

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

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

If I have read your function correctly


=a1*80*b1*55*if(d1=”Y”,1.05,1) *if(e1=”Y”,1.05,1) *if(f1=”Y”,1.1,1)
*if(g1=”Y”,1.025,1)

Of course the 1.05 could be referenced by the cells which hold the
values in case you had to update them in the future, but if what you do
works, that’s all that matters

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

Dav,

Thanks very much for your post, and indeed it does work. I am always amazed
by how much there is about Excel that I don't know.

--
Thanks again,
MarkN


"Dav" wrote:


If I have read your function correctly


=a1*80*b1*55*if(d1=€�Y€�,1.05,1) *if(e1=€�Y€�,1.05,1) *if(f1=€�Y€�,1.1,1)
*if(g1=€�Y€�,1.025,1)

Of course the 1.05 could be referenced by the cells which hold the
values in case you had to update them in the future, but if what you do
works, thats all that matters

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