Array Formula Help

[Dave E]

Hello

I am half-way there with an array formula (first time!) but I'm struggling with the nitty gritty... wonder if anyone can help do what I need...

(I've simplified the formala down to just a few columns etc - made up the scenario).

Col A

Row 1: W London
Row 2: C London
Row 3: Liverpool
Row 4: W London
Row 5: W London
Row 6: Birmingham

Col B

Row 1: Mark
Row 2: Mark
Row 3: John
Row 4: Mark
Row 5: Mark
Row 6: Keith

Col C

Row 1: 10
Row 2:
Row 3:
Row 4:
Row 5: 20
Row 6: 23

I am trying to average the values in Col C, when Col A contains "London", and Col B = Mark. This is what I have so far:

=AVERAGE((A1:A6="W London")*(B1:B6="Mark")*(C1:C6))

There are 2 problems with this:

1) It only picks up "W London"... I need it to pick up anything with "London" in the string.

2) The average should be 15 (with just W London), but it picks up row 4 as a 0, and averages to 10. I need to exclude the blank cells from the average.

I've tried various ways around, like including <>"" clauses etc, but they are probably miles off :-O

Cheers in advance for any help...

Dave E

 

[pgc01]

Hi Dave

Try:

=AVERAGE(IF(ISNUMBER(SEARCH("London",A1:A6))*(B1:B6="Mark")*ISNUMBER(C1:C6),C1:C6))
entered with CSE

 

[Stormseed]

=AVERAGE(IF(ISNUMBER(SEARCH("London",A1:A6))*(B1:B6="Mark")*ISNUMBER(C1:C6),C1:C6))

hello PGC,

(ISNUMBER
Why isnumber() ? I realize that arrays only work with numbers but could I not use the double unary ?

Again, ISNUMBER(C1:C6)

Could I not use the equation (C1:C6<>"") ?

 

[Dave E]

Sotrmseed - thats along the lines that I was trying...

But... I am very happy to report that it works spot on Thanks PGC...

I wonder if you could add one more thing for me....

I have a month start date, 01/01/08 in cell G1, and a month end date, 31/01/08 in cell G2.
I also have a column of dates in Col D.

I wish to extend the formula to limit it to only those rows whose date are within the start and end period...

Many thanks again

 

[pgc01]

Hi Dave

I'm glad it worked. You were almost there.If I understood correctly, try:

=AVERAGE(IF(ISNUMBER(SEARCH("London",A1:A6))*(B1:B6="Mark")*(D1:D6>=G1)*(D1:D6<=G2)*ISNUMBER(C1:C6),C1:C6))


Hi Stormseed

The IsNumber() is used 2 times for different reasons.

1 - Search() returns a number if it has success and an error if if fails. I need the IsNumber() not only to filter only the successful searches, but also to avoid error values in the array.

2 - In the second case I'm testing if column C has a number, but your alternative is also good, you can test for non empty cells and use (C1:C6<>""). It will also work.

 

[Dave E]

Brilliant - thanks :D

 

[Stormseed]

Thanks, PGC.

But you missed to tell me about using the double unary in this formula ? I guess I can and I am trying to do it

 

[pgc01]

Stormseed

The unary is to convert the value to a number. You don't need it in this formula because you are multiplying. When excel sees an arithmetic operation it converts automatically any non-number to number.

Try this:

=TRUE*"4"

Since there is a multipication excel converts the TRUE to 1 and the string "4" to the number 4 automatically.

Hope it's clear.

 

[Stormseed]

You don't need it in this formula because you are multiplying

hi again PGC

this has got me confused to the next level now. I dont know how to explain

 

[pgc01]

this has got me confused to the next level now. I dont know how to explain

Well, let's use a Sumproduct() as an example:

=SUMPRODUCT(--(A1:A10="A"),--(B1:B10="B"))

Each parameter is isolated. The result of (A1:A10="A") is a boolean and Sumproduct only accepts numbers. You have to convert the boolean to a number and you use the double unary minus to do it. You could also convert the boolean to a number in other ways, like

=SUMPRODUCT((A1:A10="A")+0,(B1:B10="B")+0)
=SUMPRODUCT((A1:A10="A")*1,(B1:B10="B")*1)

Now let's write the Sumproduct like this:

=SUMPRODUCT((A1:A10="A")*(B1:B10="B"))

In this case the booleans are multiplying. Excel sees the multiplication and automatically converts them to numbers. You don't need, therefore, to use the double unary minus in this case.

Is it now clear?