Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Strip Out Four Characters, then Average
I posted this onto the end of a post that I put up earlier today. This is
tough and I can't tell if people stopped reading the old post or if no one has been able to come up with a solution. Basically, this is the scenario: New request; just a bit different. I have this kind of setup in several cells: ="(-30.5)" This will eliminate the left parentheses: =SUBSTITUTE(AD8,CHAR(40),"") This gets rid of the right: =SUBSTITUTE(AD8,CHAR(41),"") Also, I have to get rid of the quotes!! Four characters need to be stripped, and I want to calculate an average on top of that!! I know it's a tall order. Can it be done? I tried this, committed with CSE: =AVERAGE(--SUBSTITUTE(AD8:AD12,CHAR(40),""))--SUBSTITUTE(AD8:AD12,CHAR(41),"") That didn't work, but even if it did, I would still need to strip out the quotes somehow. Any ideas on this? Thanks, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Strip Out Four Characters, then Average
Hi,
Untill someone solves this in a single formula you could extract your nimbers using this then average them =(IF(ISNUMBER(FIND("-",AD8)),"-","")&LOOKUP(10^23,--MID(AD8,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},AD8&"0123 456789")),ROW(INDIRECT("1:"&LEN(AD8))))))*1 Mike "ryguy7272" wrote: I posted this onto the end of a post that I put up earlier today. This is tough and I can't tell if people stopped reading the old post or if no one has been able to come up with a solution. Basically, this is the scenario: New request; just a bit different. I have this kind of setup in several cells: ="(-30.5)" This will eliminate the left parentheses: =SUBSTITUTE(AD8,CHAR(40),"") This gets rid of the right: =SUBSTITUTE(AD8,CHAR(41),"") Also, I have to get rid of the quotes!! Four characters need to be stripped, and I want to calculate an average on top of that!! I know it's a tall order. Can it be done? I tried this, committed with CSE: =AVERAGE(--SUBSTITUTE(AD8:AD12,CHAR(40),""))--SUBSTITUTE(AD8:AD12,CHAR(41),"") That didn't work, but even if it did, I would still need to strip out the quotes somehow. Any ideas on this? Thanks, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Strip Out Four Characters, then Average
I'm confused.
Do you have this formula ="(-30.5)" or do you have the actual text "(-30.5)" or even the text ="(-30.5)" Maybe one of these--just keep adding more (not too many more!) =substitute()'s: =AVERAGE(--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(AD8:AD12,"(",""), ")",""),"""","")) (still an array formula) And I didn't include the another =substitute() to remove the = sign. ryguy7272 wrote: I posted this onto the end of a post that I put up earlier today. This is tough and I can't tell if people stopped reading the old post or if no one has been able to come up with a solution. Basically, this is the scenario: New request; just a bit different. I have this kind of setup in several cells: ="(-30.5)" This will eliminate the left parentheses: =SUBSTITUTE(AD8,CHAR(40),"") This gets rid of the right: =SUBSTITUTE(AD8,CHAR(41),"") Also, I have to get rid of the quotes!! Four characters need to be stripped, and I want to calculate an average on top of that!! I know it's a tall order. Can it be done? I tried this, committed with CSE: =AVERAGE(--SUBSTITUTE(AD8:AD12,CHAR(40),""))--SUBSTITUTE(AD8:AD12,CHAR(41),"") That didn't work, but even if it did, I would still need to strip out the quotes somehow. Any ideas on this? Thanks, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Strip Out Four Characters, then Average
And you have another response at the other thread.
ryguy7272 wrote: I posted this onto the end of a post that I put up earlier today. This is tough and I can't tell if people stopped reading the old post or if no one has been able to come up with a solution. Basically, this is the scenario: New request; just a bit different. I have this kind of setup in several cells: ="(-30.5)" This will eliminate the left parentheses: =SUBSTITUTE(AD8,CHAR(40),"") This gets rid of the right: =SUBSTITUTE(AD8,CHAR(41),"") Also, I have to get rid of the quotes!! Four characters need to be stripped, and I want to calculate an average on top of that!! I know it's a tall order. Can it be done? I tried this, committed with CSE: =AVERAGE(--SUBSTITUTE(AD8:AD12,CHAR(40),""))--SUBSTITUTE(AD8:AD12,CHAR(41),"") That didn't work, but even if it did, I would still need to strip out the quotes somehow. Any ideas on this? Thanks, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Strip Out Four Characters, then Average
Mike, yours calculated the Median. I needed the Mean and the Median. Dave,
your function worked perfect. I got the average, then got the median; everything works great now!! Thanks all!! Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Glenn" wrote: ryguy7272 wrote: I posted this onto the end of a post that I put up earlier today. This is tough and I can't tell if people stopped reading the old post or if no one has been able to come up with a solution. Basically, this is the scenario: New request; just a bit different. I have this kind of setup in several cells: ="(-30.5)" This will eliminate the left parentheses: =SUBSTITUTE(AD8,CHAR(40),"") This gets rid of the right: =SUBSTITUTE(AD8,CHAR(41),"") Also, I have to get rid of the quotes!! Four characters need to be stripped, and I want to calculate an average on top of that!! I know it's a tall order. Can it be done? I tried this, committed with CSE: =AVERAGE(--SUBSTITUTE(AD8:AD12,CHAR(40),""))--SUBSTITUTE(AD8:AD12,CHAR(41),"") That didn't work, but even if it did, I would still need to strip out the quotes somehow. Any ideas on this? Thanks, Ryan--- Array formula (commit with CTRL+SHIFT+ENTER): =AVERAGE(--SUBSTITUTE(SUBSTITUTE(AD8:AD12,")",""),"(","")) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Strip Out Four Characters, then Average
Glenn, just saw yours. Your solution worked great too!
Thanks for everything!! Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Glenn" wrote: ryguy7272 wrote: I posted this onto the end of a post that I put up earlier today. This is tough and I can't tell if people stopped reading the old post or if no one has been able to come up with a solution. Basically, this is the scenario: New request; just a bit different. I have this kind of setup in several cells: ="(-30.5)" This will eliminate the left parentheses: =SUBSTITUTE(AD8,CHAR(40),"") This gets rid of the right: =SUBSTITUTE(AD8,CHAR(41),"") Also, I have to get rid of the quotes!! Four characters need to be stripped, and I want to calculate an average on top of that!! I know it's a tall order. Can it be done? I tried this, committed with CSE: =AVERAGE(--SUBSTITUTE(AD8:AD12,CHAR(40),""))--SUBSTITUTE(AD8:AD12,CHAR(41),"") That didn't work, but even if it did, I would still need to strip out the quotes somehow. Any ideas on this? Thanks, Ryan--- Array formula (commit with CTRL+SHIFT+ENTER): =AVERAGE(--SUBSTITUTE(SUBSTITUTE(AD8:AD12,")",""),"(","")) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Strip Out Four Characters, then Average
ryguy7272 wrote:
I posted this onto the end of a post that I put up earlier today. This is tough and I can't tell if people stopped reading the old post or if no one has been able to come up with a solution. Basically, this is the scenario: New request; just a bit different. I have this kind of setup in several cells: ="(-30.5)" This will eliminate the left parentheses: =SUBSTITUTE(AD8,CHAR(40),"") This gets rid of the right: =SUBSTITUTE(AD8,CHAR(41),"") Also, I have to get rid of the quotes!! Four characters need to be stripped, and I want to calculate an average on top of that!! I know it's a tall order. Can it be done? I tried this, committed with CSE: =AVERAGE(--SUBSTITUTE(AD8:AD12,CHAR(40),""))--SUBSTITUTE(AD8:AD12,CHAR(41),"") That didn't work, but even if it did, I would still need to strip out the quotes somehow. Any ideas on this? Thanks, Ryan--- Array formula (commit with CTRL+SHIFT+ENTER): =AVERAGE(--SUBSTITUTE(SUBSTITUTE(AD8:AD12,")",""),"(","")) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Strip Out Four Characters, then Average
Hi,
You can use this formula to extract the numeric portion on the string (along with the sign) =1*MID(B9,MIN(SEARCH({"-";0;1;2;3;4;5;6;7;8;9},B9&"-0123456789",1)),SEARCH(")",B9,1)-MIN(SEARCH({"-";0;1;2;3;4;5;6;7;8;9},B9&"-0123456789",1))) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "ryguy7272" wrote in message ... I posted this onto the end of a post that I put up earlier today. This is tough and I can't tell if people stopped reading the old post or if no one has been able to come up with a solution. Basically, this is the scenario: New request; just a bit different. I have this kind of setup in several cells: ="(-30.5)" This will eliminate the left parentheses: =SUBSTITUTE(AD8,CHAR(40),"") This gets rid of the right: =SUBSTITUTE(AD8,CHAR(41),"") Also, I have to get rid of the quotes!! Four characters need to be stripped, and I want to calculate an average on top of that!! I know it's a tall order. Can it be done? I tried this, committed with CSE: =AVERAGE(--SUBSTITUTE(AD8:AD12,CHAR(40),""))--SUBSTITUTE(AD8:AD12,CHAR(41),"") That didn't work, but even if it did, I would still need to strip out the quotes somehow. Any ideas on this? Thanks, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do i strip off Upper case Characters to another cell in Excel. | Excel Worksheet Functions | |||
STRIP CHARACTERS | Excel Worksheet Functions | |||
Strip Out Time | New Users to Excel | |||
TAB STRIP | Excel Discussion (Misc queries) | |||
tab strip | New Users to Excel |