Formula for extracting a raw number

tmk0427

Board Regular
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.

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
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
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
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
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

Replies
11
Views
98
Replies
25
Views
690
Replies
2
Views
56
Replies
5
Views
95
Replies
2
Views
118