SOLVED

Need Formulas For Stats

%3CLINGO-SUB%20id%3D%22lingo-sub-3293025%22%20slang%3D%22en-US%22%3ENeed%20Formulas%20For%20Stats%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3293025%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20creating%20a%20stat%20tracker%20for%20my%20baseball%20league.%20I%20have%20set%20up%20multiple%20formulas%20already%20but%20there%20are%20a%20few%20I%20would%20love%20to%20know%20if%20are%20possible.%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example...%3C%2FP%3E%3CP%3Eif%20B3%20is%20the%20Wins%20column%2C%20and%20C4%20is%20Losses%2C%20is%20there%20a%20way%20for%20them%20to%20recognize%20which%20number%20is%20greater%20from%20the%20score%20submission%20columns%20(K3%2C%20L3%20week%20one%20scores)%20then%20(M3%2C%20N3%20week%20two%20scores)%20etc...%20and%20automatically%20increase%20by%20%2B1%20in%20the%20wins%20or%20loss%20columns%3F%3C%2FP%3E%3CP%3EAlso%20we%20have%20sportsmanship%20rating%20and%20each%20week%20a%20team%20submits%20a%20score%20out%20of%2010%20ie.(7%2F10)...%20This%20I%20have%20recorded%20in%20E3.%20Is%20there%20a%20way%20to%20have%20it%20automatically%20add%20the%20new%20number%20to%20the%20existing%20one%20entered%3F%20So...%20if%20the%20cell%20currently%20reads%2020%2C%20i%20just%20select%20it%20and%20type%207%2C%20and%20it%20autosums%20to%2027.%3C%2FP%3E%3CP%3EEverything%20else%20I%20have%20figured%20out%20already%20but%20any%20help%20would%20be%20appreciated.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3293025%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3299233%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Formulas%20For%20Stats%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3299233%22%20slang%3D%22en-US%22%3EYou%20are%20very%20welcome%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3299217%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Formulas%20For%20Stats%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3299217%22%20slang%3D%22en-US%22%3EThank%20you%20very%20much%20for%20your%20help%2C%20formula%2C%20and%20time.%20I%20do%20appreciate%20as%20it%20will%20do%20perfectly.%3CBR%20%2F%3EThanks%20again%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3298819%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Formulas%20For%20Stats%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3298819%22%20slang%3D%22en-US%22%3EIf%20you%20have%20access%20to%20365%20and%20LAMBDA...%3CBR%20%2F%3E%3CBR%20%2F%3EWin%20column%3A%20%3DSUM(MAP(K3%3AAT3%2CLAMBDA(x%2CIF(AND(ISODD(COLUMN(x))%2Cx%26gt%3BOFFSET(x%2C0%2C1))%2C1%2C0))))%3CBR%20%2F%3ELoss%20column%3A%20%3DSUM(MAP(K3%3AAT3%2CLAMBDA(x%2CIF(AND(ISEVEN(COLUMN(x))%2Cx%26gt%3BOFFSET(x%2C0%2C-1))%2C1%2C0))))%3CBR%20%2F%3EDraw%20column%3A%20%3DSUM(MAP(K3%3AAT3%2CLAMBDA(x%2CIF(AND(ISODD(COLUMN(x))%2Cx%3DOFFSET(x%2C0%2C1)%2Cx%26lt%3B%26gt%3B%22%22)%2C1%2C0))))%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3298767%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Formulas%20For%20Stats%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3298767%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1371441%22%20target%3D%22_blank%22%3E%40Solon_Jagers%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOh%20yes%2C%20the%20last%20part%20of%20your%20original%20question%3A%26nbsp%3B%3C%2FP%3E%3CP%20style%3D%22%20padding-left%20%3A%2030px%3B%20%22%3E%3CEM%3EAlso%20we%20have%20sportsmanship%20rating%20and%20each%20week%20a%20team%20submits%20a%20score%20out%20of%2010%20ie.(7%2F10)...%20This%20I%20have%20recorded%20in%20E3.%20Is%20there%20a%20way%20to%20have%20it%20automatically%20add%20the%20new%20number%20to%20the%20existing%20one%20entered%3F%20So...%20if%20the%20cell%20currently%20reads%2020%2C%20i%20just%20select%20it%20and%20type%207%2C%20and%20it%20autosums%20to%2027.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI've%20always%20found%20it%20helps%20me%20keep%20my%20brain%20functioning%20when%20I%20do%20those%26nbsp%3B%3C%2FSPAN%3Eautosums%20(especially%20when%20we're%20talking%20of%20max%20two-digit%20numbers)%20in%20the%20aforementioned%20brain.%20And%20I%20suspect%20it's%20a%20lot%20faster%20than%20any%20VBA%20routine%20or%20macro%20that%20otherwise%20would%20have%20to%20be%20written.%20An%20alternative%2C%20if%20you%20think%20it%20necessary%20to%20keep%20track%20of%20the%20individual%20weekly%20submissions%2C%20would%20be%20to%20actually%20keep%20in%20the%20cell%20a%20running%20total.%20So%20although%20the%20cell%20might%20be%20showing%2C%20say%2C%2030%2C%20if%20you%20put%20your%20cursor%20on%20the%20cell%2C%20you%20could%20read%20%3D7%2B8%2B6%2B9...%20%26nbsp%3B%20Personally%2C%20I'd%20go%20with%20doing%20the%20math%20in%20my%20head%20and%20just%20entering%20the%20new%20total%2C%20overwriting%20whatever%20was%20there.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20fun%20coaching!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3298759%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Formulas%20For%20Stats%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3298759%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1371441%22%20target%3D%22_blank%22%3E%40Solon_Jagers%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%20style%3D%22%20padding-left%20%3A%2030px%3B%20%22%3E%3CEM%3EI%20think%20I%20did%20this%20correctly%3F%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EWell%2C%20yes...but.%20You%20should%20take%20it%20down%20and%20post%20only%20a%20copy%20of%20the%20first%20sheet%20if%20you%20want%20to%20continue%20getting%20help.%20There's%20a%20sheet%20in%20there%20that%20contains%26nbsp%3Breal%20names%20and%20phone%20numbers%3B%20that%20should%20not%20have%20been%20posted.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThat%26nbsp%3Bsaid%2C%20I'm%20attaching%20a%20spreadsheet%20%5Bthe%20one%20you%20shared%20is%20read-only%2C%20so%20I%20had%20to%20copy%20to%20my%20own%20computer%5D%20%26nbsp%3Bthat%20shows%20one%20way%20to%20accomplish%20what%20you're%20trying%20to%20do.%20It's%20probably%20not%20the%20most%20elegant%20solution%2C%20but%20it'll%20work%20(after%20you%20follow%20the%20pattern%20out%20for%20the%20remaining%20weeks).%20What%20you'll%20see%20is%20that%20I%20added%20a%20column%20for%20each%20week%20that%20simply%20(and%26nbsp%3B%3C%2FSPAN%3Eautomatically)%20notes%20whether%20it%20was.%20a%20win%2C%20loss%2C%20or%20draw.%20And%20then%20the%20formulas%20back%20at%20the%20far%20left%2C%20where%20you%20summarize%2C%20call%20on%20those%20%22helper%20columns%22%20rather%20than%20trying%20to%20determine%20win%20or%20loss%20AND%20add%20all%20in%20one%20fell%20swoop.%20As%20I%20said%2C%20probably%20not%20the%20most%20elegant%2C%20but%20then%2C%20I'd%20have%20approached%20the%20whole%20thing%20in%20a%20different%20way%20(which%20still%20may%20not%20have%20been%20the%20most%20elegant).%3C%2FP%3E%3CP%3EI%20also%20added%20some%20conditional%20formatting%20to%20highlight%20teams%20with%20winning%20or%20losing%20season%20records.%20So%20the%20final%20summary%20columns%20look%20like%20this.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_0-1651238354759.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F367981iC3DEA050396917AC%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22mathetes_0-1651238354759.png%22%20alt%3D%22mathetes_0-1651238354759.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3298422%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Formulas%20For%20Stats%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3298422%22%20slang%3D%22en-US%22%3E%3CP%3E%3CBR%20%2F%3EI%20think%20I%20did%20this%20correctly%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3294041%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Formulas%20For%20Stats%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3294041%22%20slang%3D%22en-US%22%3EYour%20verbal%20description%20is%20somewhat%20helpful%3B%20you've%20even%20done%20a%20pretty%20good%20job.%20You've%20heard%2C%20I'm%20sure%2C%20that%20a%20picture%20is%20worth%201%2C000%20words.%20Could%20you%20take%20that%20as%20a%20truism%20and%20attach%20an%20image%20of%20what%20your%20words%20describe.%20Better%20yet%2C%20a%20copy%20of%20the%20spreadsheet%20itself%20could%20be%20posted%20to%20GoogleSheets%20or%20OneDrive%20(or%20the%20like)%2C%20making%20sure%20to%20grant%20access%20to%20others......%3C%2FLINGO-BODY%3E
New Contributor

I'm creating a stat tracker for my baseball league. I have set up multiple formulas already but there are a few I would love to know if are possible. 

For example...

if B3 is the Wins column, and C4 is Losses, is there a way for them to recognize which number is greater from the score submission columns (K3, L3 week one scores) then (M3, N3 week two scores) etc... and automatically increase by +1 in the wins or loss columns?

Also we have sportsmanship rating and each week a team submits a score out of 10 ie.(7/10)... This I have recorded in E3. Is there a way to have it automatically add the new number to the existing one entered? So... if the cell currently reads 20, i just select it and type 7, and it autosums to 27.

Everything else I have figured out already but any help would be appreciated. 

7 Replies
Your verbal description is somewhat helpful; you've even done a pretty good job. You've heard, I'm sure, that a picture is worth 1,000 words. Could you take that as a truism and attach an image of what your words describe. Better yet, a copy of the spreadsheet itself could be posted to GoogleSheets or OneDrive (or the like), making sure to grant access to others......


I think I did this correctly?

best response confirmed by Hans Vogelaar (MVP)
Solution

@Solon_Jagers 

I think I did this correctly?

 

Well, yes...but. You should take it down and post only a copy of the first sheet if you want to continue getting help. There's a sheet in there that contains real names and phone numbers; that should not have been posted.

 

That said, I'm attaching a spreadsheet [the one you shared is read-only, so I had to copy to my own computer]  that shows one way to accomplish what you're trying to do. It's probably not the most elegant solution, but it'll work (after you follow the pattern out for the remaining weeks). What you'll see is that I added a column for each week that simply (and automatically) notes whether it was. a win, loss, or draw. And then the formulas back at the far left, where you summarize, call on those "helper columns" rather than trying to determine win or loss AND add all in one fell swoop. As I said, probably not the most elegant, but then, I'd have approached the whole thing in a different way (which still may not have been the most elegant).

I also added some conditional formatting to highlight teams with winning or losing season records. So the final summary columns look like this.

mathetes_0-1651238354759.png

 

@Solon_Jagers 

 

Oh yes, the last part of your original question: 

Also we have sportsmanship rating and each week a team submits a score out of 10 ie.(7/10)... This I have recorded in E3. Is there a way to have it automatically add the new number to the existing one entered? So... if the cell currently reads 20, i just select it and type 7, and it autosums to 27.

 

I've always found it helps me keep my brain functioning when I do those autosums (especially when we're talking of max two-digit numbers) in the aforementioned brain. And I suspect it's a lot faster than any VBA routine or macro that otherwise would have to be written. An alternative, if you think it necessary to keep track of the individual weekly submissions, would be to actually keep in the cell a running total. So although the cell might be showing, say, 30, if you put your cursor on the cell, you could read =7+8+6+9...   Personally, I'd go with doing the math in my head and just entering the new total, overwriting whatever was there.

 

Have fun coaching!!

If you have access to 365 and LAMBDA...

Win column: =SUM(MAP(K3:AT3,LAMBDA(x,IF(AND(ISODD(COLUMN(x)),x>OFFSET(x,0,1)),1,0))))
Loss column: =SUM(MAP(K3:AT3,LAMBDA(x,IF(AND(ISEVEN(COLUMN(x)),x>OFFSET(x,0,-1)),1,0))))
Draw column: =SUM(MAP(K3:AT3,LAMBDA(x,IF(AND(ISODD(COLUMN(x)),x=OFFSET(x,0,1),x<>""),1,0))))
Thank you very much for your help, formula, and time. I do appreciate as it will do perfectly.
Thanks again
You are very welcome