•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Generating a formula in a cell re-iteratively?
Our take
Generating a formula in Excel that iteratively processes data can be complex, especially for those accustomed to traditional methods. However, with the introduction of functions like LET and SEQUENCE, you can streamline your formulas into a more concise format. By leveraging these new functions, it's possible to minimize repetitive patterns and enhance clarity. This approach not only simplifies your formula but also empowers you to manage data more efficiently.
Is there anyway using the newish functions (sorry I am an old version excel guy)
to trim this down into one line using LET/SEQUENCE/ etc
=VSTACK(
IF(BA2-(BA2-1)>BA2,HSTACK("","","","","","","",""),VSTACK(HSTACK("","","","",INDEX(AZ:AZ,BA2-(BA2-1)),INDEX(AY:AY,BA2-(BA2-1)),"",""),FILTER(AP:AW,(AW:AW=INDEX(AY:AY,BA2-(BA2-1)))*(AW:AW<>"")))),
IF(BA2-(BA2-2)>BA2,HSTACK("","","","","","","",""),VSTACK(HSTACK("","","","",INDEX(AZ:AZ,BA2-(BA2-2)),INDEX(AY:AY,BA2-(BA2-2)),"",""),FILTER(AP:AW,(AW:AW=INDEX(AY:AY,BA2-(BA2-2)))*(AW:AW<>""))))
)
it actually repeats 20 times I have just trimmed it to 2 iterations here
I appreciate any insight
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Lookup in table, return a formulaIs there a way to lookup a name/value of a cell in a table and the resulting answer is a formula? I am wanting to build a calculation, which could use 1 or more variables, depending on the value of a dropdown menu (Im assuming a drop down, since that would force the exact name/format of the formula callout). https://preview.redd.it/bhib489h9fpg1.png?width=1115&format=png&auto=webp&s=c79605dbb3388ec2e3471e60aecb4dd1f304bcfd Above is a quick and dirty example. If in Cell8, there is a drop down that listed the values in column J (1 Face Area, Length Face Area, etc.). In Cells D-F8, a user can input the thickness, length & width. Cell H8 would lookup the value from C8, reference the table from J5:K11, and after finding the formula, it will do the appropriate calculation. What I listed as a formula is just text for the example. I am open to adjusting as needed. This is just an example. I know I could do a bunch of imbedded IF statements in this example, but assume the table from J5:K11, could be many many lines. The table could be as many columns and rows as needed. Further, the example is just a single line (row 8), the intent would be to have many (10's, 100's or even 1,000's) rows of data, so calculating the answers and looking those up the table wouldn't work, like a single line entry. Is this even possible? I would prefer to accomplish this without VBA, if possible. EDIT: Here is another version of the example. As you can see I am planning on having multiple lines of entries. Some will have the same dropdown formula requested (i.e. Length Face Area), while others may never be requested. Hopefully that clarifies a bit. Also, while I would prefer to do without VBA, if that is the "easiest" solution, I am not opposed to entertaining. https://preview.redd.it/kqil9xiljfpg1.png?width=759&format=png&auto=webp&s=71f6d68f69bd0f6a5550d256b344032d7bc9ccbb submitted by /u/ernstm63 [link] [comments]
- Cell merging / formatting formulasThis might be an odd one. I'm not that skilled with excel as my use of in within my job is pretty limited. However, I tend to use this template my predecessor made to summarize data from our program. Works well, just a simple ='SHEET 1'!A1 for all cells. The first two images give an example. After the data is ported, I have to get rid of the zeros between the data and write system names. When it comes to pasting it on letters, the names are bolded, upped a font size, and two of the cells are merged (3rd image) This gets a bit tedious as the lists can get pretty long so I've been trying to figure out how to streamline it on my own. My idea has so far has been to have a separate cell detect when I'm finished adding my data and then format the aforementioned cells (4th image). For the life of me, just can't figure out how to write a formula to do it. What I would need is for the formula to detect a 1 (could be anything) in cell G10. It would then check for any blank cells in columns A and B. Once found, it would merge & center, bold the text, increase the font size, and align right. Is this only possible with a macro? I've been unable to find any formulas that could accomplish this. https://preview.redd.it/853mpr4jspog1.png?width=788&format=png&auto=webp&s=943a34a57f1d7c7f88256dd89b81b9c6fc301e34 https://preview.redd.it/e3w6ms4jspog1.png?width=453&format=png&auto=webp&s=9b189fa77d6638b627f8676dad60bc148753617c https://preview.redd.it/msh6ys4jspog1.png?width=411&format=png&auto=webp&s=d5f5025b315f24fd5f3a64c6e07e019abd3a77ab https://preview.redd.it/7j66tt4jspog1.png?width=936&format=png&auto=webp&s=1faa4df12ee74892f5fa9dc27ac95621280cf3c5 submitted by /u/Extension_Train9093 [link] [comments]
- Populate Cells From a Random Range of Cells, Based on the Contents of Different Cells.Hello Reddit pros, I am trying to create/find a formula that will do the following: Check if A2 matches values in D1:D6 and return a random value from E1:E6 into B2. The hard part is this; if A2 equals D1:D3 then the random value of B2 is from E1:E3, but if A2 equals D4:D6 then the random value of B2 is from E4:E6. For example: if A2 says Input 5, then B2 could be either Result D, Result E, or Result F. The values in the screenshot are just for demonstration; I will be able to extrapolate what I learn onto the spreadsheet I need it for. I am just curious if there is a formula to do that, or am I just having a lot of high hopes? I have tried using IF combined with INDEX and RANDBETWEEN but I cannot seem to get the formula correct for doing even the first part of what I need, let alone the second part. It looks like this: =IF(A2:A19,INDEX(D1:D3,RANDBETWEEN(1,COUNTA(E1:E3))),"") This obviously is not correct, and it returns a #VALUE error that I cannot figure out. I do not know the correct way to phrase the question to get a viable answer via internet searching, so I am once again turning to the experts on Reddit. Thanks for any insight. I am aware the formula on the screenshot is different than my post body; I deleted the top row but didn't fix the formula u/Connect_Camel_5998 solved it for me. Thanks everybody! The formula that was posted works great for what I needed! submitted by /u/ClandestineGhost [link] [comments]
- Building a dynamic formulaI'm using Excel to predict the capacity usage of storage devices. It's clunky, but it works great until someone changes the name of the SharePoint folder I'm using to reference my data. (This has happened twice) I had the brilliant idea (or so I thought) to dynamically build the formula using "concat", which I thought would allow me to simply change one cell and be able to affect all of my entries (about 65 rows). I then learned that "concat" only builds the formula, it does not evaluate it and that I needed to use "indirect" to evaluate it. That worked, except my output was "REF", not what I was expecting. After a little more digging, I found that "indirect" does not work with external workbooks that are not open on the local machine. (I don't want to open 60 workbooks to get the data I need) It looks like I am out of luck, but I am hoping someone else has a brilliant idea I have not considered yet. Thanks submitted by /u/Separate-Tomorrow564 [link] [comments]
Tagged with
#no-code spreadsheet solutions#Excel compatibility#Excel alternatives for data analysis#Excel alternatives#rows.com#AI formula generation techniques#formula generator#Excel#formula#VSTACK#HSTACK#INDEX#FILTER#BA2#LET#SEQUENCE#iteration#functions#AP:AW#AY:AY