Average based on comma separated values present in same cell

[ranjan20]

Hi,

I am having data present in two columns of excel. Need to calculate average of Marks column based on say

Average of first five marks present in same cell of excel which is separated by comma.

The average of last 2 of mark present in excel for each sample same.

The result can be shown on column C.

Point I am struggling here is mark column data is huge rows and marks present are in single row seperated by comma. Really do not want to convert comma seperate into seperate excel.

Name Marks
ABC 1,3,5,22,33,45,55,67
CEF 2,20,30,24,33,44,23
ACC 7,8,22,44,55,66,77

please advise. many thanks

[JohnTopley]

Please Login or Register  to view this content.

=ave_it(b1)

[ranjan20]

Thanks John really appreciated could there be a way to establish this using excel existing formulae and without wriiting explicit function as well.

[ranjan20]

Apologies think forgot to mention and its worth that number of values for marks column could be dynamice list e.g 10 numbers 20 numbers or say 30 numbers and so on. For calculating average we have fixed values say average of first 5 number, average of first 10 numbers.

[JohnTopley]

I don't have a formula but some may: how do we know how many to average in each set of values (first 5, last 2 ?)

Please update your profile with your Excel version as answers can/do depend on knowing this : Windows 7 is not an Excel version.

[ranjan20]

Thanks for pointing version update on profile John:-) Just updated to win 10 never really realized but certainly very important input for experts when they provide solutions as with every upgrade there could be some new features. How many numbers we want to average is kind of fixed like below table. Please note here average column have used fixed integer to divide the SUM.


Name Mark Average of First two value Average of first 5 values
ABC 1,3,5,22,33,45,55,67 Sum (1+3+5+22+33/5) SUM(1+3+22+33+45+55)/6
CEF 2,20,30,24,33,44,23
ACC 7,8,22,44,55,66,77

Hope this helps.
Thanks

[ranjan20]

Apologies looks like plain text format didnt like copy of table trying to rep- post how this should look like

Name Mark Average of First two value Average of first 5 values
ABC 1,3,5,22,33,45,55,67 Sum (1+3+5+22+33/2) SUM(1+3+22+33+45+55)/5

[janmorris]

Windows 10 is your operating system, NOT an Excel version.

Please provide your Excel version so that people can try to make a solution that will work with it.

[JohnTopley]

First Windows 10 is NOT an Excel version: got o FILE >>HELP and you will see the the Excel version on the top right of page.

Re the average, you still offer no explanation as whether we average 5,6 or "n" numbers.

Sum (1+3+5+22+33/2) ??? 5 values but divide by 2 ?


SUM(1+3+22+33+45+55)/5 ?? 6 values but divide by 5 ?

[ranjan20]

Had to change system to attach but not able image.JPGto attach excel may be browser limitation attaching image pleaseBook1.xlsx

[janmorris]

Possible solution.

By taking the first N numbers before a comma, and using the formula from here, where $F$1 is the N first numbers we want to average) we can get a resulting formula like this:

Please Login or Register  to view this content.

see attached file

[ranjan20]

working excellently thanks much experts!!

[janmorris]

If the solution provided takes care of your question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - but for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

[ranjan20]

Hi,
Thanks just pressed the button of reputation. Getting some exception while testing with value 5. please see attached image.Attachment 758716

[janmorris]

the file attachment system has a bug when trying to attach in the regular posting window.

it is necessary to look in the bottom left of the reply window, and click on "Go Advanced", then attach the file.

[JohnTopley]

@Jan. You can test the #VALUE error on the file you posted.

[ranjan20]

please see attached test file showing exception when tested with count of 5 values

[janmorris]

i think i know where the error is occurring.... the last (5th) number does not have a comma after it.

here is un updated version that catches if the comma delimited string has less than the required number of commas, so basically it allows for the last number to not have a comma after it. but also any strings that have less than the required number will be averaged as they are.

[XLent]

FWIW, using prior attachment, if you have FILTERXML you can replicate the first n results using:

Please Login or Register  to view this content.

[ranjan20]

re-tested and can see that bug if fixed thanks and much appreciated. While testing another use case looks like its hitting another bug not having check for boundary condition say if there are really 24 numbers in list but on average marker one inserted 30, that case this utility is actually calculating all averages of 24 numbers thinking rather we can put some check. please see attached

[janmorris]

please give clear explanation what kind of "check" solution are you looking for.

[samba_ravi]

try below array formula
=IFERROR(AVERAGE(--MID(SUBSTITUTE(","&A2,",",REPT(" ",LEN(A2))),
ROW(INDIRECT("1:"&F1))*LEN(A2),LEN(A2))),"Out of Range")


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[ranjan20]

Hi,


Not sure if I could be be doing anything wrong here but

Please see attached sheet which has data sets I am dealing here. its working fine with 34 count of values but strangely breaking when
35 decimals values present in first column.

Attaching sheet which has 35 decimals values in column A where the average column of 35 number is
not calculating avg of 35 numbers rather its simply printing all 35 numbers on this list, that's not something correct I think.

Surprisingly this sheet is working correctly with non decimals numbers even with 35 count!

Thanks in advance.

[JohnTopley]

Updated VBA (Much simpler than formula!)

Function ave_n(r As Range) As Double
Dim t() As String

t = Split(r, ",")
nv = Range("Nv")

For i = 0 To nv-1
s = s + CSng(t(i))
Next i

ave_n = s / nv
End Function

=ave_n(A2)

[JohnTopley]

VBA in last post updated.

BUT see post from XLent re the formula in #19: go with it!

[XLent]

Out of interest, did you try post#19? On your latest sample that would return 2362.7343

The FILTERXML Function is available with most versions of XL from 2013 onwards, excluding Mac + online.

If you can use it, I would say it is the most straightforward of the formula based options suggested thus far.

[janmorris]

the formula i provided is likely a limitation with excel using that particular strategy.

i also get the same issue on Mac Excel 2021, but in G-Sheets it calculates correctly.

im sorry i can not help further. i suggest you try the formula provided by XLent in post #19

[ranjan20]

Hi- Yes attempted this and its working well, tested few use cases and working fine so far. thanks much!

[ranjan20]

Thanks Janmorris! this is all brainwork and sharing experiences!! you worked great thanks

[AliGW]

Your new request has been moved to its own thread: https://www.excelforum.com/excel-for...s-in-cell.html