Excel Array Formulae 1

[TortolaChris]

Hi

Please could you remind me how to write array formuale. I thought there had been a lot of posts on this and there was a highly ranked FAQ too but can't find them.

Thanks

Chris

 

[Loomah]

Hi Chris
It's a bit tricky just giving a blanket explanation, especially as I'm no genius in this field!

In essence what you need to consider are ways in which you can write a formula to look at all the values in a range. As I say I'm not good at explaining stuff like this but when you think you have it worked out you need to enter the formula as an array by pressing CTRL+SHIFT+ENTER.

As an example here are a couple of simplistic array formulae working on a list of values in the range A1:A20 that will give different results if not entered as arrays

Count all values >50
{=SUM(IF($A$1:$A$20>50,1,0))}

Sum all values >50
{=SUM(IF($A$1:$A$20>50,$A$1:$A$20,0))}

What is the largest number <50
{=MAX(IF(A1:A20<50,A1:A20,0))}

Hope this helps a little
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[TortolaChris]

Thanks Loomah

Great help!

Cheers

Chris

 

[Loomah]

Chris
As an after thought have a look at this site. It's where I learnt the little bit I know (together with an explantion xlbo gave me some time ago!)

http://www.cpearson.com/excel/array.htm

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244