Formula for extracting a raw number

[tmk0427]

Cell A1 contains [28.93% - 08-Aug] Cell A2 contains [59.53% - 10-Jan] and cell A3 contains the formula

I'm trying to create a formula say in cell A3 that will just take the 28.93% without the percentage sign in say cell A1 and add it the cell A2 number of 59.53% without the percentage sign and average the two numbers to find the average of the two and be shown as a percentage.

I still want the dates and the percentage sign to show in the A1 and A2 cells. I hope that makes sense. Thanks.

 

[Andrew Poulsom]

Welcome to the Board.

Try:

=AVERAGE(LOOKUP(9.99999999999999E+307,--(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))))),LOOKUP(9.99999999999999E+307,--(LEFT(A2,ROW(INDIRECT("1:"&LEN(A2)))))))

 

[Richard Schollar]

Hi

Try:

=(VALUE(LEFT(A1,FIND("%",A1)-1))+VALUE(LEFT(A2,FIND("%",A2)-1)))/(2*100)

and for mat A3 as %number format.

Regards

Richard

 

[NBVC]

TRY:

=AVERAGE(VALUE(LEFT(A1,FIND("%",A1)-1)),VALUE(LEFT(A2,FIND("%",A2)-1)))&"%"

EDIT:

Actually, that will make the result a text string.

To make it numberical use:

=AVERAGE(VALUE(LEFT(A25,FIND("%",A25)-1)),VALUE(LEFT(B25,FIND("%",B25)-1)))/100

And format to Percentage and 2 decimals.

 

[barry houdini]

Another alternative ...

=AVERAGE(LEFT(A1:A2,FIND("%",A1:A2)-1)/100)

confirmed with CTRL+SHIFT+ENTER

format as percentage

particularly useful if you have a larger range of cells, rather than just 2