Tag: formulas

Is Excel DATEDIF Gone from Excel? Quick How-To!

Posted on by Annie D

C. R. asks: Is DATEDIF gone from Excel? It is missing from the latest beta builds. Luckily, it is still there, but you have to know the arguments because Excel won’t provide any guidance.

Let’s see how DATEDIF works, even if the function doesn’t appear to be working in your version of Excel.  DATEDIF is useful for calculating the difference between two dates and can return the measure of time you specify (years, months, days, etc.).

Watch the short video below to see the steps!

This video answers all of these questions related to dated if:
is datedif still in excel
is datedif in excel 2016
why does datedif not work in excel
does datedif still work
can’t find datedif in excel
does datedif still work
datedif how to use
how excel datedif works
how to add datedif function in excel
how to get datedif in excel
how to enable datedif function in excel
what replaced datedif in excel
how to use datedif

For even more info: 

https://support.microsoft.com/en-us/office/datedif-function-25dba1a4-2812-480b-84dd-8b32a451b35c

Referencing an Excel Sheet With An Apostrophe in its Name – Solved!

Posted on by Bb

The Problem – Apostrophes in Sheet Names

You are trying to use a formula to reference an Excel Sheet with an apostrophe in its name and you keep receiving an error message or a #REF! reference error.

The Solution (Very Easy!)

Great news! Simply replace the apostrophe with two apostrophes in your formula. That’s right, two consecutive apostrophes, not a quote. Very simple solution indeed!

Below I provide two examples. You can manually update the name in your formula (or in the cell you are referencing if using an indirect lookup), or if working with many sheet names, you may opt to use the substitute solution shown below (formula displayed in E5).

The substitute can be nested in an indirect formula also.

Excel Sheet with an apostrophe solution
Excel Sheet With An Apostrophe Solution

Please comment below on any other challenges you would like for me to cover. I am contemplating doing a quick solution series in this format!

Also, be sure to check out Dose for Excel (click the image below)! Add over 100 functions to Excel to increase your productivity and more!  Disclosure: this is an affiliate link, so I may earn a small commission if you decide to purchase the add-in. Thanks for supporting my blog!

Dose for Excel - +100 Functions

Some topics addressed in this post:
Ref Error in lookup
#REF! Error in lookup
Ref Error in formula
#REF! Error in formula
How to fix ref error in Excel from apostrophe
Apostrophe in Excel sheet name issue



Average Excluding Min and Max Values -TRIMMEAN function in Excel formula

Posted on by Bb

Download Excel File: https://excelisfun.net/files/EMT1744.xlsx
Learn how to calculate the average while excluding the min and max values. See how to use TRIMMEAN function to exclude a given percentage of high and low data. Continue reading “Average Excluding Min and Max Values -TRIMMEAN function in Excel formula” →

multiply an entire column

Multiply an Entire Column by a Number in Excel (without using a formula)

Posted on by Annie D

Are you looking to multiply an entire column or range by a number?

You could always use another column and have the formula to do this, but this way is quick and easy if you do not plan on changing the values.

  1. All you need to do is have the number that you want to multiply in a cell (any cell).
  2. Now copy this cell and paste as special in the cells/range/column where you want to multiply it.
  3. In the Paste Special dialog box, choose the multiply option (in the Operation section) and click OK.  Your column/cells/range should be multiplied!
  4. If desired, can delete the cell that has the value which you multiplied by.

The resulting data is static/fixed so there’s no need to change any formula to value after multiplying using the paste special function.

Multiply An Entire Column

As always, I hope this helped you! Simple solutions are the best!

If I helped you, please consider buying me a coffee via PayPal! Thanks!!

Subscribe for more great content!