formula needed for this one...

O

ozwunder321

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.

thank
 
R

Ron Coderre

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
 
R

Ron Rosenfeld

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
Click to expand...

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
 
Top