If Average???

[Daniellel]

Is there a way of doing an average sum * =AVERAGE(A1:A900) *
with a criteria??? * =AVERAGEIF(B1:B900,"London",A1:A900) *

Is there a way of doing this? even if it is a round the houses way...

 

[yogeshmaney]

=AVERAGE(IF(B1:B900="London",A1:A900))


and ctrl+Alt+Enter

 

[Daniellel]

Sorry, this isnt working - i am just getting #Value# ??

 

[yogeshmaney]

Its a array formula. to enter use ctrl+Alt+Enter

BTW what wrong with Averageif function, its working fine.

 

[Daniellel]

Didnt know if there was an average if function? Still no luck... I an doing the CTRL+ALT+Enter......

 

[snoopyhr]

for array formula use CSE that is Ctrl Shift Enter and you should get {} at the begining and end of formula when done.

 

[Daniellel]

No, nothing is happening when i press them...

 

[snoopyhr]

applying "yogeshmaney" formula:

Excel Workbook
	A	B	C	D	E	F
1	Some number	City				Result
2	10	London				55
3	100	London
4	30	Yokyo
5	40	Vien
6	20	Paris
7
Sheet4

which works perfectly

 

[Daniellel]

I am not sure if there is a problem with my Excel because when i press Ctrl+Alt+Enter NOTHING HAPPENS???

Do I need to enable something in my options or settings for this function to work?

I am on Excel 2003 - does this effect it?

 

[T. Valko]

I am not sure if there is a problem with my Excel because when i press Ctrl+Alt+Enter NOTHING HAPPENS???

Do I need to enable something in my options or settings for this function to work?

I am on Excel 2003 - does this effect it?

The correct key combination is CTRL, SHIFT, ENTER.

Array formulas are entered differently than a regular formula. After you type in a regular formula you hit the ENTER key. With an array formula you *must* use a combination of keys. Those keys are the CTRL key, the SHIFT key and the ENTER key. That is, hold down both the CTRL key and the SHIFT key then hit the ENTER key.

When done properly Excel will enclose the formula in squiggly brackets { }. You can't just type these brackets in, you *must* use the key combo to produce them. Also, anytime you edit an array formula it *must* be re-entered as an array using the key combo.