average if function

[Darla]

I need to average the cost per lb if a string starts with...my spread sheet
is 640 rows long with over 10 averages needed.

I know how to do the formula except how to say "starts with" instead of "="
the string is quite long but I only need to reference the first part of it
"tu or ana36"
example below
TU SQRPR INB000160032000384000000200000000
ANA36 BAR INB000160001600002000000200000000
ANA36 BAR INB000160001600004000000200000000

 

[Peo Sjoblom]

=AVERAGE(IF(LEFT(A2:A640,2)="TU",B2:B640))

entered with ctrl + shift & enter

for TU

 

[Peo Sjoblom]

If you meant either of TU or ANA36

=AVERAGE(IF((LEFT(A2:A640,2)="TU")+(LEFT(A2:A640,5)="ANA36"),B2:B640))

also array entrered

 

[Bob Phillips]

or even

=AVERAGE(IF(OR(LEFT(A2:A640,2)="TU"),(LEFT(A2:A640,5)="ANA36")),B2:B640)

array entered.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Harlan Grove]

Peo Sjoblom wrote...

If you meant either of TU or ANA36

=AVERAGE(IF((LEFT(A2:A640,2)="TU")+(LEFT(A2:A640,5)="ANA36"),B2:B640))

....

Which means you're comparing A2:A640 to both TU and ANA36. One way
around that would be

=AVERAGE(IF(LEFT(A2:A640,2)="TU",1,LEFT(A2:A640,5)="ANA36"),B2:B640)

Another alternative, which scales better if there were several more
values to match, would be

=SUM(SUMIF(A2:A640,{"TU","ANA36"}&"*",B2:B640))
/SUM(COUNTIF(A2:A640,{"TU","ANA36"}&"*"))

 

[Harlan Grove]

Bob Phillips wrote...

or even

=AVERAGE(IF(OR(LEFT(A2:A640,2)="TU"),(LEFT(A2:A640,5)="ANA36")),B2:B640)

array entered.

....

Testing is good. Testing is your friend. Especially when you're still
up way too late.

Does your OR(..) term return an array? If not, it doesn't do the same
thing that Peo's formula does.

 

[Harlan Grove]

Harlan Grove wrote...
....

=AVERAGE(IF(LEFT(A2:A640,2)="TU",1,LEFT(A2:A640,5)="ANA36"),B2:B640)

....

Make that

=AVERAGE(IF(IF(LEFT(A2:A640,2)="TU",1,LEFT(A2:A640,5)="ANA36"),B2:B640))

 

[Biff]

-----Original Message-----
Harlan Grove wrote...
.... ="ANA36"),B2:B640)
....

Make that

=AVERAGE(IF(IF(LEFT(A2:A640,2)="TU",1,LEFT(A2:A640,5)

="ANA36"),B2:B640))


Testing is good. Testing is your friend. Especially when
you're still up way too late.

<g>

 

[Bob Phillips]

Where in the OP does it suggest that an array is required, and what is the
point?

Bob

 

[Harlan Grove]

Bob Phillips wrote...

Where in the OP does it suggest that an array is required, and what is the

How about a short example. A2:B7 containing

TU#### 1
ANA36@ 2
FOOBAR 3
TU 4
ANA44 5
ANA36 6

The *array* formula

=AVERAGE(IF(OR(LEFT(A2:A7,2)="TU"),(LEFT(A2:A7,5)="ANA36")),B2:B7)

returns 3.5, which is the average of *ALL* numbers in B2:B7. Your
formula when array-entered returns the average of *ALL* numbers in
B2:B7 no matter what's in A2:A7. On the other hand, Peo's array formula

=AVERAGE(IF((LEFT(A2:A7,2)="TU")+(LEFT(A2:A7,5)="ANA36"),B2:B7))

returns 3.25. If you enter 1s in C2, C3, C5 and C7, the array formula

=AVERAGE(IF(C2:C7,B2:B7))

also returns 3.25.

Anyway, I misread your formula. I thought the B2:B640 reference was
inside the IF call. It isn't. All your IF(OR(..),..) call does is
return an array of booleans to the AVERAGE function, which then ignores
them. Is that what you intended?