41–50 of 55 posts
The functionality I want is a count-by-format function. I had to write my own in VBA and of course that means every time I open that sheet I have to approve its use of macros (and it also doesn’t always catch format changes that impact the calculation)
I wish they completed the IF variants of statistical functions. Why do we have AVERAGEIF , but not AVERAGEAIF or GEOMEANIF , MAX and MAXIFS , but not MAXIF , etc? I can’t find logic in that. ( https://support.microsoft.com/en-us/office/excel-functions-b... ) Ideally, the “IF” would be a separate function that filters out cells, so that it could be used with VBA functions taking multiple cells, too. While at it, these…
https://support.microsoft.com/en-us/office/if-function-69aed...
Excel makes me feel like such a novice. I often just write a python script instead out of frustration. I don't think little of people that write complex formulas and macros with it. I am still waiting for "python in excel".
20 years overdue... Another one that is massively overdue: take multiple arrays as arguments and return the distinct values, sorted (kind of like the remove duplicate button, but that doesn't require to click a button). [edit] actually it was introduced in 2021 ("UNIQUE" function) Also take multiple arrays and returns the values that are in common (like an inner join). Use case: you want to align two time series by c…
You just have to add a column and you are an XLOOKUP and a quick filtering away from the result you want. It’s a fairly common operation.
> Excel should also come with the most common holiday list (all the major cities at least).
It’s the same. You just have to add a table and do a lookup.
Excel makes me feel like such a novice. I often just write a python script instead out of frustration. I don't think little of people that write complex formulas and macros with it. I am still waiting for "python in excel".
If you want to lean into the novice feeling you could watch this: https://www.youtube.com/watch?v=x1RVNGDSdw4
[0] https://www.youtube.com/watch?v=ICp2-EUKQAI [1] https://www.youtube.com/watch?v=xubbVvKbUfY
Excel makes me feel like such a novice. I often just write a python script instead out of frustration. I don't think little of people that write complex formulas and macros with it. I am still waiting for "python in excel".
20 years overdue... Another one that is massively overdue: take multiple arrays as arguments and return the distinct values, sorted (kind of like the remove duplicate button, but that doesn't require to click a button). [edit] actually it was introduced in 2021 ("UNIQUE" function) Also take multiple arrays and returns the values that are in common (like an inner join). Use case: you want to align two time series by c…
> Also take multiple arrays and returns the values that are in common (like an inner join). Use case: you want to align two time series by creating a 3rd time series made of the dates common to both original time series. You just have to add a column and you are an XLOOKUP and a quick filtering away from the result you want. It’s a fairly common operation. > Excel should also come with the most common holiday list (a…
Yeah you can do that by introducing multiple columns or creating a VBA UDF. My point isn’t that it cannot be done (like TEXTSPLIT or XLOOKUP, there were more convoluted ways to do that already). It’s more that it is something common enough that there should be a simpke function for that.
On the holiday lists I mean the list of bank holidays by major city. I believe right now you need to provide that yourself, but it is something microsoft could build and maintain centrally instead of everyone reinventing the wheel. They do that for timezones/time change in the OS already.
Earlier quoted context omitted.
> Also take multiple arrays and returns the values that are in common (like an inner join). Use case: you want to align two time series by creating a 3rd time series made of the dates common to both original time series. You just have to add a column and you are an XLOOKUP and a quick filtering away from the result you want. It’s a fairly common operation. > Excel should also come with the most common holiday list (a…
> You just have to add a column and you are an XLOOKUP and a quick filtering away from the result you want. It’s a fairly common operation. Yeah you can do that by introducing multiple columns or creating a VBA UDF. My point isn’t that it cannot be done (like TEXTSPLIT or XLOOKUP, there were more convoluted ways to do that already). It’s more that it is something common enough that there should be a simpke function f…
The point I wanted to make is that sometimes you can just get results quickly by some manual manipulation like sorting in complement to formula.
Array functions feel like they're just too far from the excel design thinking. A single function that affects nearby cells is hard for me to swallow. I kind of wish they went for the matlab cell array style where a function can return an array, but it just becomes a data structured stored within a single cell. So TEXTSPLIT (which is great, finally), would return an object like ARRAY("I", "SAW","A","CAT") and if you w…
If you want to do lots of reshaping data and performing operations on tables of data, you are better using ETL tools (Extract Transform Load) that were designed for this task. For example: Easy Data Transform, Tableau Prep or Alteryx.