Formula for extracting a raw number

tmk0427

Board Regular
Joined
Dec 14, 2005
Messages
50
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.
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,799
Messages
5,574,387
Members
412,590
Latest member
Velly
Top