I have a column with "W" or "L". The data is sorted from top to bottom with the data on the bottom being the most recent. The column has blank cells that need to be ignored by the formula. I want the current streak of wins to be displayed. If the bottom most cell with data in it contain a "W" then there should be a current win streak displayed. However if the bottom most cell with data in it contains a "L" then there is no winning streak
Please try:Formula:
Please Login or Register to view this content.
Another solution that works in Excel 2021:Formula:Please Login or Register to view this content.
I made it too difficult for myself.
This formula also works in Excel 2021:See attachment with all the 3 formulas.Formula:Please Login or Register to view this content.
I tried all 3 formulas. I get the error "the formula contains unrecognizable text"
TEXTAFTER does not exist in Excel 2021. It's in 365 only.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Try this,
Please Login or Register to view this content.
A value used in the formula is of the wrong data type
See attachement. If this is wrong, point out the error.
TEXTAFTER does not exist in Excel 2021. It's in 365 only.
It is very strange. I can download the example spreadsheet. But if i edit the formula or try and copy paste the formula it just changes to #NAME? and shows error "The formula contains and unsupported function"
Last edited by hammerb; 12-14-2023 at 12:17 PM.
TEXTJOIN is included in Excel 2019.
Is what i'm trying to accomplish not possible for Excel to do?
I'm sure it will be - be patient, please.
Try this:
=LET(x,LOOKUP(2,1/(A:A<>""),A:A),w,LOOKUP(2,1/(A:A="W"),ROW(A:A)),l,LOOKUP(2,1/(A:A="L"),ROW(A:A)),IF(x="W",COUNTIF(INDIRECT("A"&l&":A"&w),"W"),0))
This is the most baffling thing. I am going to upload my actual spreadsheet to this. I am obviously missing something and/or not understanding something. The page I am working on is named "Games", The cell i am trying to add the formula to is M1349. The column with the data is the AB column
https://www.dropbox.com/scl/fi/88tr839o75iv50o9di0g3/OU-Record.xlsx?rlkey=n1k8dpphixwwh3n1or57u1ir2&dl=0
Please attach the workbook directly here.
I have no clue what is wrong. I cannot upload the file "upload of file failed"
Make it smaller - exclude anything that's not relevant.
Try this:
=LET(x,LOOKUP(2,1/(AB1:AB1341<>""),AB1:AB1341),w,LOOKUP(2,1/(AB1:AB1341="W"),ROW(AB1:AB1341)),l,LOOKUP(2,1/(AB1:AB1341="L"),ROW(AB1:AB1341)),IF(x="W",COUNTIF(INDIRECT("AB"&l&":AB"&w),"W"),0))
That worked perfectly. Thank you very much
Glad to have helped.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks