Braille Translator Challenge
Using the Braille Alphabet lookup table provided: Part 1 - convert the given phrase from Braille to words. Part 2 - convert the given phrase from words to Braille. ✅ Solutions should be in the form of a single-cell formula. ✅ Generalized Lambda functions are welcome, provided the function definitions are included with your solution. ✅ Keep in mind, the "best" solution is not necessarily the shortest... Strive for conciseness, if you wish; just don't let your solutions be ordinary. 😉 Good luck and have fun!
Webservice function
Hello. I want to use the WEBSERVICE function to geolocate an IP address. The address is in cell A1, and the function is in A2. The function looks like this: =WEBSERVICE("https://ip-api.com/csv/" & A1). Unfortunately, the result is #ARG!. I have no idea what is wrong. Can you help me? Best regards, Piotr
Linked data not updating
Hi I have a rostering spreadsheet where each person has their own spreadsheet with their rota they select a shift in their spreadhseet. I have a main spreadhseet where I link each spreadhseet to my my main rota so I can see what shifts they have selecteeneed and be able to resolve any conflicts i.e 2 peole selecting sam eshift. The issue is when data is updated in the individual spreadhseet it is not pulling through to my main spreadsheet. I used Data and get data form Excel file to link the workbooks. Not sure what I am doing wrong When Dr Adams adds a Y in Thursday it is not showing in my spreadhseetFormulas Challenge for 365
Thank you for reading this post! I propose a formulas challenge (The shorter the better). Attached is a workbook in need of two formulas. I have solutions for each (Formula 1 is 66 characters, Formula 2 is 72) but would like to compare notes with others and maybe learn some new things. Formula 1: Rollup data from 5 sheets. Must be sorted A-Z, no blank rows, and must include header. Formula 2: Find the j+ max for each agent. This sounds simple but you'll have to see the workbook. The arrangement is based off a client's workbook that crossed my desk several years ago. The rules are simple. For this challenge the following are restricted: vba, PowerQuery, fill handle, use of named items, and alteration of existing data. Functions available on the Beta channel are fair game. Note: You may find this exercise difficult if you're not on 365 with Insider. The goal of this exercise is to explore some of the newest functions Excel has to offer. Have at it!
Referenced cells do not identify themselves with color
For some reason when I create a formula that references other cells, I cannot get those cells to "identify" themselves with a specific color. For example, in the image below the formula references cells B7, B8, B6, etc but they are just black text and don't show up in color per standard excel documents. Usually they "glow" with a specific color so you can find them easily in big spreadsheets. How do I get the colors back?BigSpill: A 92‑Function Excel LAMBDA Library for Dynamic Arrays
What is BigSpill? BigSpill is an Excel LAMBDA library containing 92 functions across 10 categories, built from extensive experimentation with dynamic arrays. The goal is to provide elegant, efficient tools that make sheet‑level formulas easier to read, more modular, and more dynamic. The library includes a mix of: quality‑of‑life helpers essential primitives mid‑level operators developer‑level tools a few functions you might not expect to see in Excel Who is it for? Everyone. BigSpill aims to make complex operations more approachable and expressive. A few examples from the library: Pairwiseλ Staircaseλ Grainλ PolarGridλ Convolveλ Foldλ Tessellateλ Revealλ Knapsackλ Magnifyλ Traverseλ The repository includes full documentation and 11 sample workbooks so you can explore the functions without setup. If you’re interested in experimenting with dynamic arrays, or LAMBDA workflows, feel free to take a look. Feedback and suggestions are always welcome. I've attached 1 onboarding workbook. There are 10 more at the link below. GitHub: BigSpillDoes a LET variable get computed even if it's only referenced inside IFNA's fallback argument?
In the formula below, expensiveMatch is defined in LET but only referenced as IFNA's fallback argument: =LET( matchRow, XMATCH(1, (A1=Sheet2!$A$1:$A$1000)*(B1=Sheet2!$B$1:$B$1000)), x, INDEX(Sheet2!$C$1:$C$1000, matchRow), expensiveMatch, INDEX(Sheet3!$C$1:$C$500, XMATCH(A1&B1, Sheet3!$A$1:$A$500&Sheet3!$B$1:$B$500)), IFNA(x, expensiveMatch) ) Question: When x resolves successfully (no #N/A), does expensiveMatch still get computed because it's a top-level LET variable - or does Excel skip it since IFNA's fallback argument is never reached? I know IFNA short-circuits its fallback argument, and LET avoids recomputing a variable if referenced multiple times - but I haven't found much documentation on the behavior of Let. Is the below a more efficient way to write the formula? =LET( matchRow, XMATCH(1, (A1=Sheet2!$A$1:$A$1000)*(B1=Sheet2!$B$1:$B$1000)), x, INDEX(Sheet2!$C$1:$C$1000, matchRow), IF(ISNA(x), INDEX(Sheet3!$C$1:$C$500, XMATCH(A1&B1, Sheet3!$A$1:$A$500&Sheet3!$B$1:$B$500)), x ) ) Is this restructuring necessary, or does the first version already skip the unused computation? Basically I'm wondering if Let computes lazily/defers calculation in the way SQL's optimizer works. Input from anyone with insight or knowledge into the calc engine would be greatly appreciated. Thanks (Currently on Microsoft Excel for Microsoft 365 MSO (Version 2607 Build 16.0.20228.20190) 64-bit)
Advanced Filter
Hello. I created a filter that has 2 conditions. 1. If the Ref Cert or the SK Cert is YES, bit should appear. The problem is that if the columns with Ref Exp or SK Exp is blank, then it gives me a 0 or if it does have a date, then it gives me the number equivalent. The original table, it does have dates. I tried to format the columns so it would give me an actual date, but it doesn't work. It only works for the blank cells. This is the actual formula. =FILTER(C3:N500,((H3:H500)="YES")+((J3:J500)="YES"))&IF(ISBLANK(I3),"",I3) Array is C3 : N500 (This is the table where is information is coming from) Ref Cert is H3 : H500 SK Cert is J3 : J500 This is what the finish product looks like.
