Nov 27 2023 03:23 AM - edited Nov 27 2023 03:55 AM
Hello,
I just saw the math functions (add-ins) from the site http://xl-viking.com/audit-excel-formula-examples/ where you can show equations with symbols and values, beside just showing the calculated value. This seems like a very good way to ease the review process of a design sheet containing many variables and equations. A function that I've been looking after for a long time.
However, at my company, we are not allowed to download add-ins that are not included in the usual Excel portfolio. So my question is if there is an existing add-in in Excel similar to the one provided by xl-viking that I've missed?
Regards,
Albin
Nov 27 2023 05:19 AM
Nov 27 2023 07:00 AM
Nov 27 2023 08:11 AM
Nov 27 2023 06:52 PM
Dec 01 2023 01:11 AM
@peiyezhu, that is a good idea thanks!
I guess I won't be able to show the equation with inserted numbers, but at least I can show formula with symbols (names) shown.
Usually I work with design sheets in columns, where I compare different configurations to find the best one. I give a simple example below, but usually the equation systems are rather long.
If I apply name here, I assume that I will need to distinguish the names in each column, i.e. for each configuration. Normally when I set up the equation system in one column, I just drag the column to the right and change the input parameters and its done. With names I assume that I need to do some more work, designing L1,L2,L3 , W1,W2,W3 and H1,H2,H3?
I assume that I can use "Create from Selection" and "Create Names from values in the: Left Column" to minimize the work. But when I try to do this with C2-C5, I get the following error message:
Regardless if I select C2:C5 or only C2.
Using this command I thought that I could automatically Name C2:C5 "L1", "W1", "H1" and "V1", instead of manually defining the name for each cell.
Do you see anything wrong with this approach?
Appreciate your input.
Dec 01 2023 05:01 PM
Dec 02 2023 04:17 PM
You can name the input cells, but:
But if you are willing to store your data entities one-per-row (rather than one-per-column, as in your sample data), you can put such data into an Excel table. You can then write formulas (one for each table column) that reference the column names, and there are other advantages. Formulas that reference Excel table data use structured references (like [@Height]) rather than cell references (like D4).
Of course, in either case, you can do simple manipulation of the result of the FORMULATEXT function yourself. See the attached workbook. (My examples are intentionally verbose. One or more could be shortened and/or defined as a named formula(s).)