Showing league position names

[Mabeam]

Hello

I have excel documents with league tables in them.

At the end of the table i have a column that automatically shows where they are at the time.
It is all working great apart from one aspect.
At the top of the league i have, "CHAMP", "AUTO", "PLAY OFF"
So it will automatically show up when they are definitely in that area, but i cannot get "PLAY OFF" to show while in the top 2 places.

All we have to concentrate on is the top two formulas in the last column AK6 & AK7.

As you can see, in AK8 there is this formula: "=IF(OR(AND(S8>MAX(AJ$12:AJ$29),S8<MIN(AJ$6:AJ$7)),AND(SUM(AC8:AD8)=0, NOT(OR(AND(S8=S7,SUM(AC7:AD7)>0),AND(S9=S8,SUM(AC8:AD8)>0))))),"PLAYOFF")" which works perfectly.

In AK7 & AK6 it will automatically show when they are either "Auto" or "Champs", but i want it to also show that they are "Play Off" before they get to that stage, it looks wrong that 3rd place has "Play Off", but the other two do not.

In AK7 the formula is: "=IF(OR(AND(S7>MAX(AJ8:AJ29),S7<AJ6),AND(AND(S6=S7,SUM(AC6:AD7)=0),AND(S7=S8,SUM(AC7:AD8)=0))),"AUTO")"

In AK6 the formula is: "=IF(S6>MAX(AJ8:AJ29),IF(OR(S6>MAX(AJ7:AJ29),AND(S6=S6,SUM(AC6:AD7)=0)),"CHAMP","AUTO"))"

In effect i want...

AK7 to show "PLAY OFF" before it shows "AUTO"

AK6 To Show "PLAY OFF" before it shows "AUTO" before it shows "CHAMP"


Hopefully this is okay and you understand, if not i will try the upload document.


Thanks for looking

Regards

 

[Eric W]

Welcome to the MrExcel forum!

Do you ALWAYS want PLAY OFF to show in those 2 cells? If so, just change those formulas to:

="PLAYOFF/"&IF(OR(AND(S7>MAX(AJ8:AJ29),S7<AJ6),AND(AND(S6=S7,SUM(AC6:AD7)=0),AND(S7=S8,SUM(AC7:AD8)=0))),"AUTO")

="PLAYOFF/"&IF(S6>MAX(AJ8:AJ29),IF(OR(S6>MAX(AJ7:AJ29),AND(S6=S6,SUM(AC6:AD7)=0)),"CHAMP","AUTO"))

If it only shows up in some situations, just change the "PLAYOFF/"& to "IF(something,"PLAYOFF/","")&"

 

[Mabeam]

Welcome to the MrExcel forum!

Do you ALWAYS want PLAY OFF to show in those 2 cells? If so, just change those formulas to:

="PLAYOFF/"&IF(OR(AND(S7>MAX(AJ8:AJ29),S7<AJ6),AND(AND(S6=S7,SUM(AC6:AD7)=0),AND(S7=S8,SUM(AC7:AD8)=0))),"AUTO")

="PLAYOFF/"&IF(S6>MAX(AJ8:AJ29),IF(OR(S6>MAX(AJ7:AJ29),AND(S6=S6,SUM(AC6:AD7)=0)),"CHAMP","AUTO"))

If it only shows up in some situations, just change the "PLAYOFF/"& to "IF(something,"PLAYOFF/","")&"

Thanks Eric

No, i don't want it all the time, now that you have the formulas, i will explain (this sounds great in my head) the bottom, then the Top...

If you look at AK29 it has automatically shown "DOWN" because that cell cannot get as many points as AK26, the same for AK28 & AK27
These work perfectly

So if you take that to the top section...
AK11, AK10, AK9, AK8 will show "PLAY OFF" when they cannot be caught by any cell below AK11
These work perfectly

But then, this is where i need help...

AK7 & AK6 Will show "AUTO" when they cannot be caught by any cell below
These work but not perfectly
Then
AK6 Will show "CHAMP" when it cannot be caught by any cell below
This works but not perfectly

But

I want AK7 & AK6 to firstly, do what AK11, AK10, AK9, AK8 do & show "PLAY OFF" when they cannot be caught by any cell below AK11


I have just thought of another way to put this...

This is what is in AK7 "=IF(OR(AND(S7>MAX(AJ8:AJ29),S7<AJ6),AND(AND(S6=S7,SUM(AC6:AD7)=0),AND(S7=S8,SUM(AC7:AD8)=0))),"AUTO")" This works for "AUTO"
This is what i want AK7 to do as well "=IF(OR(AND(S7>MAX(AJ8:AJ29),S7<AJ6),AND(AND(S6=S7,SUM(AC6:AD7)=0),AND(S7=S8,SUM(AC7:AD8)=0))),"AUTO","PLAY OFF")"
I just want to make it do what AK11, AK10 AK9, AK8 does first, then the "AUTO" part

This is what is in AK6 "=IF(S6>MAX(AJ8:AJ29),IF(OR(S6>MAX(AJ7:AJ29),AND(S6=S6,SUM(AC6:AD7)=0)),"CHAMP","AUTO"))"
This is what i want AK6 to do as well "=IF(S6>MAX(AJ8:AJ29),IF(OR(S6>MAX(AJ7:AJ29),AND(S6=S6,SUM(AC6:AD7)=0)),"CHAMP","AUTO","PLAY OFF"))"
I just want to make it do what AK11, AK10 AK9, AK8 does first, then the "AUTO" part

Hope this is better explained

Regards

 

[Eric W]

This is a lot to wrap my head around.

First, are the rows always going to be sorted, I assume by points total?

Second, the AK column seems to be the playoff status, like "mathematically eliminated", or "guaranteed in", or "regular season champion", right? Can you explain how the playoffs work? How many get in? Is it all based on most points earned, or is there a play-in game sometimes? Can you explain what all the values in AK4:AK5 mean? How are they used? From your original question, it appears that multiple values can occur in the same cell, suggesting different criteria. Can you explain this without me having to decipher formulas? I can probably build new ones better from the explanation.

 

[Mabeam]

Thanks Eric
Everything is working great ( could do with it calculating them by POINTS and then by GOAL DEFFERENCE as well, but that is not the most important part right now) apart from the top 2 cells AK6 & AK7, they are only doing half of the job, i want them to firstly show PLAYOFF when they meet that criteria.

This is just a league table where teams play each other twice, no extra games, the TOP 2 teams are Automatically promoted, the teams in the PLAYOFF places AK8 to AK11 will play again and 1 team will be promoted, but that will not be used in this league table.

AK6 - will be the champions CHAMP - Promoted

AK7 - Will be Runners Up AUTO - Promoted

AK8 to AK11 - Will go into the PLAYOFF

AK12 to AK26 - Stay in this league

AK27 To AK29 - Will be relegated to the league below


If you read from the bottom (AK29) and work upwards you should get the idea i am trying to do for AK7 & AK6

AK6 - Will show AUTO first if it cannot be caught by any below AK7 THEN CHAMP when it cannot be caught by any other below - I want this to show PLAYOFF like AK11 to AK8 first, if it is the same scenario as AK11 to AK8

AK7 - Will show AUTO when it cannot be caught by any below AND cannot catch the above - I want this to show PLAYOFF like AK11 to AK8 first, if it is the same scenario as AK11 to AK8

AK11 to AK8 - Will show PLAYOFF when they cannot be caught by any below AND they cannot catch any above - This works okay

AK12 - Will show UNLUCKY when it cannot be caught by any below AND it cannot get enough points to catch any above - This works okay

AK25 to AK13 - Will show BORING when the bottom 4 cannot reach enough points to catch them AND they cannot get enough points to catch AK12 - This works okay

AK26 - Will show VERY LUCKY when that cell has more points than the bottom 3can reach - This works okay

AK27 - Will show DOWN when that cell can no longer get enough points to catch AK26 - This works okay

AK28 - Will show DOWN when that cell can no longer get enough points to catch AK26 - This works okay

AK29 - Will show DOWN when that cell can no longer get enough points to catch AK26 - This works okay


Thanks again for trying

Regards

 

[Eric W]

OK, give these a try:

AK6:

=IF(AJ6>MAX(AJ7:AJ29),"CHAMPS",IF(AJ6>LARGE(AJ6:AJ11,3),"AUTO",IF(AJ6>LARGE(AJ6:AJ26,7),"PLAYOFF","")))

AK7:

=IF(AJ7>LARGE(AJ6:AJ26,3),"AUTO",IF(AJ7>LARGE(AJ6:AJ26,7),"PLAYOFF",""))

 

[Mabeam]

OK, give these a try:

AK6:

=IF(AJ6>MAX(AJ7:AJ29),"CHAMPS",IF(AJ6>LARGE(AJ6:AJ11,3),"AUTO",IF(AJ6>LARGE(AJ6:AJ26,7),"PLAYOFF","")))

AK7:

=IF(AJ7>LARGE(AJ6:AJ26,3),"AUTO",IF(AJ7>LARGE(AJ6:AJ26,7),"PLAYOFF",""))

Thanks Eric

I'm off on a course all week, i'll try and have a look but it might be next week.

Thanks for looking

Regards

 

[Eric W]

I haven't actually tested these formulas, since I didn't want to retype your entire worksheet. (Check out the XL2BB tool.) But in thinking about it, I realized I made a mistake, these should be the formulas:

=IF(S6>MAX(AJ7:AJ29),"CHAMPS",IF(S6>LARGE(AJ6:AJ11,3),"AUTO",IF(S6>LARGE(AJ6:AJ26,7),"PLAYOFF","")))

=IF(S7>LARGE(AJ6:AJ26,3),"AUTO",IF(S7>LARGE(AJ6:AJ26,7),"PLAYOFF",""))

Let me know when you get a chance to try them, no hurry.

 

[Mabeam]

I haven't actually tested these formulas, since I didn't want to retype your entire worksheet. (Check out the XL2BB tool.) But in thinking about it, I realized I made a mistake, these should be the formulas:

=IF(S6>MAX(AJ7:AJ29),"CHAMPS",IF(S6>LARGE(AJ6:AJ11,3),"AUTO",IF(S6>LARGE(AJ6:AJ26,7),"PLAYOFF","")))

=IF(S7>LARGE(AJ6:AJ26,3),"AUTO",IF(S7>LARGE(AJ6:AJ26,7),"PLAYOFF",""))

Let me know when you get a chance to try them, no hurry.

Hiya Eric

I have had a little play and changed the scores around to mimic the position scenario.

Just one thing if i may...









You, my friend are an absolute star.

As far as i can see, everything changes perfectly.

Cannot thank you enough pal

When the table changes in real time in the coming weeks, that will be the test, but i am confident you have got it spot on



Thank You

Regards

 

[Mabeam]

Hiya Eric

Just to say, do not get too comfortable just yet.

There is something else i need to calculate into them for the next Season, in August.

I will have a play around, but...

I might be back



Thanks again

Regards