# Array Formula Help

#### Dave E

##### New Member
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

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

#### pgc01

##### MrExcel MVP
Hi Dave

Try:

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

#### Stormseed

##### Banned
=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

##### New Member
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

##### MrExcel MVP
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

##### New Member
Brilliant - thanks :D

#### Stormseed

##### Banned
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

##### MrExcel MVP
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

##### Banned
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

##### MrExcel MVP
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?

Replies
8
Views
348
Replies
2
Views
105
Replies
0
Views
188
Replies
1
Views
139
Replies
0
Views
275

1,191,274
Messages
5,985,695
Members
439,974
Latest member
sjoerdbosch

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back