I want to average including all cell that may have text with numbers.

[afzalahmed81]

65R 0A 67 73 83 D 85RD 82RD 84RD 86R 74 D 76R 57 48 47 43 average here


Hi, I have data in excel rows which contain numbers and text attached with numbers as you can see above. I want the average of all numbers excluding text in the adjacent cell. average function skips all cells that have text in them. Anybody please suggest me a way out. I will be thankful.

Afzal Ahmed

+923212815781

[XLent]

based on samples, perhaps:

Please Login or Register  to view this content.

[afzalahmed81]

Thnaks XLent for your valuable suggestion.. The formula you wrote above is working only with the mentioned letters but in my case any alphabet may appear.
I need little more help can you devise a general formula that detaches any alphabet and averages numbers only. I tried the following one but having problems with it. I will be thankful.

=AVERAGE(INDEX(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B366:Y366,"A",""),"B",""),"C",""),"D",""),"E",""),"F",""),"G",""),"H",""),"I",""),"J",""),"K",""),"L",""),"M",""),"N",""),"O",""),"P",""),"Q",""),"R",""),"S",""),"T",""),"U",""),"V",""),"W",""),X",""),"Y",""),"Z","")+0,0))

[Sam Capricci]

you missed the "RD" in the combination and the quotes around the X, but that being noted I don't believe it will work anyway.
Any chance of using a helper row? Then it is easily accomplished though if you have multiple rows of these that would make it difficult.

[XLent]

It would help to see a more rounded example however based on samples you might try below alternative, to handle multitude of non-numeric suffixes

Please Login or Register  to view this content.

edit: modified number format for negatives {just in case}

[afzalahmed81]

Dear mates thanks for your time and effort, I am grateful. Formula suggested by XLent is really excellent it detaches all the alphabets and averages the numbers. a little problem is that it does not exclude zeroes in the division. For example i have 24 values in a row and if 20 of them are positive (no -ve) it divides the sum by 24 rather than 20 which affects the average. Is it possible to adjust this, I tried many things but no success e.g inserting "<>0" at different places in the formula. Any help will made my uphill task easier.

Best Regards
Afzal Ahmed

[AliGW]

Have a look at the end of this thread to find a really nice solution:

https://www.excelforum.com/excel-for...ml#post4821289

Try using AVERAGE instead of SUMPRODUCT.

[XLent]

Afzal; negative numbers should be included in the average already - if you wish to exclude modify the -0 to \0 in below format

To exclude 0s from the Average modify the Divisor

Please Login or Register  to view this content.

@AliGW; the linked thread is not suitable here given the strings are of varied length - some numbers, some strings etc, some w/space, some w/out space etc...

I see Bosco might be looking at it, so I suspect we will get a nicer solution shortly....

[afzalahmed81]

Thanks Brother XLent, You just gave me great favor, formula does exactly what i wanted. I am impressed by your coding skills, may you get more.
Can you recommend me a book, website or a pdf etc to learn some excel coding. I am already familiar with python. finally thanks again.

Best Regards

[Bosco]

1] A1:Q1 =65R 0A 67 73 83 D 85RD 82RD 84RD 86R 74 D 76R 57 48 47 43

2] Average numeric value exclude 0, in A3 enter formula :

=SUMPRODUCT(--TEXT(MID(TEXT(MID(A1:Q1&"x",1,ROW($2:$16)),),1,ROW($1:$15)),"[<>];-0;0;\0"))/COUNT(INDEX(1/LEFT(A1:Q1),0))

A3, will return 69.2857

Regards
Bosco

[afzalahmed81]

Thank you for your effort as well.