Excel Functions You Never Need!
Why would I tell you about Excel Functions you will never need? Well, you might be using them now, and not be aware that there are simpler, more elegant alternatives.
Are you using any of these: CONCATENATE, CONCAT, PRODUCT, POWER, DAYS, or DAYS360? Then read on…
CONCATENATE and CONCAT to Join Strings
Syntax: CONCATENATE(text1, [text2], …) and CONCAT(text1, [text2],…)
CONCAT is a new function in Excel 2019, presumably to save typing. Microsoft has kept CONCATENATE for backward compatibility. But why bother when you can type a single character instead?
Why do you never need it?
You can concatenate (join together) strings in a formula using the ampersand, “&”. Thus:
= Text1 & Text2 & Text3…
For example, using literal stings (in double-quotes) and cell addresses:
=”Our address is: “ & A1 & “, “ & B1 & “ “ & C1
PS: Excel 2019 and later also have TEXTJOIN(delimiter, ignore_empty, text1, [text2], …). This function may have some merit. For you can specify delimiters and tell the function to ignore empty cells. How is that useful? You could build up a comma-delimited address from several cells, but avoid having adjacent commas from blank cells.
PRODUCT to Multiply Numbers
Why do you never need it?
Use the “*” (asterisk) for multiplication instead. Instead of =PRODUCT(A1, D1) it is easier to type =A1 * D1.
In some circumstances, the PRODUCT function might be useful to multiply many cells together, because you can use a range. For example, the formula =PRODUCT(A2:A4, D2:D4) is the same as =A2 * A3 * A4 * D2 * D3 * D4.
POWER to Raise a Number to a Power
Syntax: POWER(number, power) where Number is the base and Power is the exponent to which the base is raised.
Why do you never need it?
Use the “^” (caret or circumflex) for exponentiation instead. Instead of =POWER(2,3), it is easier to type =2 ^ 3.
Why does the function exist at all? I have no idea. Probably belt-and-braces from Microsoft. Or some programmer there had a quota of functions to fill, and couldn’t think of anything useful.
DAYS for the Number of Days Between Two Dates
Syntax: DAYS(end_date, start_date). Start_date and End_date are the dates between which to calculate the number of days.
Why do you never need it?
Microsoft Excel stores dates as integers, starting at 1 on 1 January 1900 and adding 1 for each day. Thus, you can deduct one date from another to get the number of days between the two dates.
In other words, =DAYS(Day_2, Day_1) is exactly the same as =Day_2 – Day_1 and the latter is less typing.
However…
DAYS always returns an integer. If your dates include decimals (fractions of a day indicate a time of day, thus 0.5 would be noon) then =DAYS(Day_2, Day_1) and =Day_2 – Day_1 will give different results. =DAYS(Day_2, Day_1) is in fact equivalent to =ROUNDUP(Day_2 – Day_1, 0), as shown here:
DAYS360 for the Number of Days Between Two Dates Using a 360-day Year
Syntax: DAYS360(start_date,end_date,[method]). Start_date and End_date are the dates between which to calculate the number of days.
Why do you never need it?
According to the link, some accounting calculations use a 360-day year (twelve 30-day months). What about the extra 5 days? I hope that we engineers are above such fantasy.
Of course, there are many more Excel functions that engineers are unlikely to use. The above are ones that nobody needs, IMNSHO!