How - and when - does the accumulator of SCAN get reset?
This was inspired by this LinkedIn discussion; I figure that this is a better forum to discuss the issue I would like to raise. In a nutshell, the assignment discussed there was to fill the blanks in a "gappy" column by repeating each visible value enough times to populate the empty cells beneath each visible value until the next visible value is reached and then continue repeating using that next value. The answer (as modified by PeterBartholomew1) in that discussion was: =SCAN("", names, LAMBDA(accumulator, current, IF(current <> "", current, accumulator))) In my attached file, "names" refers to the list of names in A1:A10 (that is, including the otherwise blank A10). A couple of things I don't understand: First (and probably simpler) is the function of the initial value of the accumulator. Whether it's "" or "xxx" (which I used in the attached file) the output stays the same. So why is the accumulator necessary to begin with? Or does it imply that - for that particular purpose - SCAN is not the right approach? Second, whether the initial accumulator value is "" or "xxx", it somehow gets reset as we go down the column and its value becomes equal to the value of the most recent visible row cell; it doesn't "accumulate" - which to my (obviously over-simplistic) mind would imply some sort of concatenation of all previously visible row cells. To demonstrate, I changed the formula to just =SCAN("xxx",names,LAMBDA(a,c,"a is now "&a)) and the output became In this case, there is some form of "accumulation" - but even that doesn't take the form I would expect. As the title says: how and when does the accumulator gets reset? That's an issue I ran into in various other scenarios so I would like to understand it once and for all, if possible...
Dynamic Let Function
If you have a long let function with lots of variables, it is annoying to audit. It is also a shame that all the other info in the let function is "trapped" without manually going in to change the item ment to be returned. Here is a fun way to solve both problems but it has one thing missing that perhaps someone from the community will be able to figure out. If you create an item_array and an item output, you can use a relative reference to a cell to ouput any item in the let function. This lets you copy and past with reference to the changing variable to get the changing output. My question is how can I make the `item_output` simply replicate the `item_array" without having to retype each item for the choice function. I tried textsplit to generate the second argument of CHOOSE but CHOOSE treats the whole array as an item and the output is the variable name rather than the data. Seems like INDEX or CHOOSE or CHOOSECOLS or something should work but I had no luck making them work. Perhaps someone will have an idea?
Unknown error
I 'd copy screenshots ctrl + v to the cells wjile working on my file. It worked like a charm but then I've had uninstalled my excel do to the license expiration it was 2019 version and noticed this error so i thought to myself that need to get the latestet version. Just downloaded it and still the error is unknown please advise
Calculating the "spread" between multiple numbers in one cell.
Is there a way to calculate the difference between multiple numbers in one cell that are separated by a forward slash? This is for a spreadsheet that I'm using to track stock option trades. The cell in question can contain up to 4 values (i.e. option strike prices) with each value separated by a forward slash. For example: "235/240/310/315". I want to create a formula that will calculate the "spread" between the 1st two numbers and the last two numbers and return the larger of the two values. In this example I need the value of 5 returned. There will be other times that the cell may contain three values where I need the difference between the 1st & 2nd numbers and then the 2nd & 3rd numbers, again returning the larger of the two values. For example: "37/44/50" where the value of 7 is returned. Thank you in advance for any solutions/suggestions.Excel Formula
I created an excel workbook with multiple worksheets that all have formulas. I made sure everything worked, then uploaded to Google Sheets. Now some of the formulas are not working. Some are, but some aren't. I know this isn't a Google forum, but I'm hoping someone else has experienced this and can help?Conditional Formatting Issue
Hello all, I have a spreadsheet I use to keep track of paint costs associated with various part numbers we have. I manually enter each line throughout the year. I currently have conditional formatting set to highlight any duplicates within Column A. However, I would now like to set up some sort of conditional formatting formula where Column C is formatted if they are unique values if Column A are duplicates. I have a screenshot to better illustrate what I'm saying. What I'm looking to do is after I had manually entered C997, both C997 and C990 would highlight the pricing discrepancy between the A990 and A997 duplicates. I feel as though this may be a simple XLOOKUP formula and I'm just overthinking but could use some expertise on how to write up the formula. Thank you!Formula question!
I need some help to see if this is even possible as I will do my best to explain. I have an excel spreadsheet that I need everything over a specific number to transfers over to Column A. For example, anything over the number 4.5 in Column B would transfer to Column A. So if I type the number 5.5 in Column B and Column A had the number 10 in it than Column B would reduce to 4.5 and A would increase to 11.Searching for multiple words in an Excel spreadsheet (ugh, why is this so hard?)
I know that I can search for words in Excel using CTRL-F. However, I have an Excel spreadsheet that I regularly need to search for roughly 40 keywords, and I need to do that on a weekly basis as the contents of that spreadsheet itself changes weekly. I don't want to have to CTRL-F and do 40 different word searches every since time (especially since I can't remember all 40 keywords I need to search for off the top of my head). Is there a way to have Excel search an entire worksheet (or even just Column "B" of that worksheet), for the following keywords (as an example) Apple Pear Monkey Table Chair Television Microwave (I'd want it to report back finding "Microwave" even if it actually found "Microwave Oven") Mouse Waterfall etc. (for all 40 words) ... and then have it tell me WHICH of those keywords it found in that worksheet? I have to imagine there is some EASY way to do this with a FORMULA or a MACRO? (of which I have basically no knowledge of how to do either) . I'm guessing worksheet #1 could be my keywords, and worksheet #2 could be the actual spreadsheet that I'm searching, but I've no idea how to make this work. Any help here?Cascading Data Validation 3 level dropdown list with reoccurring text
Good day, I want to make a 3 level cascading dropdown list but the 3d level is not working. My idea is to make a budget tracker from 2 banks (Level 1) from which each bank has 3 accounts (Level 2) and each account has a different currency (Level 3). The problem arises when different accounts have the same currency. The system does not seem to be able to read it and returns a blank dropdown. Is there a way around it? attached is the link to the file: Excel questions Feel free to reach out if there are any questions. Thank you in advanced
