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

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

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

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:

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.

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

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

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