formula needed for this one...
 

hi,
this appears in one cell,

20: 5- 2- 4

How make a formula using the above,

(5*10+2*7+4*3)/20 in another cell.

thanks

formula needed for this one...
 

OK.....This isn't very elegant...but, here goes:

With
A1: 20: 5- 2- 4


B1: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,": ","|"),"- ","||",1),"-
","|||")&"||||"

C1:
=SUMPRODUCT(MID(B1,FIND({"|","||","|||"},B1)+{1,2, 3},FIND({"||","|||","||||"},B1)-FIND({"|","||","|||"},B1)-{1,2,3})*{10,7,3})/LEFT(A1,FIND(":",A1)-1)

Note: Watch out for text wrap in the display

With that example, C1 returns 3.8


Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"ozwunder321" wrote:


hi,
this appears in one cell,

20: 5- 2- 4

How make a formula using the above,

(5*10+2*7+4*3)/20 in another cell.

thanks




--
ozwunder321

formula needed for this one...
 

On Sat, 28 Oct 2006 20:32:02 +0100, ozwunder321
wrote:


hi,
this appears in one cell,

20: 5- 2- 4

How make a formula using the above,

(5*10+2*7+4*3)/20 in another cell.

thanks

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

Then use this formula:

=(REGEX.MID(A1,"\d+",2)*10+
REGEX.MID(A1,"\d+",3)*7+
REGEX.MID(A1,"\d+",4)*3)/
REGEX.MID(A1,"\d+",1)

The regular expressions extract the appropriate numbers, and perform the
specified arithmetic operations.

Note that this will only work if the values are positive integers. If that is
not always the case, the expression will need to be revised.


--ron