naming a function same way naming a range

[lezawang]

Hi
I have nested if() i.e. =if(cond1, "a", if(cond2,"b",if(cond3,"c",f)))
I want to know if I can name that function to for example XYZ and use that name to pass it to another function instead of passing the whole function. Similar idea of naming a range A1:A20 as ABC and then use it as =sum(ABC). Thank you.

 

[Joe4]

You can create your own functions in Excel, but that must be done using VBA.
See: https://support.office.com/en-us/ar...in-excel-2f06c10b-3622-40d6-a1b2-b6748ae8231f

 

[jproffer]

You can name the cell that formula is in, and then refer to that name in another formula. You can't name the actual formula and then use it like "=MyFormula(A1) ", no...is that what you're asking?

 

[dreid1011]

Yes, you can do that. In the 'Refers to' box, just type in the formula as you would type it into a cell. However, as I tested it, the formula got messed up after first entry, so I had to go back into the Name Manager and fix the ranges.

By doing this, the name then references the result of the formula you put in.

Here's what I did:

I made an entry in the Name Manager for this formula and named it "Fives" and typed that formula into the "Refers To" box:

=IF(B1="x",B2,IF(C1="x",C2,D2))

The SUM formula then returned the sum of E2 and whatever the result of my formula was, 5, 10, 55, etc.

And after further testing, you don't even need to put the named formula on the sheet anywhere.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]named formula here
[/TD]
[TD]x
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD]5
[/TD]
[TD]10
[/TD]
[TD]55
[/TD]
[TD]100
[/TD]
[TD]=SUM(Fives,E2)
[/TD]
[/TR]
</tbody>[/TABLE]