- My Forums
- Tiger Rant
- LSU Recruiting
- SEC Rant
- Saints Talk
- Pelicans Talk
- More Sports Board
- Fantasy Sports
- Golf Board
- Soccer Board
- O-T Lounge
- Tech Board
- Home/Garden Board
- Outdoor Board
- Health/Fitness Board
- Movie/TV Board
- Book Board
- Music Board
- Political Talk
- Money Talk
- Fark Board
- Gaming Board
- Travel Board
- Food/Drink Board
- Ticket Exchange
- TD Help Board
Customize My Forums- View All Forums
- Show Left Links
- Topic Sort Options
- Trending Topics
- Recent Topics
- Active Topics
Started By
Message
Google Sheets Formula Question
Posted on 3/27/25 at 3:06 pm
Posted on 3/27/25 at 3:06 pm
Good afternoon!
I am a high school soccer coach, and I am in charge of keeping the district standings sheet up to date.
We have a "goal differential" stat for each team. Each team is allowed a maximum of +5 goals for, and -5 goals against... Example: Team A wins the game 7-0 over Team B. Team A only gets +5, and Team B only gets -5 due to the max/min rule.
I am trying to get a formula to "cap" the value at a certain number in the differential column. So using that scenario above, under Team A, their GD (goal differential) for that game would be 5, and Team B would be -5, even though technically Team A won by 7.
The sheet is set up like this:
How can I get M3 (under AND) to show only 5 and not 7?
I am a high school soccer coach, and I am in charge of keeping the district standings sheet up to date.
We have a "goal differential" stat for each team. Each team is allowed a maximum of +5 goals for, and -5 goals against... Example: Team A wins the game 7-0 over Team B. Team A only gets +5, and Team B only gets -5 due to the max/min rule.
I am trying to get a formula to "cap" the value at a certain number in the differential column. So using that scenario above, under Team A, their GD (goal differential) for that game would be 5, and Team B would be -5, even though technically Team A won by 7.
The sheet is set up like this:
How can I get M3 (under AND) to show only 5 and not 7?
This post was edited on 3/27/25 at 3:20 pm
1
Posted on 3/27/25 at 3:20 pm to lovethetigers7
Here's one way:
Put that formula in B1 for example, and A1 is the score diff cell. It says that if A1 is greater than 5, put 5 in B1. If A1 is less than -5, put -5 in B1. And finally if A1 is between -6 and 6, just put A1 in B1.
Seeing your sheet now, using a formula will be no better than manual calcs with that layout. I think you need to change your schedule and scorekeeping to something consistent. There might be some spreadsheet wizard who can use that layout and do lookups or something crazy, but I'm not that wizard.
quote:
=IFS(A1>5, 5, A1<-5, -5, ISBETWEEN(A1,-6,6), A1)
Put that formula in B1 for example, and A1 is the score diff cell. It says that if A1 is greater than 5, put 5 in B1. If A1 is less than -5, put -5 in B1. And finally if A1 is between -6 and 6, just put A1 in B1.
Seeing your sheet now, using a formula will be no better than manual calcs with that layout. I think you need to change your schedule and scorekeeping to something consistent. There might be some spreadsheet wizard who can use that layout and do lookups or something crazy, but I'm not that wizard.
This post was edited on 3/27/25 at 3:34 pm
Posted on 3/27/25 at 4:23 pm to Korkstand
I agree, in the current format, it's going to be really difficult to do with formulas.
Can you restructure the sheet?
ETA: The only way I see to do it with the current format would be to use google scripting to process the sheet.
Can you restructure the sheet?
ETA: The only way I see to do it with the current format would be to use google scripting to process the sheet.
This post was edited on 3/27/25 at 4:31 pm
Posted on 3/28/25 at 10:56 am to Korkstand
quote:
Here's one way:
quote:
=IFS(A1>5, 5, A1<-5, -5, ISBETWEEN(A1,-6,6), A1)
Put that formula in B1 for example, and A1 is the score diff cell. It says that if A1 is greater than 5, put 5 in B1. If A1 is less than -5, put -5 in B1. And finally if A1 is between -6 and 6, just put A1 in B1.
I added another section and this worked! Thank you!
Posted on 3/28/25 at 11:12 am to Korkstand
Alright I have another request... These games won't be played until next season. I have dashes put into the results cells, and so it's returning the following "#VALUE!"
How do I get it to return a blank cell instead of #VALUE! ?????
How do I get it to return a blank cell instead of #VALUE! ?????
Posted on 3/28/25 at 11:27 am to lovethetigers7
Use iferror formula
Page 1 of 1
Popular
Back to top
Follow TigerDroppings for LSU Football News