Help with color coding

[rmoore1977]

Hi guys,

I'm trying to track the winning % per team vs their division and the division. I wrote a macro function, it's not recognizing it on the version I'm uploading but it does work on my other one.

Please Login or Register  to view this content.

I added this formula to get excel to recognize the GREEN cells as wins, and RED cells as losses. If you look at A13 =100%, A14 = 0%, A15 = 1 for the formula in G2.
=IF(IsGreenCell(B3:E3),A15*($A$13),A15)-IF(IsRedCell(B3:E3),A15*($A$14),A15)/2. It isn't calculating correctly and coming up with .5 instead of 1.0 since they're undefeated. Can someone help me.

I learned how to write this VBA code from a YouTube video and kind of thought out how to write the formula along with the video, so this is my first time ever doing this so please if you respond it may sound like I know what I'm doing but I stumbled here so how and now I'm stuck.

Richard

[Bernie Deitrick]

VBA doesn't work in online Excel. You need to use Conditional Formatting with the formula option, writing a formula that uses the logic that is used to color the cells on the worksheet. Your worksheet is not set up to do that - you would either need to use another cell to indicate who won (or if it was a tie) or include the scores.....

[PrizeGotti]

VBA doesn't work in online Excel. You need to use Conditional Formatting with the formula option, writing a formula that uses the logic that is used to color the cells on the worksheet. Your worksheet is not set up to do that - you would either need to use another cell to indicate who won (or if it was a tie) or include the scores.....

Nothing mentioned about Excel online, and this isn't about formatting, it's a question about an incorrect calculation.

[Bernie Deitrick]

Really? I read this:

it's not recognizing it on the version I'm uploading

[rmoore1977]

Hey, thanks for responding. Yes I need help figuring out the correct formula to track winning percentages so that cell can calculate the value correctly. I'm using regular Excel not online. I wrote the VBA so that it would recognize RED, GREEN, and YELLOW as certain values. It came back TRUE when I tested it with this formula, =IsRedCell(D4), =IsGreenCell(B3), =IsYellowCell(B43). The number is exact, which ever cell is that color would go there. After it came back true I added the formula =IF(IsGreenCell(B3:F3),A45*(A43),A45)-IF(IsRedCell(B3:F3),A45*(A44),A45)/5.

=IF(IsGreenCell(B3:F3) is supposed to keep track of the amount of Green cells. A45* = 1, (A43)= 100%, IF(IsRedCell(B3:F3) keeps track of Red cells. (A44) = 0%

So for instance you have 14u bball league, there are 4 Divisions but I'll just use 2 for example. Ballers are in East Div and Hoopers are in West Div. I want to find out every time each team winning % vs that division. So I colored the cell green for a win and red for loss, and yellow for a tie. I made columns vs/ East Div, vs. West Div, vs. North Div, vs. South Div. The Ballers are 5-0 and Hoopers are 3-2. So under each column I want to add the formula so it automatically gives me a % by adding the Green(Wins) cells - Red(Losses) cells/ by #of games played.


vs. East Div

Ballers Green, Green, Green, Green, Green =IF(IsGreenCell(B3:F3),A45*(A43),A45)-IF(IsRedCell(B3:F3),A45*(A44),A45)/5 it's giving me .8 It should be 1

Hoopers Green, Green, Red, Green, Red =IF(IsGreenCell(B4:F4),A45*(A43),A45)-IF(IsRedCell(B4:F4),A45*(A44),A45)/5 it's giving me 0 It should be .6

If this formula were to work then I can tweak it to replace B3:F3 with the actual cells that correspond for each particular division, or give each division gets it's own color and write a VBA to recognize those colors and input that into the excel formula, or change the setup and do 4 different row vs columns and use the original formula I'm asking about. I just need to fix the calculations.

[protonLeah]

Is?Cell(B3:F3) only works if both cells are the same color, otherwise it returns zero.

I don't understand your explanation, e.g., "... The Ballers are 5-0 and Hoopers are 3-2 ..." doesn't relate (at least, to me) to the data on sheet1

[Bernie Deitrick]

Change your functions to count the number of cells, like so:

Please Login or Register  to view this content.

And then the function will directly count the number of cells that you have filled with each color.

[rmoore1977]

I replaced the vba code with the one you sent, but it still comes up .8 instead of 1.0.

Please Login or Register  to view this content.

[rmoore1977]

Is there a way to upload a pic or video to this thread?

[alansidman]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however your post does not comply with our guidelines

Please take a moment to review how to add code tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Guideline #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here.

As you are new to this forum, I have added the code tags for you today. Please take a few minutes to read our guidelines and rules and comply in the future