Friends:Excel 97 onwards got Array Functions 2

[IJR]

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&quot*(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

 

[IJR]

Sorry pour une erreur

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

Respects

IJR

 

[chrisbullough]

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

 

[Mutt]

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.

 

[joerd]

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.