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.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,091
Messages
5,857,303
Members
431,870
Latest member
Muratculous

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top