101–110 of 141 posts
Earlier quoted context omitted.
If you have multiple lines of formula, you should probably break up the formula across a few cells. This is similar to breaking up a long script into modules... each cell should have 1 purpose. Allows better testing of the formulas too.
That's just ugly hack, formulas are essentially code and should be treated liek that
Earlier quoted context omitted.
Yep, we have a expensive reporting tool, can too all kinds of things, query every type of database we have... Number one function used. Export to Excel, and normally it is on reports of basic table join queries, none of the advanced things it can do with the data
This is because businesspeople like to fiddle with formulas, graphs, and filters untill they isolate (i.e. cherry-pick) the subset of data that [makes them look good | supports their agendas]. They don't want canned reports. Excel makes this easy because they already know how to do those things in Excel.
Earlier quoted context omitted.
People get really mad when you call this p-hacking, btw. I used to try and make this point to ”analysts” that I worked with, that science involves coming up with a hypothesis and then looking at the data. The response I got often enough that I’m sure it’s being taught somewhere was that you “need to let the data tell it’s own story”. Turns out lots of people have decided what the conclusion is and are taking that con…
As my professor said: All good statistics is done before you have looked at the data.
Recently I started building Excel addons with custom formulas (e.g. =company.inventory(upc, WAREHOUSE), which call an api. This has blown the minds of non-tech folks among my clients. They equate this to magic. I was forced into doing this because after a year of digging to find out what reporting they wanted in the dashboard (“oh a thousand things… where’s the Excel spreadsheet export button?”), I gave up and now de…
What is your language of choice for building custom formula? Python + xlwings: most successful (relatively speaking) to build custom finance functions in my line of work. Downside is the slowness and deploying on other computers. Rust + xladd: really enjoyed this but feels immature still. Better performance than python and easier to distribute as single dll. VBA: options above make this almost obsolete, however can’t…
My current #1 wish for Excel formula entry: Please let me use [Tab] and [Enter] in the formula bar without trying to commit the change! I like to format my formulas with multiple lines and indentation, but to do that I have to hold down [Alt] and mash the spacebar like a caveman. I am happy with the new features I've been seeing. LAMBDA() and the new TEXT functions are nifty.
As noted, Google Sheets introduced this over a year ago [1]. But this is exactly what competition is about -- I love seeing this come to Excel precisely as an answer to Google's version. You have to wonder if Microsoft would have tried it otherwise, since Excel is so entrenched there's less profit motivation for innovating. Sometimes it feels like "office" software hasn't changed much since the 90's, but when you loo…
If you don't known have test input (e.g. an array of values) for which you have known output to observe for such an opaque programming language, you will be bitten! This innovation just makes it even more important.
That said Excel lets non-programmers create visual representations of data on a regular basis, and everyone has it on their desktop so there's no, "I can't use this" excuse.
Great. Can they fix the autocorrect so it doesn't mangle gene names? https://www.nature.com/articles/d41586-021-02211-4
Excel is a generic spreadsheet program that's designed to be used by 90% of people for 90% of use cases. Accommodating for this niche use case will definitely break things for many existing users. The real solution is for geneticists (and other data scientists) to use software designed more specifically for their use cases, such as CSV files and pandas or something more GUI-based but meant for researchers.
For anyone who writes Excel formulas, it is one of the use cases for ChatGPT (a currently free chatbot that appears highly intelligent in some contexts) that users rave the most about. The next time you're writing an excel formula, give chatgpt a try. It might just get it right the first time.
This almost certainly is GPT. OpenAI has an exclusive licensing agreement with Microsoft.