Trying to do Average of text as a string in a cell,getting these errors

**b_rianv** · 09-08-2014, 11:20 AM

Good morning everyone,

I am trying to a average formula for text as a srtring in a cell. I am working with this formulas.

First I tried this
=AVG($A1:$A15)...this gave me this error...#DIV/0!

So I then do this
=AVG(IFERROR($A1:$A15)..that gives me this...to few arguments for this function, and highlights $A15)

So I add this to the formula....
{=AVG(IFERROR($A1:$A15,1),0)} this is a array because it is a string of text. The answer it gives is a 0 in the cell. So what am I doing wrong??

Trying to find the average text string of text...ABCDEF,GHIJKL,AKBCDK,CDDFGG,AABCDF....ect
Each string is within one cell, so "cell A1"has this data ABCDEF, "cell A2" has this data EFFDCB, "cell A3" has this data ABDCBA....so on and so forth...

Need some help please.

Thank you,

**XOR LX** · 09-08-2014, 11:23 AM

Hi.

Forgive me for not understanding, but what do you mean by an "average" in this context? Average is a mathematical concept applied to numbers only. How can you have an average of text strings?

Do you perhaps mean the "most frequently occurring"? Or perhaps "occurring the median number of times"?

Regards

**b_rianv** · 09-08-2014, 11:28 AM

Trying to find the average string of text within a range. So excel can look through each string and compare each letter and of that string and then produce a average text string of the overall it has compared. Anyone follow me?

**XOR LX** · 09-08-2014, 11:30 AM

That's not clear at all, I'm afraid.

Perhaps if you gave a small dataset of about 10 strings and said what you would expect the "average" of those strings to be.

If we have something to go on then maybe we can help with the Excel work.

Regards

**b_rianv** · 09-08-2014, 11:38 AM

Here you go

**b_rianv** · 09-08-2014, 11:41 AM

I guess I am trying to say is have excel count the occurrences of text with in a string then produce a average/mean/sum/ of the existing strings it looks at.

Think of it like this.....1's and 0's

111000
101010
001101
111111
000000
001101
110001
then excel calculates the common string found between all of these...101011

**XOR LX** · 09-08-2014, 11:41 AM

Sorry - you don't understand.

What should be the result? You still haven't said what you mean by an "average" when it comes to text strings.

Perhaps that means something to you, but until you share it with us we're not going to be any the wiser.

Please state clearly your expected result(s) for the sample you just provided.

Regards

**XOR LX** · 09-08-2014, 11:49 AM

Originally Posted by b_rianv

I guess I am trying to say is have excel count the occurrences of text with in a string then produce a average/mean/sum/ of the existing strings it looks at.

Think of it like this.....1's and 0's

111000
101010
001101
111111
000000
001101
110001
then excel calculates the common string found between all of these...101011

But that's just the problem I'm having. And excuse me if it's just me for not seeing it, but I don't see why 101011 is the "common" string amongst those.

Can you explain why it is?

Regards

**b_rianv** · 09-08-2014, 12:39 PM

101011 is not, it is a EXAMPLE!! That is all. And that's is what excel would need to do is calculate the average of the strings it is looking at. So what formula or formulas needed might be ...Average, SUM, Countif, or whatever else I am trying to figure out.

**XOR LX** · 09-08-2014, 12:49 PM

But you have to tell us the logic first!

Excel is a machine! Unless we first give it a definition of what the average is for a range of text entries, then it can't do anything to help you.

Like I said, it already has in-built functions to return the mathematical average for a series of numbers, but until you define what is meant by an "average" of text values, i.e. what results you would like to see, and why, then there's absolutely nothing I, nor Excel, can do to help you.

So, given that dataset you gave above, try in your own words to explain what you think should be the average for those text values and why.

If we can get your reasoning for one dataset, then we can look at building the logic into an Excel formula so that we can then apply that to as many datasets as we like. But at the moment you've given us absolutely nothing to go on.

To repeat: there is no such thing as an average of text values per se. That definition is something you think should exist, but in fact doesn't.

As such, you have to tell us what you think it should mean. Then maybe we can help you.

Regards

**xladept** · 09-08-2014, 01:17 PM

Hi Brian,

Are all the strings 6 characters in length??

**b_rianv** · 09-08-2014, 01:21 PM

Sorry let me explain

In a cell there are a total of 6 letters E and O. These letters make a pattern EEEOOO,EOOEOE,OOOOOO,EEOOOE,and I would like excel to count these letters that are in that pattern from range A1:A15 then calculate a pattern from what it just counted from that range AS the mean pattern. But I don't think excel can count individual letters in a cell and then calculate a pattern.

Is that better

**b_rianv** · 09-08-2014, 01:24 PM

Yes the pattern is 6 characters in lenght.

Examples of patterns
EEEEEE,OOOOOO,EEOOOO,EEEEOO,OOOOEE,OEEOOO,OEOOOO,EEEEOO,OEOEOO...ect

**daddylonglegs** · 09-08-2014, 02:00 PM

OK, assuming you have 6 letter strings of "E" and "O" only this formula will produce another 6 letter string of "E" and "O" with the most common letter in each position:

=SUBSTITUTE(SUBSTITUTE(TEXT(SUMPRODUCT((MMULT({-1,1},COUNTIF(A1:A15,SUBSTITUTE("??????","?",{"E";"O"},{1,2,3,4,5,6})))>0)*10^{5,4,3,2,1,0}),"000000"),0,"E"),1,"O")

If there are equal numbers of "E" and "O" in any position then you get "E" for that position

**b_rianv** · 09-08-2014, 02:26 PM

So this formula will give me a average/mean pattern of "E" and "O" for the range this formula is looking at?

**b_rianv** · 09-08-2014, 02:30 PM

Ok I am trying the formula =SUBSTITUTE(SUBSTITUTE(TEXT(SUMPRODUCT((MMULT({-1,1},COUNTIF

(A1:A15,SUBSTITUTE("??????","?",{"E";"O"},{1,2,3,4,5,6})))>0)*10^

{5,4,3,2,1,0}),"000000"),0,"E"),1,"O")

And I am getting a error, excel highlight the whole formula.

Any ideas??

Never mind I got the formula to work!!!

**Olly** · 09-08-2014, 02:37 PM

Try this UDF:

Please Login or Register  to view this content.

Use as:

Formula:

Please Login or Register  to view this content.

If there is no mode for a given character position, it will return "?" for that character.

**daddylonglegs** · 09-08-2014, 02:37 PM

Originally Posted by b_rianv

And I am getting a error, excel highlight the whole formula.

OK, I put it in the worksheet - cell E2 highlighted

**b_rianv** · 09-08-2014, 02:45 PM

Yea I got it to work after taking a look at it...Thanks

**b_rianv** · 09-08-2014, 02:47 PM

Alright guys I will take a look at these tonight (formula and macro) when I get home from work.

**Olly** · 09-08-2014, 02:49 PM

Originally Posted by b_rianv

Alright guys I will take a look at these tonight (formula and macro) when I get home from work.

It's worth noting that the UDF above will handle any length of strings (and even multiple lengths within the given range), any size range, and any text content (not just letters "E" and "O")

**Pete_UK** · 09-08-2014, 03:58 PM

You kept asking for the "average", though you didn't really define what you meant by that. DDL's formula gives you the most frequent letter in any one position, but you could try this array* formula:

=SUBSTITUTE(SUBSTITUTE(TEXT(DEC2BIN(ROUND(AVERAGE(BIN2DEC(SUBSTITUTE(SUBSTITUTE(A1:A15,"E","1"),"O","0"))),0)),"000000"),"1","E"),"0","O")

This converts the Es and Os to a binary number for each cell in the range, then takes the average (rounded to an integer) and then converts it back to Es and Os.

* NOTE: you must use the key combination of CTRL-SHIFT-Enter (CSE) to commit an array formula, rather than the usual <Enter>.

The attached file shows my solution in blue, and DDL's in yellow.

Hope this helps.

Pete

**b_rianv** · 09-09-2014, 05:38 AM

Pete's formula worked great, just what I was looking for. Olly macro is nice and DDL's formula is nice for positions. I will be using Pete's as it give a average of the patterns. But both OLLY'S and DDL's will be put to use else where in different projects. Thank you ALL for your hard work and helping me my problem.

**Fjcosta** · 09-11-2014, 03:05 PM

Hi. Not sure of what you're trying to do. Counting the average of characters? Or the strings?

Trying to do Average of text as a string in a cell,getting these errors

LinkBack

Thread Tools

Rate This Thread

Display

Trying to do Average of text as a string in a cell,getting these errors

Re: Trying to do Average of text as a string in a cell,getting these errors

Re: Trying to do Average of text as a string in a cell,getting these errors

Re: Trying to do Average of text as a string in a cell,getting these errors

Re: Trying to do Average of text as a string in a cell,getting these errors

Re: Trying to do Average of text as a string in a cell,getting these errors

Re: Trying to do Average of text as a string in a cell,getting these errors

Re: Trying to do Average of text as a string in a cell,getting these errors

Re: Trying to do Average of text as a string in a cell,getting these errors

Re: Trying to do Average of text as a string in a cell,getting these errors

Re: Trying to do Average of text as a string in a cell,getting these errors

Re: Trying to do Average of text as a string in a cell,getting these errors

Re: Trying to do Average of text as a string in a cell,getting these errors

Re: Trying to do Average of text as a string in a cell,getting these errors

Re: Trying to do Average of text as a string in a cell,getting these errors

Re: Trying to do Average of text as a string in a cell,getting these errors

Re: Trying to do Average of text as a string in a cell,getting these errors

Re: Trying to do Average of text as a string in a cell,getting these errors

Re: Trying to do Average of text as a string in a cell,getting these errors

Re: Trying to do Average of text as a string in a cell,getting these errors

Re: Trying to do Average of text as a string in a cell,getting these errors

Re: Trying to do Average of text as a string in a cell,getting these errors

Re: Trying to do Average of text as a string in a cell,getting these errors

Re: Trying to do Average of text as a string in a cell,getting these errors

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] need to use a text string inside range - errors

[SOLVED] Find partial text string within another text string return original text into cell.

[SOLVED] Average column of cells,but ignore errors and return average of numbers that are there...

Formula to average cell range by month, ignoring blanks or errors

Bookmarks

Bookmarks

Posting Permissions