average if function
 

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

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

entered with ctrl + shift & enter

for TU
--

Regards,

Peo Sjoblom


"Darla" wrote in message
...
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

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


--

Regards,

Peo Sjoblom


"Peo Sjoblom" wrote in message
...
=AVERAGE(IF(LEFT(A2:A640,2)="TU",B2:B640))

entered with ctrl + shift & enter

for TU
--

Regards,

Peo Sjoblom


"Darla" wrote in message
...
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

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)


"Peo Sjoblom" wrote in message
...
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


--

Regards,

Peo Sjoblom


"Peo Sjoblom" wrote in message
...
=AVERAGE(IF(LEFT(A2:A640,2)="TU",B2:B640))

entered with ctrl + shift & enter

for TU
--

Regards,

Peo Sjoblom


"Darla" wrote in message
...
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 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"}&"*"))

Bob Phillips wrote...
or even

=AVERAGE(IF(OR(LEFT(A2:A640,2)="TU"),(LEFT(A2:A64 0,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 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))

-----Original Message-----
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:A64 0,5)
="ANA36"),B2:B640))


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

<g

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

Bob

"Harlan Grove" wrote in message
oups.com...
Bob Phillips wrote...
or even

=AVERAGE(IF(OR(LEFT(A2:A640,2)="TU"),(LEFT(A2:A64 0,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.

Bob Phillips wrote...
Where in the OP does it suggest that an array is required, and what is
the
point?
....
Bob Phillips wrote...
....
=AVERAGE(IF(OR(LEFT(A2:A640,2)="TU"),(LEFT(A2:A 640,5)="ANA36")),B2:B640)

array entered.

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)="A NA36"),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?