CODEWITHSUNDEEP
excel
Donald Clark-Charest
Donald Clark-Charest

Reputation: 387

Excel: Creating sets or string of data within single cell

I've come across a few solutions, but I was wondering if this is just excel's limitation or is there no means to create multiple values to be assesses from the same cell. C3 creates average from all values in neighboring cell.

I'll explain why I wanted to do this. I'm working on a speadsheet for prices of items in a game and want to create a pseudo database out of one of the column's cells. An example of what I was thinking of doing it just simply have in one cell:

2, 40, 300, 200, 340

There's no such thing as C2 (cell name) sum all entries divided by total number of entry? An average of all the values separated by commas?

I know this is easily done with multiple cell, but I wanted to reduce the horizontal clutter, so was wondering if there's ANY way to seperate the values with the syntax, without using VBA or overcomplicated codes.

Upvotes: 1

Views: 344

Answers (2)

Scott Craner
Scott Craner

Reputation: 152585

the formula is a bear but:

=SUMPRODUCT(--MID(SUBSTITUTE(C2,",",REPT(" ",99)),(ROW(INDIRECT("1:" & LEN(C2)-LEN(SUBSTITUTE(C2,",",""))+1))-1)*99+1,99))/(LEN(C2)-LEN(SUBSTITUTE(C2,",",""))+1)

enter image description here

There is a lot happening here, so let's break it down:

LEN(C2)-LEN(SUBSTITUTE(C2,",",""))+1: Gets the count of items separated by ,, In this case 5. It is used twice in this formula. The second is the denominator to get the average.

The second is used in the (ROW(INDIRECT("1:" & LEN(C2)-LEN(SUBSTITUTE(C2,",",""))+1))-1). Here is the part that iterates. SUMPRODUCT Will cause a iterations and sum the parts.

The row will return 1,2,3,... to what is returned by the LEN(...) - LEN(...) function. We subtract one to start at 0.

Each iteration is multiplied by 99 and we add one because MID does not like 0 as the start.

The SUBSTITUTE(C2,",",REPT(" ",99)) creates a string that has 99 spaces in place of every ",".

The MID takes this string of number separated by now 100 spaces and parses it. The second criterion is iterating from 1,100,199,298,... and so forth the number of numbers in the cell times. Which will always fall on a space, since we gave it a 100 spaces in which to land.

The third criterion sets the length of the string to return on each iteration. We use 99 because again it will always hit a space after the number.

The -- in front of the MID will cause the returned parsed string to revert to a number. This forces Excel to drop the extra spaces and see only the number.

It is this number that SUMPRODUCT() maintains and adds to the total each iteration.

Upvotes: 5

VBA Pete
VBA Pete

Reputation: 2666

You could also write a VBA function for your issue:

Example:

enter image description here

Code to be pasted before the function can be used:

Public Function AverageCell(allItems As String) As Variant

Dim itemArray() As String
Dim totalsum As Variant
Dim totalav As Long

totalsum = 0
totalav = 0

itemArray() = Split(allItems, ",")

    For Each num In itemArray()
        totalsum = totalsum + num
        totalav = totalav + 1
    Next num

AverageCell = totalsum / totalav

End Function

Upvotes: 0

Related Questions