| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced text function
Hi
I wonder if somebody can help me. I need to find a formula that finds which cost pool split to apply to each cost pools. Example: Cost Pool Cost Pool Split Amount A BBB CA A/BBB/CA 20/30/50 50 =amount*cost pool split If I use search function it find me position of cost pool only FE=SEARCH("bbb",A2,1) I need to check position to "/" to find which cost pool split to apply Eva |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Advanced text function
Let's assume the following...
A2 contains A/BBB/CA B2 contains 20/30/50 C2 contains 50 Let E1:G1 contain A, BBB, and CA Now, define the following... 1) Select E2 2) Insert Name Define Name: CostPool Refers to: =EVALUATE("{"""&SUBSTITUTE(Sheet1!$A2,"/",""",""")&"""}") Click Add Name: CostPoolSplit Refers to: =EVALUATE("{"""&SUBSTITUTE(Sheet1!$B2,"/",""",""")&"""}") Click Ok Lastly, try... E2, copied across: =$C2*INDEX(CostPoolSplit,MATCH(E$1,CostPool,0))/100 Note that the formula can also be copied down, if needed. Hope this helps! In article , Eva wrote: Hi I wonder if somebody can help me. I need to find a formula that finds which cost pool split to apply to each cost pools. Example: Cost Pool Cost Pool Split Amount A BBB CA A/BBB/CA 20/30/50 50 =amount*cost pool split If I use search function it find me position of cost pool only FE=SEARCH("bbb",A2,1) I need to check position to "/" to find which cost pool split to apply Eva |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Advanced text function
On Mar 30, 3:19 pm, Domenic wrote:
Let's assume the following... A2 contains A/BBB/CA B2 contains 20/30/50 C2 contains 50 Let E1:G1 contain A, BBB, and CA Now, define the following... 1) Select E2 2) Insert Name Define Name: CostPool Refers to: =EVALUATE("{"""&SUBSTITUTE(Sheet1!$A2,"/",""",""")&"""}") Click Add Name: CostPoolSplit Refers to: =EVALUATE("{"""&SUBSTITUTE(Sheet1!$B2,"/",""",""")&"""}") Click Ok Lastly, try... E2, copied across: =$C2*INDEX(CostPoolSplit,MATCH(E$1,CostPool,0))/100 Note that the formula can also be copied down, if needed. Hope this helps! In article , Eva wrote: Hi I wonder if somebody can help me. I need to find a formula that finds which cost pool split to apply to each cost pools. Example: Cost Pool Cost Pool Split Amount A BBB CA A/BBB/CA 20/30/50 50 =amount*cost pool split If I use search function it find me position of cost pool only FE=SEARCH("bbb",A2,1) I need to check position to "/" to find which cost pool split to apply Eva |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Advanced text function
Domenic wrote...
Let's assume the following... A2 contains A/BBB/CA B2 contains 20/30/50 C2 contains 50 Let E1:G1 contain A, BBB, and CA Now, define the following... .... Could be done without XLM, but the formulas would be longer. Define n as, say, 255 and v as =ROW(INDEX(Sheet1!$1:$65536,1,1):INDEX(Sheet1!$1:$ 65536,n,1)) Then enter the following array formula in cell D2. =INDEX(MID($B2,SMALL(IF(MID("/"&$B2,v,1)="/",v),v), SMALL(IF(MID($B2&"/",v,1)="/",v),v)-SMALL(IF(MID("/"&$B2,v,1)="/", v),v)),MATCH(D$1,MID($A2,SMALL(IF(MID("/"&$A2,v,1)="/",v),v), SMALL(IF(MID($A2&"/",v,1)="/",v),v)-SMALL(IF(MID("/"&$A2,v,1)="/", v),v)),0))*$C2/100 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Advanced text function
It seems somewhat expensive, but most interesting nonetheless. Nice one
Harlan! In article .com, "Harlan Grove" wrote: Domenic wrote... Let's assume the following... A2 contains A/BBB/CA B2 contains 20/30/50 C2 contains 50 Let E1:G1 contain A, BBB, and CA Now, define the following... ... Could be done without XLM, but the formulas would be longer. Define n as, say, 255 and v as =ROW(INDEX(Sheet1!$1:$65536,1,1):INDEX(Sheet1!$1:$ 65536,n,1)) Then enter the following array formula in cell D2. =INDEX(MID($B2,SMALL(IF(MID("/"&$B2,v,1)="/",v),v), SMALL(IF(MID($B2&"/",v,1)="/",v),v)-SMALL(IF(MID("/"&$B2,v,1)="/", v),v)),MATCH(D$1,MID($A2,SMALL(IF(MID("/"&$A2,v,1)="/",v),v), SMALL(IF(MID($A2&"/",v,1)="/",v),v)-SMALL(IF(MID("/"&$A2,v,1)="/", v),v)),0))*$C2/100 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Advanced text function
wow! great, thank you-it works!!!!
Eva "Domenic" wrote: Let's assume the following... A2 contains A/BBB/CA B2 contains 20/30/50 C2 contains 50 Let E1:G1 contain A, BBB, and CA Now, define the following... 1) Select E2 2) Insert Name Define Name: CostPool Refers to: =EVALUATE("{"""&SUBSTITUTE(Sheet1!$A2,"/",""",""")&"""}") Click Add Name: CostPoolSplit Refers to: =EVALUATE("{"""&SUBSTITUTE(Sheet1!$B2,"/",""",""")&"""}") Click Ok Lastly, try... E2, copied across: =$C2*INDEX(CostPoolSplit,MATCH(E$1,CostPool,0))/100 Note that the formula can also be copied down, if needed. Hope this helps! In article , Eva wrote: Hi I wonder if somebody can help me. I need to find a formula that finds which cost pool split to apply to each cost pools. Example: Cost Pool Cost Pool Split Amount A BBB CA A/BBB/CA 20/30/50 50 =amount*cost pool split If I use search function it find me position of cost pool only FE=SEARCH("bbb",A2,1) I need to check position to "/" to find which cost pool split to apply Eva |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Advanced cell function. is it possible | Excel Worksheet Functions | |||
| Advanced use of LARGE function | Excel Worksheet Functions | |||
| Advanced Filter Function | Excel Worksheet Functions | |||
| Advanced IF function | Excel Worksheet Functions | |||
| Advanced COUNTIF Function | Excel Worksheet Functions | |||
Linear Mode
