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
|
|||
|
|||
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. |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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"}&"*")) |
#8
|
|||
|
|||
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)) |
#9
|
|||
|
|||
-----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 |
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 |