×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Friends:Excel 97 onwards got Array Functions
2

Friends:Excel 97 onwards got Array Functions

Friends:Excel 97 onwards got Array Functions

(OP)
Writing macros may be time consuming sometimes and I have found direct use of built in functions very helpful. Now after 97 excel(spreadsheet I use) has functions which can return results as an array or as a single cell output after going through an output.


You type in a formula and you dont press enter but you press Ctrl+Shift+Enter. The formula will then be enclosed within curly brackets to mark it as an array operation

You might want to check out this feature or provide more useful tips for us here

To get you going consider this

           A          B          C

        1  Lettuce   James       10
        2  Apples    Rick        50
        3  Tomatoes  James       5
        ......
        N  Lettuce    Dave       5

 where in C1 you have 10 lettuce sold by James

 How about the number of Tomatoes James has sold in a long ledger?

        You type this formula in some cell

            =sum((A1:An="Lettuce")*(B1:Bn="James)*(c1:c3))


and then you do the Ctr+Shift+Enter

I picked this one as offered in some web site(lost the adress but kept the stuff in memory).

Friends who would like to share more please do:

Respects

IJR

RE: Friends:Excel 97 onwards got Array Functions

2
(OP)
Sorry pour une erreur

You must type c1:cn instead of c1:c3 I gave above in the formula

Respects

IJR

RE: Friends:Excel 97 onwards got Array Functions

IJR mentioned a web site that described the use of array functions, but said he had forgotten the link.

The following link to John Walkenbach's site gives some further examples and a fairly detailed explanation.

http://j-walk.com/ss/excel/tips/tip74.htm

I have recently found them useful to answer the question (alternatives to D-functions to simplify/speed processing) raised in my thread:

Thread770-62429

regards

Chris

RE: Friends:Excel 97 onwards got Array Functions

Yep, using arrays is useful - especially when combined with defined user functions in VBAsic to return whole set of answers from for example a reactor model.

RE: Friends:Excel 97 onwards got Array Functions

Right, although array functions tend to become rather slow when you have large datasets. So don't forget the good old D-functions, SUMIF, SUMPRODUCT, etc. since these are quite a bit faster.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources