help: Nested function as simple as UDF, but faster in calculation speed

[yurble_vn]

Just wonder, is there any way to define UDF as nested function

For example, I have a nested function like this:


SUMPRODUCT((INDIRECT($AC$2)=$S28)*(INDIRECT($AB$2)=$T28),INDIRECT(U$3))

So, is there anyway to define an UDF which actualy is the same as above function. I mean it's just a simplification of the nested function.

thanks all in advance

[Bob Phillips]

I am sure I am not understanding what you are asking, but a UDF is a function and acts like any worksheet function, so you can embed/nest it within another function just as easily.

[yurble_vn]

Hi XLD,

What I mean is a simplier call for the long nested function.

For example, with following nested function:
=SUMPRODUCT((INDIRECT($AC$2)=$S28)*(INDIRECT($AB$2)=$T28),INDIRECT(U$3))

Is there anyway to call it in a simplier way, let say, for example:

=myfunction( $AC$2=$S28, $AB$2=$T28 , U$3 )

But it must still be a excel built in function, so it can calculate faster. And the file will look simplier.


Thanks for prompt help.

[Norie]

What makes you think it will be faster doing this in code?

SUMPRODUCT and INDIRECT are built-in Excel worksheet functions.

[Bob Phillips]

If it must be an Excelbuilt-in, then not much, the INDIRECT constrains it.

=IF(AND(INDIRECT($C$2)=$S2,INDIRECT($B$2)=$T2),INDIRECT(U$3),0)

[yurble_vn]

I'm not looking for another UDF or any other function.

WHat I'm trying to do here, is: instead of call the very below statement, is there any to simplify the calling way. For example, when I type: myfunctionname($AC$2=$S28, $AB$2=$T28 , U$3), excel will understand as following:

=SUMPRODUCT((INDIRECT($AC$2)=$S28)*(INDIRECT($AB$2)=$T28),INDIRECT(U$3))

[Bob Phillips]

Those statements are contradictory.

If you are not looking for a UDF or another function, how can you do anything BUT keep the formula that you have.

[matthewspatrick]

Bob is right; your request is not making sense. Bob correctly noted that your options are either:

Keep the existing Excel formula or
Create a UDF in code to "hide" the complexity of the Excel formula

All in all, your formula is not very complex. The native Excel formula will calculate faster than a UDF, so what is gained?

[yurble_vn]

Maybe I unrealistically demanded

I just wondered there must be some way to simplify the nested function so I tried to seek for it. Because, it, in some way, sound useful:
1. Simple/lighter file
2. Can easily change all cells function in a controlable manner...

Anyway, thank all for your caring and considering. You all are so kind. Happy to be here with all.