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
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
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
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
RE: Friends:Excel 97 onwards got Array Functions
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.