for an ad-free Fetcheveryone experience!

SpreadEveryone: The Fetchland Excel wire

102 watchers

SPR Nov 2024 3:09pm, 11 Nov 2024 46,701 posts	Quote Pin SPR =VALUE(REPLACE(A1,FIND(" ",A1,1)-2,2,)) That gives a number that you can then format and gives the correct date.
larkim Nov 2024 3:13pm, 11 Nov 2024 26,074 posts	Quote Pin larkim Thanks SPR - I'd got something like that just to extract the date integer, wrapping it all together is even better.
GordonG Nov 2024 11:37am, 18 Nov 2024 10,709 posts	Quote Pin GordonG Hi all, would appreciate some advice about something I’m trying to do on Excel. There are 8 columns, A-H (representing January to August of any given year). Each row represents a runner in our club. We have over 100 members, which is why I want to find a way of automating what I want to do. Each column is used to record when a club runner races in a specific club race. There is one race per month, so if Runner A runs in January this will be recorded in cell A2 (and unless it makes a significant difference to the function, I’m not bothered whether it’s the actual date that goes in A2, or just an X or something to indicate that Runner A did actually run. Their time is irrelevant to this request.) Runner A misses the February race, so cell B2 can stay blank. Runner A runs in March (C2) but not April or any subsequent race. What I want to do is have a way of automatically indicating, in cell I2, how many races it’s been since Runner A ran. So in our example, Runner A hasn’t run in any of the past 5 races and I’d like cell I2 to display the number 5. Once they do run again, that number should automatically go back to zero once I’ve inputted this info into the latest column. I know I can count how many cells in the row of 8 are blank, but that’s not what I’m after. It’s specifically, “starting at the cell immediately to the left and going backwards, how many cells are blank before we get to a cell that isn’t blank?” Anyone got any suggestions on the most appropriate Function or other process to achieve this? If I’ve described an overly complicated way of achieving my aim, then I’m open to suggestions of easier ways to do this. If I was to really push my ask, I’d like cell I2 to simply display the letters TT if the number is 5 or above and to display nothing if it’s 4 or below, but that’s less of a concern than just simply having the calculations automated. Thanks in advance
larkim Nov 2024 12:00pm, 18 Nov 2024 26,135 posts	Quote Pin larkim A messy nested "if" would do the job quickly and dirtily! But I suspect something which uses column references would help using absolute references rather than relative ones as a neater solution.
Diogenes Nov 2024 12:07pm, 18 Nov 2024 89,388 posts	Quote Pin Diogenes There may be a more sophisticated way of doing this (there almost certainly is), but how about putting in a 1 in the cells where a runner runs? You could then have a formula that counts the last 5 cells and and if the number is less than 1 puts TT in I2?
Diogenes Nov 2024 12:08pm, 18 Nov 2024 89,389 posts	Quote Pin Diogenes (by number, I mean result...)
JamieKai chameleon Nov 2024 12:12pm, 18 Nov 2024 19,583 posts	Quote Pin JamieKai chameleon Just thinking about this, one complication is that the ask changes depending on the date, ie in January - April you don't want the result to show anything, in May you're interested only in January - May, in June it's February - June... I'm not good at thinking up elegant solutions, think I'd have a bunch of hidden columns doing calculations, and then likely a messy if statement.
GordonG Nov 2024 12:12pm, 18 Nov 2024 10,710 posts	Quote Pin GordonG thanks both for your prompt suggestions. @Diogenes, hmm yes i hadn't thought about doing it that way, thank you.
GordonG Nov 2024 12:16pm, 18 Nov 2024 10,711 posts	Quote Pin GordonG yes that's true JK. Thinking about it, I'll expect I'll be deleting the columns older than 5 months. So actually it's really only, "in these five columns/months...", which I guess makes Dio's suggestion even more helpful.
Diogenes Nov 2024 12:34pm, 18 Nov 2024 89,390 posts	Quote Pin Diogenes In J2: =SUM(E2:I2) In K2: =IF(J2=0, "TT", " ") I couldn't find a nice way to make this happen all in the one cell