Hi guys,
New member, first post, go easy on me please!
I am after a formula that calculates a current streak whether it be win, loss or draw.
I would also like it to not take in to consideration the games that the result is currently "To Be Played" (once a game is played it changes to either "Won", "Lost" or "Draw").
Any help would be appreciated!
Hi there... and welcome to the Excel Forum.
You haven't told us very miuch, have you!! Anyhow - does this come anywhere close??
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Hi There,
Welcome to the Excel Forum.
Lets assume you have value in Column A and wanted to get the result in E2, F2 & G2.
win loss.png
Thanks,
Anil Dhawan
Click *Add Reputation to say "Thanks" and don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved) if you are satisfied.
Don't stop when you are tired. STOP when you are done!
Modification to take account (ignore) To be played
Hi, thank you but not quite what i'm looking for...
See the attached shhet, I basically want the cell B1 to say "1 Loss" as that is the current streak. If we win the next say 3 games, I want it to say "3 Wins".
I also want it to ignore Game Results that say "To Be Played" as they won't be part of the current streak.
Streaks.xlsx
Hi
G2This is array formula? Press same time Ctrl+Shift+Enter, Copy down.Formula:
Please Login or Register to view this content.
Need to remove "To Be Played" to blank to make it work?
Regard
Last edited by micope21; 04-10-2015 at 06:38 AM.
To help you by my post? it would be nice to click onto say "Thank you".
If you are happy with a solution to your problem?
Click Thread Tools above your first post,
select "Mark your thread as Solved".
Thanks but no still doesn't work...
After I remove the "To Be Played" if I add a "Win" it just counts the total wins and comes up as "4 Wins" but should be "1 Win" as that would be the current streak.
Last edited by buttsy00; 04-10-2015 at 07:56 AM.
Also what does the "zzz" do? Should I replace "zzz" with something?
For B1 cell
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself
Please Login or Register to view this content.
Hi Did you press Ctrl+Shift+enter same time? Excel will surround with curly braces {}.Originally Posted by buttsy00
See the file.
Regard
Get rid of the "To Be Played" in those cells. Just leave them empty.
This array formula** entered in B1:
=LOOKUP(1E100,FREQUENCY(IF(G2:G25=LOOKUP("zzz",G2:G25),ROW(G2:G25)),IF(G2:G25<>LOOKUP("zzz",G2:G25),IF(G2:G25<>"",ROW(G2:G25)))))&" "&LOOKUP("zzz",G2:G25)
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Hi Yes this worked (I must not have pressed Ctrl+Shift+Enter)
Thank you!
For B1 cell
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourselfPlease Login or Register to view this content.
Thank you this formula worked too!
I went with the other simply because it was shorter and a bit easier to understand.
How do I marked this thread as "Solved"?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks