What is function that evaluates other functions indirectly?

  • Thread starter Thread starter nomail1983
  • Start date Start date
N

nomail1983

I have two questions.

First, what is the name of the function that computes other functions
"indirectly"?

As I recall, it is something like SUBFUNCTION(funcNumber, funcArgs).
Obviously, SUBFUNCTION is the wrong name.

Second, how can I get a comprehensive list of Excel functions using
Excel Help (perhaps), either alphabetically or by category?

I know that if I enter a "similar" function name in Excel Help, Show
All gives me the option of getting a list of related functions in a
category. But I was unable to think of a function similar to what I
call SUBFUNCTION. I would have been content with exhaustively
searching a list of all Excel functions. But I was unable to get that
comprehensive list.
 
Can't think what Function you are looking for.

SUMPRODUCT, INDIRECT, SUBTOTAL?

Download Peter Noneley's workbook from this site.

http://www.xlfdic.com/

Very good examples and usage of all the Excel Functions.


Gord Dibben MS Excel MVP
 
Can't think what Function you are looking for.
SUMPRODUCT, INDIRECT, SUBTOTAL?
Click to expand...

Aha! SUBTOTAL() is what I had in mind. But I see that it is more
limited than I thought. It would not have solved my problem -- which
I already solved.

The following paradigm illustrates the problem I was trying to solve.
The following works:

=sumproduct(row(A1:$A$90))

Why doesn't the following work?

=sumproduct(row(A1:address(90,1)))

I thought perhaps I needed an Excel equivalent of the (Unix) ksh eval
"function". And based on a vague recollection of some previous
postings that used SUBTOTAL(), I thought perhaps that was it. I guess
not.

PS: I eventually solved the problem by using INDIRECT() as follows:

=sumproduct(row(indirect("a1:a"&90)))

Of course, my problem was not nearly that simple. That is just a
"reduction" of the problem to its bare minimum.

By the way, what I __really__ want is for SUMPRODUCT() to cycle
through the numbers 1,2,...,90, where "90" is an expression, not a
constant, in a form similar to:

=sumproduct(--(function(expression+1:90)=expression))

I use "row(A1:A90)" for "1:90". If there is a more "obvious" way to
do that, I am interested in the alternative.
Download Peter Noneley's workbook from this site.
http://www.xlfdic.com/
Click to expand...

Thanks. I was hoping for something maintained by MS, preferrably
accessible by Excel Help (and preferrably local, not online). From
the two responses so far, I gather than none such thing exists :-(.
 
Back
Top