Can you apply a formula to a formula in Excel instead of the value?

Question

e.g. if cell A1 contains the function =SUM(B1:B6) is there a way I could use a formula like SUBSTITUTE(A1,"SUM","COUNT") to return the formula =COUNT(B1:B6)?

Alternatively, is there a formula that will return what has been entered - i.e. return =SUM(B1:B6) as a string instead of the value?

Kinda of hard to follow what you are attempting. Can you please explain what you are trying to accomplish overall? It doesn't make sense why you would want to change the formula that way when you can just use `=COUNT(B1:B6)` as the formula. — CharlieRB, Apr 20 '16 at 14:55
You might be able to accomplish this kind of thing with EVALUATE. Check out the function selection drop down example under "USING THIS IN EVERYDAY SITUATIONS" in this mini-tutorial: http://www.myonlinetraininghub.com/excel-factor-12-secret-evaluate-function. This was available at least through Excel 2010. Note that it requires named ranges. It might even accomplish the last step in Máté Juhász's answer. — fixer1234, Apr 21 '16 at 06:05
If you're able to restructure the requirement, you could use a function like [SUBTOTAL](https://support.office.com/en-us/article/SUBTOTAL-function-7b027003-f060-4ade-9040-e478765b9939?ui=en-US&rs=en-US&ad=US&fromAR=1). You specify the math function you want to apply in a parameter. — fixer1234, Apr 21 '16 at 06:14

score 2 · Accepted Answer · answered Apr 20 '16 at 14:56

2

There is a FORMULATEXT which is available from version 2013.

So you can use both
FORMULATEXT(A1)
and
SUBSTITUTE(FORMULATEXT(A1),"SUM","COUNT")
Second will result the formula as a text, you can't evaluate it.

For versions before 2010 you can do it only with VBA.

answered Apr 20 '16 at 14:56

Máté Juhász

21,403
6
54
73

I don't have ready access to Excel to play with this, but check out using EVALUATE on your last step. – fixer1234 Apr 21 '16 at 06:19
@fixer1234: Evaluate is available only in VBA. – Máté Juhász Apr 21 '16 at 07:34
Through at least Excel 2010, you could use EVALUATE in a formula that references named ranges. – fixer1234 Apr 21 '16 at 07:39
@fixer1234: Are you sure? It doesn't work for me (2013), also looking online help I couldn't find any reference to it. – Máté Juhász Apr 21 '16 at 07:42
See my comment on the question (I found a bunch of online references, some saying that it isn't documented in Excel). – fixer1234 Apr 21 '16 at 07:44
1

This SU post describes using it inside the name manager: http://superuser.com/a/774135/364367. Another reference: https://www.sumproduct.com/thought/evaluating-evaluate.html. Also: http://chandoo.org/wp/2011/05/16/lost-excel-functions/ – fixer1234 Apr 21 '16 at 07:52
@fixer1234 great research!! I've never heard about that. – Máté Juhász Apr 21 '16 at 08:02

score 2 · Answer 2 · answered Apr 21 '16 at 18:45

This is really a supplement to Máté Juhász's answer rather than a complete solution to the question. An answer is the only way to include an image and this approach may be limited as far as which versions of Excel it will work with.

I mentioned the EVALUATE function in comments on the question and on Máté's answer. I don't have access to Excel 2013 (and don't know to what extent Máté may have tested this). I did a quick test on Excel 2007, which, unfortunately, doesn't support the FORMULATEXT function. So I couldn't apply this directly to that answer. However, here is the nature of how it would be used; it would need to be adapted to the actual solution.

Cell A1 contains the original formula in the question. A2 contains the substituted text (had to hard code the A1 formula since FORMULATEXT wasn't supported). A3 is a reference to a named range, which contains the EVALUATE function applied to the substituted formula.

If this works in Excel 2013, the solution would look like this:

Create a named range (call it, say, "formula"), defined as:

=EVALUATE(SUBSTITUTE(FORMULATEXT(A1),"SUM","COUNT"))

Refer to the named range for the result:

=formula

Can you apply a formula to a formula in Excel instead of the value?

2 Answers2