FormulaText function in Excel

Hello all. In this short section, let’s cover the formulatext function in excel. Formulatext field would return the formula used in the reference cell as a text value.

Syntax

=Formulatext(ref)

Here, ref is any cell reference.

Working Example

In the below example, I have used an offset function to return the ops metrics for Feb month. The formula was used in I4.

If we need to return the formula used as text value, we use the =formulatext(I4) formula.

You may ask a better work example to understand why we use this. let’s look at a more comprehensive example. In the below example, we have a set of values in H2:H18. We don’t know what these values are as they don’t have any description.

Let’s use the formula text to understand what formula is used which will help us understand what these values are

With the formula used in the cell, we can determine what the values are. For example, H6 uses xlookup function to return the month name which has the maximum ticket count. We wouldn’t have known this without seeing the formula when the description of the value was not available.

Did you notice, we have “#NA” error though we had values? Please note the formula used in H7 – the if function will check all the values in B2:B13 to check if they are above or below the yearly average and return the appropriate text result. The function would return 11 values as we are checking a range – only H7 has the formula but values in H8:H18 are the output. When there is no formula but just an output, the formulatext function would just return “#NA” error.

You can trap the “#NA” error with the use of IFERROR function and have a custom message. Like below:

I hope you like this short tutorial. Please help us with your valuable feedback. Thanks for reading!

Leave a Reply

%d bloggers like this: