+ Reply to Thread
Results 1 to 9 of 9

256 possible outcomes

  1. #1
    MarkN
    Guest

    256 possible outcomes

    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

  2. #2
    Forum Expert
    Join Date
    09-09-2005
    Location
    England
    MS-Off Ver
    2007
    Posts
    1,500
    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
    Last edited by Dav; 06-27-2006 at 04:09 AM.

  3. #3
    MarkN
    Guest

    Re: 256 possible outcomes

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


  4. #4
    Bob Phillips
    Guest

    Re: 256 possible outcomes

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    > >
    > >




  5. #5
    Franz Verga
    Guest

    Re: 256 possible outcomes

    Nel post news:[email protected]
    *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



  6. #6
    Forum Expert
    Join Date
    09-09-2005
    Location
    England
    MS-Off Ver
    2007
    Posts
    1,500
    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

  7. #7
    MarkN
    Guest

    Re: 256 possible outcomes

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


  8. #8
    Forum Expert
    Join Date
    09-09-2005
    Location
    England
    MS-Off Ver
    2007
    Posts
    1,500
    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

  9. #9
    MarkN
    Guest

    Re: 256 possible outcomes

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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1