- 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
Calling excel/sheets gurus... please help
Posted on 8/21/23 at 12:15 pm
Posted on 8/21/23 at 12:15 pm
Have a football stat counter for high school. Got it pretty much lined out how I want it but I'm needing a way to count .5 Sacks in a players total.
So currently I have "sack" in the E column...E4:E101, and "assist" in the F column...F4:F101. If I just put say #7 in E column it counts it Q column where it should. I want it to add .5 if I have a player in E and in F to there corresponding Q column spot if that makes sense. Please let me know if there is a way I could explain it any better or let whoever look at my sheet
So currently I have "sack" in the E column...E4:E101, and "assist" in the F column...F4:F101. If I just put say #7 in E column it counts it Q column where it should. I want it to add .5 if I have a player in E and in F to there corresponding Q column spot if that makes sense. Please let me know if there is a way I could explain it any better or let whoever look at my sheet
5
Posted on 8/21/23 at 12:38 pm to H2A2
Use ChatGPT or Google Bard or Bing Chat. They are very good at giving you Excel formulas if you spell out what you have, where you have it, and what you want to get.
Posted on 8/21/23 at 12:46 pm to H2A2
use the same formula to count the F column as you are the E but multiply it times .5
Q would look something like
=countif(E:E,"sack")+(countif(F:F,"assist")*.5)
Q would look something like
=countif(E:E,"sack")+(countif(F:F,"assist")*.5)
Posted on 8/22/23 at 1:22 pm to TigerinATL
I don't know why you are being downvoted. I think at some point this feature is coming to apps like Excel anyway. Especially useful for highly complicated or tedious formulas with a lot of operators. Microsoft already has a partnership with OpenAI so this is feasible.
I pasted your post in ChatGPT word for word and got the following formula:
=SUM(E4:E101) + (SUM(F4:F101) * 0.5)
I pasted your post in ChatGPT word for word and got the following formula:
=SUM(E4:E101) + (SUM(F4:F101) * 0.5)
This post was edited on 8/22/23 at 1:26 pm
Posted on 8/22/23 at 2:20 pm to H2A2
Happy to help, but you are not describing it very well.
This post was edited on 8/22/23 at 2:24 pm
Posted on 8/22/23 at 6:04 pm to H2A2
A little more info would help. For instance is each row a player? Or is each row a play so you put a player number Like #7? It is not clear what you have. Maybe take a snip of it for us to see.
But if I understand what you are doing, this may work. In this case P contains the player number so if #7 occurs anyplace in E4:E101 it will count it as a sack but will subtract half a sack for every time #7 occurs in both E4:E101 and F4:F101.
Hope that helps
=COUNTIF(E4:E101,P13) - (COUNTIFS(E4:E101,K13,F4:F1101,P13)*0.5)
But if I understand what you are doing, this may work. In this case P contains the player number so if #7 occurs anyplace in E4:E101 it will count it as a sack but will subtract half a sack for every time #7 occurs in both E4:E101 and F4:F101.
Hope that helps
=COUNTIF(E4:E101,P13) - (COUNTIFS(E4:E101,K13,F4:F1101,P13)*0.5)
Posted on 8/22/23 at 6:05 pm to ThatBaw
ChatGPT does not understand football 
Posted on 8/22/23 at 8:01 pm to TigerinATL
One thing that ChatGPT is great with is code. If it doesn't work, you tell it the result and it almost always gets it right after that.
Posted on 8/24/23 at 7:31 am to H2A2
quote:
chatGPT
Here’s the formula you'd use in Column Q to calculate the total sacks for each player:
1. Click on the first cell in Column Q where you want the total to appear, let's say Q2.
2. Enter the formula: ```excel =E2 + (F2 * 0.5) ```
3. Press Enter.
4. Drag the bottom right corner of the Q2 cell down to fill the formula for all football players.
This will sum up the whole sacks from Column E with half of the assisted sacks from Column F for each player in Column Q
Posted on 8/25/23 at 2:17 am to holdem Tiger
Appreciate the answers back. Let me try to answer a few of the questions.
1.) Each row is a play.
So for example.. c cell is for a solo tackle so I would put 7 in the cell C4 if the player makes a tackle by himself. That is then tallied to that players tackle counter I have in O10 in his "Solo" total. If it's a TFL it goes in D cell > P10, Sack goes in E cell> Q10.
The problem I'm running in to currently is if a player "assists" which I have as my F column. I want it to count as .5 for both the players. Whoever is C,D or E and whoever is in F, depending which type of play it is. If it is a regular tackle with a Assist goes to R cell .5 for both players, TFL assist goes to each player S column counter and Sack goes to each players T column counter as .5.
2.) Current formula I use for each cell on the counting side of sheet... players numbers are in U column U4:U22 ...=countif(F$4:F$101,$U10).
1.) Each row is a play.
So for example.. c cell is for a solo tackle so I would put 7 in the cell C4 if the player makes a tackle by himself. That is then tallied to that players tackle counter I have in O10 in his "Solo" total. If it's a TFL it goes in D cell > P10, Sack goes in E cell> Q10.
The problem I'm running in to currently is if a player "assists" which I have as my F column. I want it to count as .5 for both the players. Whoever is C,D or E and whoever is in F, depending which type of play it is. If it is a regular tackle with a Assist goes to R cell .5 for both players, TFL assist goes to each player S column counter and Sack goes to each players T column counter as .5.
2.) Current formula I use for each cell on the counting side of sheet... players numbers are in U column U4:U22 ...=countif(F$4:F$101,$U10).
This post was edited on 8/25/23 at 2:43 am
Posted on 8/25/23 at 2:19 am to Locoguan0
I tried Google bard and it didn't give the desired result maybe it was on my end but I was explaining the best I could. I'm willing to send a copy of the sheet if anyone would like to toy around with it.
Posted on 8/25/23 at 2:23 am to mdomingue
Posted on 8/25/23 at 12:43 pm to H2A2
This is the best I could come up with. If you have stuff above or below your intended data, you can change the complete column references to you desired range (i.e. change from "C:C" to "C4:C101"). If you don't have data that might get caught up in your calcs, then it should prove to be a little easier for continued use since you won't have to worry about the number of plays exceeding your current reference.
Starting in cell:
M4: =SUM(N4:S4)
N4: =COUNTIF(C:C,$T4)-(COUNTIFS(C:C,$T4,$F:$F,">0")*0.5)
O4: =COUNTIF(D:D,$T4)-(COUNTIFS(D:D,$T4,$F:$F,">0")*0.5)
P4: =COUNTIF(E:E,$T4)-(COUNTIFS(E:E,$T4,$F:$F,">0")*0.5)
Q4: =COUNTIFS($F:$F,$T4,C:C,">0")*0.5
R4: =COUNTIFS($F:$F,$T4,D:D,">0")*0.5
S4: =COUNTIFS($F:$F,$T4,E:E,">0")*0.5
N-P is counting the number of times the player's number appears in C-E and then subtracting .5 each time there is a number in F since they were assisted.
Q-S is counting the where each player had an assisted tackle based on the type (C-E) and multiplying it by 1/2.
Starting in cell:
M4: =SUM(N4:S4)
N4: =COUNTIF(C:C,$T4)-(COUNTIFS(C:C,$T4,$F:$F,">0")*0.5)
O4: =COUNTIF(D:D,$T4)-(COUNTIFS(D:D,$T4,$F:$F,">0")*0.5)
P4: =COUNTIF(E:E,$T4)-(COUNTIFS(E:E,$T4,$F:$F,">0")*0.5)
Q4: =COUNTIFS($F:$F,$T4,C:C,">0")*0.5
R4: =COUNTIFS($F:$F,$T4,D:D,">0")*0.5
S4: =COUNTIFS($F:$F,$T4,E:E,">0")*0.5
N-P is counting the number of times the player's number appears in C-E and then subtracting .5 each time there is a number in F since they were assisted.
Q-S is counting the where each player had an assisted tackle based on the type (C-E) and multiplying it by 1/2.
Posted on 8/25/23 at 7:47 pm to MiloTealeaf
This might work trying to input info to test it out thank you for your help, I'll report back after I mess around with it next couple days before game night
Posted on 8/26/23 at 2:08 pm to MiloTealeaf
Seems to be working great really appreciate your help
Page 1 of 1
Popular
Back to top
Follow TigerDroppings for LSU Football News