Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reusing formula | Excel Discussion (Misc queries) | |||
IF-THEN Formula help needed | Excel Worksheet Functions | |||
Formula needed | Excel Worksheet Functions | |||
continuous sum formula needed | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions |