+ Reply to Thread
Results 1 to 13 of 13

Formula for Calculating Win-Loss amount based on certain Criteria

  1. #1
    Forum Contributor Brian.Aerojet's Avatar
    Join Date
    03-25-2017
    Location
    TN-USA
    MS-Off Ver
    2016 365
    Posts
    156

    Formula for Calculating Win-Loss amount based on certain Criteria

    Hello Everyone,

    I have an unusual formula I need, this is normal for me here on Excel Forums.

    I want to take it a step further later on, but for now it will be confusing enough to get this worked out.

    My formula will look at 3 Digit Numbers (Column B) and also look at 2 Digit "Paired Numbers" (Column G) to determine how much the outcome will be in Column D.

    The good part is, the winning amount can only be $750 or $1500 and this is determined by what 3 Digit Number is in Column B.

    The Cost amount in Column C is always the same for now (Later on after I get a feel for how this will work, i might need to change that.)

    I explain in the Attachment Sheet in more detail of what I'm looking for as an outcome.

    If you have any questions, please ask.

    Thanks,
    Brian
    Attached Files Attached Files
    Brian

  2. #2
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010
    Posts
    856

    Re: Formula for Calculating Win-Loss amount based on certain Criteria

    Here is a formula for D2 then copied down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    It matches your expected results in column E.

    Attached is an update of your workbook implementing the above. Not a promising start if you have additional complexity in mind
    Attached Files Attached Files

  3. #3
    Forum Contributor Brian.Aerojet's Avatar
    Join Date
    03-25-2017
    Location
    TN-USA
    MS-Off Ver
    2016 365
    Posts
    156

    Re: Formula for Calculating Win-Loss amount based on certain Criteria

    Worked perfect GeoffW283, Thanks for the quick help.

    Im still working on the next part that I talked about.

    What it will do is change the column C "Cost" to different amounts based on the paired numbers shown in column G.

    I only play the paired numbers that have hit in last 5 draws, its difficult for me to explain so it can be understood.

    Once i figure out how to word it and make example sheet for it, i will open this thread back.

    For now I will show it as solved.

    I do show it solved correct, and then if I add something similar then I open it back up?

    I'm not for sure on this, but i think thats the way it works.

    Thanks,
    Brian

  4. #4
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010
    Posts
    856

    Re: Formula for Calculating Win-Loss amount based on certain Criteria

    I do show it solved correct, and then if I add something similar then I open it back up?
    Yeah, I think that's OK. The more closely related the follow-on the better. The other option would be to create a new thread with a link back to this one.

    Thanks for the rep

  5. #5
    Forum Contributor Brian.Aerojet's Avatar
    Join Date
    03-25-2017
    Location
    TN-USA
    MS-Off Ver
    2016 365
    Posts
    156

    Re: Formula for Calculating Win-Loss amount based on certain Criteria

    I have worked on this sheet so that it will hopefully be understood of what I would like as my outcome.

    In this sheet you will notice Im wanting count back to the last time a paired number hit.

    I enter this manually myself, but the reason i want it in a formula is because of mistakes I make while entering data.

    Actually when making this sheet, I did miss count a number, so hopefully this will not happen in the future after getting a formula working.

    It was hard for me to explain what Im looking for. You will notice in the attachment more about this.

    Thanks,
    Brian
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010
    Posts
    856

    Re: Formula for Calculating Win-Loss amount based on certain Criteria

    The row-3 numbers have the property that the 1st digit is always smaller than the 2nd digit. I rely on this to simplify the search: I modified your col-F:H formulas (along the lines of my last post) to order the pair of digits smallest first, largest last.
    In F5: =--(SMALL(--MID($B5, {1,2,3}, 1), 1) & SMALL(--MID($B5, {1,2,3}, 1), 2))
    In G5: =--(SMALL(--MID($B5, {1,2,3}, 1), 1) & SMALL(--MID($B5, {1,2,3}, 1), 3))
    In H5: =--(SMALL(--MID($B5, {1,2,3}, 1), 2) & SMALL(--MID($B5, {1,2,3}, 1), 3))

    Note: in your posted workbook col-G was formatted as "text" - that needs to be reverted to "general".

    The following formula in D3 copied across to AG3 matches your expected results (I added row-4 to retain your expected results for easy verification).
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Note that this is an array formula and needs to be committed via CTRL-SHIFT-ENTER

    Your workbook with the above changes is attached.

    Let me know if this works for you.
    Attached Files Attached Files
    Last edited by GeoffW283; 05-14-2019 at 02:17 PM.

  7. #7
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010
    Posts
    856

    Re: Formula for Calculating Win-Loss amount based on certain Criteria

    One additional tweak: I added conditional formatting to row-3 to highlight values between 0 and five and above 51 per your requirement.
    Conditional formatting formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Revised workbook is attached.
    Attached Files Attached Files

  8. #8
    Forum Contributor Brian.Aerojet's Avatar
    Join Date
    03-25-2017
    Location
    TN-USA
    MS-Off Ver
    2016 365
    Posts
    156

    Re: Formula for Calculating Win-Loss amount based on certain Criteria

    Im having some trouble getting it to work, I have uploaded my original for you to look at.

    Look on sheet called cash3TN(1)
    Then look at C3 for the formula.

    I figure it is something simply Im not doing properly.


    Update: I see what the problem is on the 1st sheet called Cash 3 TN in Column B is one of the only cells I always have to input manually which is the actual 3 Digit winning number for that draw.

    In Columns BY, BZ and CA where the =--(Small formula is located on the other sheet called Cash 3 TN(1) I have it filled with future numbers to update automatically once I input the winning number back on sheet 1 (Cash 3 TN) this caused a "Value Error" because the formulas in row 3 are trying to calculate down past the last number I input because of the auto fill.

    I didn't know if maybe something can be done about this, or I will need to fill it down after each draw?



    Other than that, I'm so happy with this outcome, I tested it and everything worked perfect.

    You are great at this stuff, I have always asked for help on Excel Forum for these type of workbooks I do and have always received great help from gurus like you.

    I know I ask for results of very unusual formulas, but you guys always pull through for me and i can't thank you enough.



    Also, I like the tweak on the Conditional Format. I wish someone like you could take my entire workbook and get it to looking "Great"

    I have many workbooks that deal with Cash 3 Lottery, but this one is going to be my baby.


    If you dont care for me asking:

    What does the OR stand for with the Conditional Format formula?

    Also, what would be a conditional format formula to highlight doubled or tripled numbers like 112, 335, 555, 777, 442, etc.?

    Thanks,
    Brian
    Attached Files Attached Files
    Last edited by Brian.Aerojet; 05-15-2019 at 04:55 AM.

  9. #9
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010
    Posts
    856

    Re: Formula for Calculating Win-Loss amount based on certain Criteria

    Surrounding the BY17:CA17 formulas with iferror() then copying down looks like it fixes the #VALUE error. In C3:
    =IFERROR(--(SMALL(--MID($B17, {1,2,3}, 1),1) & SMALL(--MID($B17, {1,2,3}, 1),2)), "")
    =IFERROR(--(SMALL(--MID($B17, {1,2,3}, 1), 1) & SMALL(--MID($B17, {1,2,3}, 1), 3)), "")
    =IFERROR(--(SMALL(--MID($B17, {1,2,3}, 1),2) & SMALL(--MID($B17, {1,2,3}, 1),3)), "")

    There needs to be a tweak in the C3:AF3 formula to change COUNTA() to COUNT()
    Don't forget to C_S_E enter!

    What does the OR stand for with the Conditional Format formula?
    It's just a function that returns TRUE if either the first condition (D3<=5) is true OR the second condition (D3>51) condition is true (or if both are true, which of course can't happen in this particular case).

    I'll think about your CF question later - need to go out now though!

    Attached is your workbook with the above changes.
    Attached Files Attached Files

  10. #10
    Forum Contributor Brian.Aerojet's Avatar
    Join Date
    03-25-2017
    Location
    TN-USA
    MS-Off Ver
    2016 365
    Posts
    156

    Re: Formula for Calculating Win-Loss amount based on certain Criteria

    Thanks again GeoffW283,

    I forgot to add the conditional formatting that you had in the original, I have it added now, also with the updated formula to take care of the Value Error.

    I actually added a few more of the conditional formatting for colors to other parts of the workbook. From what I have seen from my other workbooks, the formatting and formuals like we have in this workbook will start to slow down calculating process as I continue adding Data. Of course this workbook is not near the size of the older workbooks I have. 339kb, I think as long as I stay below 1 or 2mb I should be good
    I have learned a lot in a year or so to keep from adding to much stuff. I already know some of the things I would take out of this workbook If I start having problems with it being to slow.


    For now everything is working like a charm now.

    Not to be nosy, but I would like to know if you are rewarded for helping people like me, or you just do it because you like it?
    You dont have to answer that question.

    You can get back to me when you have time regarding my other question about CF with the doubles and triples.

    I will show this thread as solved for now. I will probably come up on something else soon that i will need to start another thread on, lol.

    Thanks again for your help,
    Brian

  11. #11
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010
    Posts
    856

    Re: Formula for Calculating Win-Loss amount based on certain Criteria

    What would be a conditional format formula to highlight doubled or tripled numbers like 112, 335, 555, 777, 442, etc.?
    Do you mean in B17 and downwards?
    If so, then:
    Select B17:B500
    Conditional Formatting > New Rule > Use a formula to determine which cells to format
    Formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Set whatever formatting you want.

    That should do it.

    Not to be nosy, but I would like to know if you are rewarded for helping people like me, or you just do it because you like it?
    The only tangible "reward" is accumulating Reputation Points I suppose it's a bit like doing crosswords or sudokus or 538 puzzles but with the added benefit that maybe you are actually helping someone a little by solving a real-world problem. Another benefit is that I get to learn lots of stuff too!

  12. #12
    Forum Contributor Brian.Aerojet's Avatar
    Join Date
    03-25-2017
    Location
    TN-USA
    MS-Off Ver
    2016 365
    Posts
    156

    Re: Formula for Calculating Win-Loss amount based on certain Criteria

    That is cool, I have always wanted that feature, but had to many other things to update in workbook to worry about the highlighting of Doubles and Triples.

    I definitely agree with the benefit of learning lots of stuff. Im still light years behind people like you, but It helps me to learn when I come up with weird things like you have helped me with.

    I still have so many more things I want to update in this workbook, basically I want to type in the 3 digit winning number and everything else updates from formulas and filling down the page or copy and paste.

    You have definitely helped me from making simply mistakes when plugging in numbers, now with your formulas excel does it for me.


    Thanks,
    Brian

  13. #13
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010
    Posts
    856

    Re: Formula for Calculating Win-Loss amount based on certain Criteria

    Thanks for the feedback. I'll look forward to your next problem

+ 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