Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
=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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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"}&"*")) |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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)) |
#8
|
|||
|
|||
-----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 |
#9
|
|||
|
|||
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. |
#10
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using The Average Function if a cell has NA | Excel Worksheet Functions | |||
Average Function help | Excel Worksheet Functions | |||
How do I nesting subtotal function within average function in Exc | Excel Worksheet Functions | |||
Average function with If | Excel Worksheet Functions | |||
Using Average function when number is zero | Excel Worksheet Functions |