Power Query has a few advantages over VBA for creating user-defined functions for use in Excel formulas:

• Power Query produces shorter code that is faster and easier to write. This is largely thanks to Power Query native supporting tables, lists, and records; having many built-in functions to work with these data types; and supporting first-class functions and higher-order functions, which eliminates repetitive code and grants the programmer more expressive power than VBA.

• The Power Query GUI produces code that is closer to ideal than the VBA code that the Macro Recorder produces. Power Query also shows your recorded steps in a GUI and allows you to delete any step at any point.

• Power Query is focused on data manipulation and calculation. VBA includes the clutter of programmatic spreadsheet editing.

• Power Query can be used in any modern Excel spreadsheet format. VBA cannot be used in XLSX files.

• Power Query is being actively developed. The VBA in Excel is outdated and is largely abandoned.

Please allow us to use user-defined Power Query functions in Excel formulas.

If you type in for example =-6^2, Excel gives you 36. This is incorrect. Excel is assuming you mean =(-6)^2. But -6^2 is different.

You can see this when you factorise. -6^2 = (-6)*(+6) = -36.

Whereas (-6)^2 = (-6)*(-6)=36.

Now if you type in = 0-6^2, only then does it give you the correct answer of -36.

This error goes against millennia of algebraic convention.

Plot the graph y=-x^2-7x-12 and then plot y=-7x-x^2-12. These two expressions are identical, yet Excel gives very different traces (the latter of the two gives the correct graph).

Another way to correct -x^2 would be to type =-(x^2) but you shouldn't have to. You don't write it out that way if you're plotting quadratics.

Hi there guys,

Let's say I have a simple column with numerical values (check figure A attached).

If I look at the right bottom corner of the Excel window, I'll get a quick summary of the data (average, count, sum).

Oftentimes, I'll need to use this sum elsewhere (sometimes beyond Excel). Thus, I've always wondered why there is no shortcut to simply copy the sum to clipboard.

I understand that I can use something like Autosum to calculate it quickly but I feel this would be an even faster and more efficient way. And that's sort of the point of Excel, right? To make us more productive.

When working with large data (hundreds of columns, and thousands of rows) I would really prefer not to disrupt the data (or maybe I'm too lazy trying to find a free cell to use Autosum) but just get the sum copied to my clipboard. And to be honest, I've consistently met people wondering the same thing wherever I worked.

Thank you!

Best

It would be nice to have a function that can accept a string value that represents a date and/or time and an Excel date/time format expression to return the date/time value i.e.
todate(stringvalue, format expression)
totime(stringvalue, format expression)

For example, consider the following string values for dates stored in cell A1
20190913 : todate(A1, "yyyymmdd")
19-09-13 : todate(A1, "yy-mm-dd")
19-Sep-13 : to_date(A1, "yy-mmm-dd")

for time
091500 : totime(A1, "hhmmss")
9:15 : totime(A1, "h:mm")

for timestamp
20190913091500 : to_date(A1, "yyyymmddhhmmss")

The existing datevalue() fucntion only recognize "standard" date formats and can make mistake interpreting the string based on your regional settings (ex "yy-mm-dd" vs "mm-dd-yy").
In order to do this with the existing date() function, you need to parse the value manually. This works ok with fixed length formats (left, mid, right functions) but is more complex with variable length formats (such as month names, hours without leading 0,...)

Hi,
There is a problem in one excel formula. When you use a formula for a second order equation with a negative coefficient for the square term, excel applies the square to the cell value and the negative coefficient. The consequence is that the result is always positive and so result is wrong.
Ex.
=-A1^2, if A1=1,2,3 the result is 1,4,9 and not -1, -4, -9. Using parenthesis does not correct the bug =-(A1)^2 = 1, 4, 9. it is not the case if you do = 0-A1^2 (results, -1, -4,-9). Normally if you want include the negative coefficient in the square you have to do =(-A1)^2
So if you have decreasing second order equation, the result will be always wrong. Do you think it is possible to correct this bug in excel?
Bets regards

There needs to be a huge update regarding the Evaluate Formula.
I'm sure those using this feature are not trying to see simple calculations done.

For me, I might create a report that has a cell calculate multiple IFs within IFs that have embedded functions.

with this in mind, it would be a huge advantage to the user if the Evaluate Formula has the following upgrades:

1. able to expand the window.
   the window currently is too small and only allows to view simple calculations.




2. color code what is being calculated.
   with everything being the same color it is a bit difficult to see what changes are happening in the calculations if the calculation is complex. underlining, bolding and italicizing seem a bit archaic.




3. allow to reverse the steps.
   sometimes if clicking Evaluate many times, over-clicking it might occur, missing a change the user wants to see. it becomes tirelessly time consuming if the user has to redo the evaluation from the beginning.




4. behavior when dealing with arrays.
   this is in reference to the Evaluate Formula expanding an array. if the array is long, it is sometimes pointless to see all the values it has. There should be an option in the window to allow the arrays to expand or to suppress it.




5. eliminate spaces in the window
   if a calculation is long, sometime it is necessary to write it out using multiple lines ([alt + enter] and indenting). when going to the Evaluate Formula, these spaces are included when it serves no purposes for readability in the Evaluate window.




6. do not have the Evaluate Formula formatted as prose
   again, if the calculation is complex, there is an issue of readability when seeing the steps. having the evaluation as one long line of wrapped text can be hard to digest

most of these points have been requested already and they are all pretty much asking the same thing: make the Evaluate Formula more readable and account for any evaluations they may not be important to the calculation.