sum areas in 81x81 matrix

[samot79]

Hi

I have a big matrix in Excel where I want to sum cells given three different criteria. I am trying to calculate probability for a team to qualify in Champions League given correct score table from 1st and 2nd leg. UEFA rule is that away goals is worth more. If 1st Leg ends (A vs B) 2-1 and 2nd Leg ends (B vs A)1-0, team B will qualify.


Attached you find CS Matrix for 1st and 2nd league. I want to sum cells where Team A qualify, team B qualify and outcomes that would lead to extra time. That is for example 1st leg 0-0 and 2nd leg 0-0.

I have tried to do this with conditional formatting and sum on certain colors, but I dont get conditional correct.

[David A Coop]

Sorry Samot,

I am missing the question! Please give an example of what you are trying to add up.

Regards

David

[samot79]

Hi

I will try

I want to add up all cells where Team A qualify and where Team B qualify.

I give some examples. Imagine 1st leg with A vs B and 2nd leg B vs A.

Cell B21 with Team A winning 2-0 1st leg and draw 0-0 2nd leg would make Team A to qualify. Probability of that exact outcome is 0.004281, this cell will be added up to sum of probability for team A to qualify.

Cell C14 would make Team B to qualify. Team B win first match with 2-1 and loses 2nd match with 1-0. Aggregated score is tie (2-2) but away goal is worth more, so Team B qualify with more goals away.

Hard nut to crack

Helpful for all help I can get here. As I said before, I am thinking that conditional formatting can solve the issue, but not sure how I should write those conditions.

[samot79]

Is it possible to sum cells given their color, without conditional formatting?

[David A Coop]

Hi Samot,

I understand all that. But what is the reference point? Are you entering the scores somewhere so that the matrix can be referenced?
What is it that causes a conditional format to color a cell?

I also don't get adding up the probabilities. I assume you mean that Teams A & B play each other multiple times and you want to know the sum of those probabilities.

There is no connection that I can see between the probabilities, and who qualifies. I assume this has nothing to do with the current question.

There is a piece of this puzzle missing!

Is it possible to sum cells given their color, without conditional formatting?

Yes, but you need a UDF (User Defined Macro) to accomplish this.

Regards,

David

[FDibbins]

1. what deterime if a team qualifies? How do we ID what needs to be added?
2. It looks to me that there is no repeating of info in column A and row 2? So what would you be adding> (maybe the answer to 1 will help clear that up)
3. If there is nothing to add, then perhaps you just want the intersection of the 2 legs?
4.

I have tried to do this with conditional formatting and sum on certain colors, but I dont get conditional correct.

Conditional formatting does just that - it Formats cells (changes forns and colors), nothing more, nothing less...it cannot be used to do calcs.
Also, you cannot run calcs on colors using regular formulas. excel uses data for calcs, formatting is not data, it is cosmetics

[David A Coop]

I had a brain wave! It this what you're looking for? (Or part of it)

I have tried to apply conditional formatting so that Team A qualifies shows as green (Dark green where it is due to away goals) and Team B is shown as blue.

If so, the next challenge is the adding up you were looking at, and I have no clear idea of what is required yet.

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[samot79]

I had a brain wave! It this what you're looking for? (Or part of it)

I have tried to apply conditional formatting so that Team A qualifies shows as green (Dark green where it is due to away goals) and Team B is shown as blue.

If so, the next challenge is the adding up you were looking at, and I have no clear idea of what is required yet.

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

This is exactly what I was looking for! Team A to qualify = sum of all green and dark green cells. I was planning to do this with a VBA macro.

Yes, correct score table with probabilites for each result are stored on a different place in sheet. I use these to populate CS matrix both legs with probs.

Issue not yet completely solved, but I have get very far thanks to all help.

[samot79]

Apparently not as easy as I hoped.. Seems quite impossible to sum cells on their background color when I used conditional formatting. Is it any way to apply formula for conditional formatting in SUMIFS?

[David A Coop]

I have attached a new version with a UDF I have called SUMFMT(). You select the range and the format cell sample, and it adds everything up for you.

It has a problem which I can't figure out. Perhaps Ford can help. When I click F9 to recalculate, it bombs and I get a #VALUE error. I have to press F2 then enter to get it to calculate again properly.

Here it the model. I don't really know why you want to add it up. It gives the answer I expected with both A an B being identical, and the Draw scenario making up the total to 1.000.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[samot79]

Strange, function only returns #VALUE for me. It works for me in your file when I enter the formula.. but doesnt seem to be able and paste it to new workbook. I pasted function from VBA editor as well.. could it be that I used different colors?

[David A Coop]

Yes, I note that! If you click in the cells (G1, K1, and N1), hit F2 then Enter, it should give you the result. Clearly unsatisfactory.

I don't know why this is happening! I have referred it to a wiser man than I for some advice.

We will hopefully get back to you!

Regards,

David

[samot79]

Your efforts are truly wise and really appreciated

Not sure if you seen this but if you set workbook calculation to automatic, it is not even possible to enter formula to get result. This has to be put to manual in order to make function to work. This goes for all formula in workbook.

If I for instance set D11 = C11-C10, I have to enter this formula as well to make it recalculate. A bit strange?


Just realized something funny. The whole matrix is actually a formula. Each cell has a formula like this,

"=IF($C$3="",$AD2*INDIRECT(ADDRESS(COLUMN(B$2),41)),1*INDIRECT(ADDRESS(COLUMN(B$2),41)))"

In example I gave you correct score tables are identical, but that is of course not the case for all play off matches.


Would it be possible to make the function enter each formula in sheet? In order to make it recalculate?

[David A Coop]

I don't know - I just can't figure it out!

BTW when I enter that formula - I just get a date. INDIRECT(ADDRESS(COLUMN(B$2),41)) is picking up "4 - 3" and then assuming it is 4 March 2015.

David

[David A Coop]

I give up on the USER DEFINED FORMAT! I just can't see why it fails!

Here is another approach. Click the button.

The weakness with this is that the cell references are in the code. This should not be an issue since I would expect the decision matrix to be static (even though the probability values may vary).

Sorry I haven't been able to get it right!

David

[David A Coop]

I give up on the USER DEFINED FORMAT! I just can't see why it fails!

I know what the problem it! It's the code that I used to get the CF colour. DisplayFormat.Interior.Color etc.

It can't be used in a UDF for some reason, but works fine in a regular macro.

If the cells were coloured manually, then the macro will work with some minor modification.

Regards, David