Thread: Cell values in the middle of a formula

Are there always 3 values in each cell? Once you put multiple values within a single cell, Excel sees them as a single string (text). It will take some manipulation to get excel to view them as numbers.

Try:

=SUMPRODUCT(--MID(SUBSTITUTE(A1,",",""),COLUMN(A1:INDEX(1:1,0,LEN(SUBSTITUTE(A1,",","")))),1))/(LEN(SUBSTITUTE(A1,",","")))

copied down.

If the numbers are not always single digits and / or vary in number:

=AVERAGE(INDEX(0+MID(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),1+(ROW(A$1:INDEX($A:$A,1+LEN(A1)-LEN(SUBSTITUTE(A1,",",""))))-1)*LEN(A1),LEN(A1)),0))

edit: above assumes A1 etc are formatted as Text - prior to data entry "just in case"
eg 100,200 would otherwise be a number and the TEXT wrappers would be a step too far for the above in XL2003

Here's my solution using LEFT, MID and RIGHT (Assuming always 3 numbers)

=AVERAGE(LEFT(A1,FIND(",",A1)-1)+0,MID(A1,FIND(",",A1)+1,FIND(",",A1,FIND(",",A1)+1)-FIND(",",A1)-1)+0,RIGHT(A1,LEN(A1)-FIND(",",A1,FIND(",",A1)+1))+0)

I believe the suggestion in post # 4 should work for you, no ?

I'm afraid I don't have the time to go through in depth presently (I will post back if no-one else explains in the meantime) but regards E2 reference

G2:
=AVERAGE(INDEX(0+MID(SUBSTITUTE(E2,",",REPT(" ",LEN(E2))),1+(ROW(A$1:INDEX($A:$A,1+LEN(E2)-LEN(SUBSTITUTE(E2,",",""))))-1)*LEN(E2),LEN(E2)),0))
copied down as nec.

OK as promised a slightly extended breakdown of how the above is processed...

First it's important we acknowledge the fact we're utilising a consistent delimiter between values (comma) ... and also that we don't want to use VBA (or XLM Call)

If we assume for sake of demo that:

E2:
5.52,100.23,10.2,4

And our formula for conducting the average is:

G2:
=AVERAGE(INDEX(0+MID(SUBSTITUTE(E2,",",REPT(" ",LEN(E2))),1+(ROW(A$1:INDEX($A:$A,1+LEN(E2)-LEN(SUBSTITUTE(E2,",",""))))-1)*LEN(E2),LEN(E2)),0))

We can work through accordingly...

Before we can conduct the average we must first convert the string into an array of numbers.
First we replace our delimiter with a number of spaces - the number of spaces being determined by the overall length of the string

SUBSTITUTE(E2,",",REPT(" ",LEN(E2)))

this generates an elongated string

"5.52                  100.23                  10.2                  4"

We then need to extract each number from the now elongated string (which we will do using a MID construct) but first we need to determine how many numbers will need extracting in the first instance.

This number we can determine by comparing the length of the original string to the length of the original string once the comma delimiter(s) have been removed:

1+LEN(E2)-LEN(SUBSTITUTE(E2,",",""))

In this case this equates to:

1+18-15 -> 4

we push this value into an INDEX call as we want to create a reference to An where n is the value established by the above

INDEX($A:$A,1+LEN(E2)-LEN(SUBSTITUTE(E2,",","")))

so in our example this evaluates to:

INDEX($A:$A,4) -> A4

By using the above reference in conjunction with A1 we can create a range reference:

A$1:INDEX(...) -> A$1:A4

We can then push this range reference into a ROW call to generate an array of ROW numbers:

ROW(A$1:INDEX(...)) -> ROW(A$1:A4) -> {1;2;3;4}

This array of values is critical to the formula calculation in terms of the latter MID call but the next thing to note is that the first of our numeric sub-strings in the elongated string is not prefixed by spaces.
The result of which is that we actually want to subtract 1 from each of the ROW values as this figure will act as a multiplier to determine the start point for our MID extraction (this may not make sense at this stage)

(ROW(...)-1) -> {1;2;3;4} -> {0;1;2;3}

We know that each numeric sub string - with the exception of the 1st number - is now delimited by a number of spaces and that the number of spaces is itself the LEN of the original string.
To be sure of pulling each numeric sub string via MID we must then adjust our starting char position for each value ... this we can do by multiplying our now adjusted ROW values by the LEN of the original string

(ROW(...)-1)*LEN(E2)

still continuing with our example this will evaluate along the lines of:

{0;1;2;3}*18 -> {0;18;36;54}

We know that 0 is not a valid start position for our first sub string so we add 1 to the above values so as to offset our start position by 1 char to the right

1+(ROW(...)-1)*LEN(E2) -> 1+{0;18;36;54} -> {1;19;36;54}

So ... we now know the starting points in our elongated string in terms of conducting our MID
Given the uniform spaces between the values we also know the number of chars to extract from the start point in our MID call so as to be sure of pulling the numeric sub string.
This will be the LEN of the original string (this determined the number of spaces)

MID(SUBSTITUTE(...),{1;19;36;54},18)

so using the elongated string and the above we return 4 now separate strings:

{"5.52              ";"    100.23        ";"          10.2    ";"              4"}

Obviously we have some superfluous spaces in each string but given they are numeric we need not Trim - we can instead coerce to number by means of an arithmetic operation... in this case we choose to add 0:

0+MID(...)

so this in turn evaluates the above to:

{5.52;100.23;10.2;4}

So - we know have an array of 4 numbers... to avoid the need to commit the formula as an Array I opted to push these values into a further INDEX

INDEX({5.52;100.23;10.2;4},0)

against which we apply the outer Average

=AVERAGE(INDEX({5.52;100.23;10.2;4},0))

which will generate for us our expected answer of 29.99 (approx.)

As outlined this is by no means bullet proof and indeed I would say a VBA (User Defined Function) would make more sense in terms of simplicity, however, I hope has cleared the waters a little ... it's not the easiest formula to explain.