Formula for Average of 2 numbers in 1 cell

[sayayinx]

I need the formula to find the average of two numbers in once cell separated by a hyphen. Here's an example:

$25,000 - $34,000

Thanks!

 

[JackDanIce]

Welcome to the board.

Try:

=TEXT(AVERAGE(LEFT(A1,FIND(" -",A1)-1)+0,RIGHT(A1,LEN(A1)-FIND(" -",A1)-1)+0),"$#")

 

[T. Valko]

I need the formula to find the average of two numbers in once cell separated by a hyphen. Here's an example:

$25,000 - $34,000

Thanks!

Try this...

=AVERAGE(LEFT(A1,FIND(" ",A1)),MID(A1,FIND("-",A1)+1,15))

 

[Andrew Poulsom]

How about?

=AVERAGE(SUBSTITUTE(LEFT(A1,FIND(" ",A1)-1),"$",""),MID(A1,FIND("-",A1)+3,255))

JackDanIce's formula returned #VALUE! for me, as did Biff's. If I the numbers are preceded by £ rather than $ they both work.

 

[sayayinx]

Thank you very much!!!

 

[number kruncher]

Assuming the format stays similar, try

=AVERAGE(VALUE(MID(A1,2,FIND(" ",A1)-1)),VALUE(MID(A1,FIND(" $",A1)+2,LEN(A1)-FIND(" $",A1)-2)))

 

[Dannyh1]

Hi

Try:

=AVERAGE(VALUE(MID(A1,2,FIND(" -",A1)-2)),VALUE(RIGHT(A1,FIND("- $",A1)-3)))

 

[Dannyh1]

5 mins too late

 

[T. Valko]

Thank you very much!!!

You're welcome!