PDA

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



yurble_vn
07-31-2007, 09:48 AM
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
07-31-2007, 10:31 AM
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
07-31-2007, 11:08 AM
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
07-31-2007, 11:28 AM
What makes you think it will be faster doing this in code?

SUMPRODUCT and INDIRECT are built-in Excel worksheet functions.

Bob Phillips
07-31-2007, 11:33 AM
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
08-01-2007, 05:18 AM
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
08-01-2007, 05:43 AM
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
08-01-2007, 07:39 AM
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
08-01-2007, 08:21 AM
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.