Generate avg from column of numbers

 StarLounger
 Posts: 94
 Joined: 09 May 2020, 14:00
Generate avg from column of numbers
In this example, with the data in column A, column E:AH returns random numbers with column AI averaging those numbers. Question, is it possible to generate the avg in column AI without calculating column E:AH?
Column A right now has 27 data points, but in reality, it could be any number.
Column A right now has 27 data points, but in reality, it could be any number.
You do not have the required permissions to view the files attached to this post.

 Administrator
 Posts: 69049
 Joined: 16 Jan 2010, 00:14
 Status: Microsoft MVP
 Location: Wageningen, The Netherlands
Re: Generate avg from column of numbers
If you have Microsoft 365, you may be able to use the RANDARRAY function.
I don't have Microsoft 365 myself, so I cannot provide the exact formula.
I don't have Microsoft 365 myself, so I cannot provide the exact formula.
Regards,
Hans
Hans

 2StarLounger
 Posts: 170
 Joined: 26 Jan 2017, 07:24
Re: Generate avg from column of numbers
Based on sir Hans idea!
may be>> =AVERAGE(RANDARRAY(1,30,MIN($A$2:$A$28),MAX($A$2:$A$28),FALSE))
you could also use online 365 version>> https://www.office.com/?auth=1
Nabeel
may be>> =AVERAGE(RANDARRAY(1,30,MIN($A$2:$A$28),MAX($A$2:$A$28),FALSE))
you could also use online 365 version>> https://www.office.com/?auth=1
Nabeel
You do not have the required permissions to view the files attached to this post.

 Lounger
 Posts: 38
 Joined: 11 Jun 2012, 20:37
Re: Generate avg from column of numbers
Without Office 365 you can do it with the likes of:, which, depending on your version of Excel, you may have to commit to the sheet using Ctrl+Shift+Enter, rather than the more usual Enter, then copy down.
Trying to come up with a way to get
So far (I may have brain block) I've come up with:
but I feel there has to be a more elegant way.
Code: Select all
=AVERAGE(INDEX($A$2:$A$28,RANDBETWEEN({1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1},{27,27,27,27,27,27,27,27,27,27,27,27,27,27,27,27,27,27,27,27,27,27,27,27,27,27,27,27,27,27})))
Trying to come up with a way to get
Code: Select all
{1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1},{27,27,27,27,27,27,27,27,27,27,27,27,27,27,27,27,27,27,27,27,27,27,27,27,27,27,27,27,27,27}
Code: Select all
=AVERAGE(INDEX($A$2:$A$28,RANDBETWEEN((ROW($A$1:$A$30)ROW($A$1:$A$30)+1),(ROW($A$1:$A$30)ROW($A$1:$A$30)+ROWS($A$2:$A$28)))))

 Lounger
 Posts: 38
 Joined: 11 Jun 2012, 20:37

 StarLounger
 Posts: 94
 Joined: 09 May 2020, 14:00
Re: Generate avg from column of numbers
I don't have 365, unfortunately, I only have 2016.
p45cal, that formula seems to be only taking one random number from A2:A28 and then averaging which would be the number itself.
Instead, I'm hoping to produce the results in Column AI which is averaging 30 random numbers from A2:A28. Then in AI3, average another 30 random numbers and so on.
p45cal, that formula seems to be only taking one random number from A2:A28 and then averaging which would be the number itself.
Instead, I'm hoping to produce the results in Column AI which is averaging 30 random numbers from A2:A28. Then in AI3, average another 30 random numbers and so on.

 Administrator
 Posts: 69049
 Joined: 16 Jan 2010, 00:14
 Status: Microsoft MVP
 Location: Wageningen, The Netherlands
Re: Generate avg from column of numbers
I don't see a way to do this with builtin formulas only. Here is a custom function:
Enter the following formula in a cell in row 2:
=AVERAGE(RandomArray($A$2:$A$28,30))
and fill down.
Code: Select all
Function RandomArray(rng As Range, n As Long)
ReDim a(1 To n) As Long
Dim i As Long
Dim j As Long
For i = 1 To n
j = Application.RandBetween(1, rng.Count)
a(i) = rng(j)
Next i
RandomArray = a
End Function
=AVERAGE(RandomArray($A$2:$A$28,30))
and fill down.
Regards,
Hans
Hans

 StarLounger
 Posts: 94
 Joined: 09 May 2020, 14:00
Re: Generate avg from column of numbers
Thank you Hans. This seems to work great.

 Lounger
 Posts: 38
 Joined: 11 Jun 2012, 20:37

 Administrator
 Posts: 69049
 Joined: 16 Jan 2010, 00:14
 Status: Microsoft MVP
 Location: Wageningen, The Netherlands
Re: Generate avg from column of numbers
Hi p45cal, I did enter your formula as an array formula with Ctrl+Shift+Enter. It didn't work as intended: the INDEX formula generates just one number.
Regards,
Hans
Hans

 StarLounger
 Posts: 94
 Joined: 09 May 2020, 14:00
Re: Generate avg from column of numbers
Yes, I also did try entering it with CSE. As I evaluate the formula, it shows that it selects a random number between 1 and 27, and then averages that one number. I should not ever get an average of 1, but that's what this gives me in some cells.

 Lounger
 Posts: 38
 Joined: 11 Jun 2012, 20:37

 Lounger
 Posts: 38
 Joined: 11 Jun 2012, 20:37
Re: Generate avg from column of numbers
In Excel 2003 I can't even get a RANDBETWEEN with 2 arrays to come up with anything but #VALUE!. :(

 Administrator
 Posts: 69049
 Joined: 16 Jan 2010, 00:14
 Status: Microsoft MVP
 Location: Wageningen, The Netherlands

 Lounger
 Posts: 38
 Joined: 11 Jun 2012, 20:37
Re: Generate avg from column of numbers
Ahh, right… then I can't test because I have no Excel 2007 anywhere.

 StarLounger
 Posts: 94
 Joined: 09 May 2020, 14:00
Re: Generate avg from column of numbers
Thank you for all your effort p45cal, but Hans' UDF works great.

 5StarLounger
 Posts: 1198
 Joined: 28 Feb 2015, 13:11
 Location: Hof, Bayern, Germany
Re: Generate avg from column of numbers
@P45cal
Hi
Some feedback for you using Excel in Office 2007 , and some other stuff…
Your formula also does not work in any of my versions of 2007. ( It does not surprise me too much that your formula works in your 365, ref_3 )
You can get it to work with a small modification in 2007, 2010, and I expect also in all higher versions…. ..
To explain….
General:
I think it was, until fairly recent versions, quite commonly reported that Index in Excel stubbornly refuses to give array results. But that is not quite true. More likely is that some values couldn't be seen sometimes *** . There are a few workarounds known to get things working as they should.
( BTW, Application.Index in VBA, does work, without need of any workarounds, and is very useful , ref1 ref 2 )
The workarounds needed in Excel for earlier versions are empirically derived and no one really knows for sure what they are doing. But up to about version 2013 the worksrounds appear to be very stable.
( After 2013 its anyone’s guess: Microsoft started changing things which effected how anything involving array calculations work. We are seeing an increasing number of forum posts where things are working differently between versions for anything to do with array calculations. I have heard some of the higher “Excel intellectuals” comments that Microsoft originally messed up a lot of dependency chains which originally made the requirement to do CSE as this incorporated a workaround to circumvent that mess. That most often got things to work as they originally intended. When they finally got their act together, the new “Spill functionality" and the associated no longer needing CSE, was just a by product of finally fixing some Bugs. But to hide their embarrassment they introduced it as something new. The newest Excel is how it would have been from the start if they hadn’t messed it up!)
I think, so far, the problems are when you get something working in post 2013, and then it may or may not work in other versions. ( Note: Microsoft did not make one change. It appears that they did different changes on different versions above 2013 so it’s a spaghetti mess that might even change with updates!!). I think so far, mostly, if something is got working in pre 2013 with known workarounds, then usually it works in most version forward ( and also backward, provided of course, any functions your are using is available in your earlier version )
Specific to your formula:
The workaround is particularly strange , a few people have reported it, ( example ref _4 ) , no one understands it, and you will get different results depending on exactly what you do. It seems pointless trying to figure out what is going on. *** .
In the uploaded file are some intermediate lines and some stuff that seems to suggest that the modified version of your ( P45cal’s ) formula is working.
( Also, There is some correlation, but not total correlation with, the Evaluate(“ “) code lines in the next coding snippet
Code: Select all
Sub IndexItInVBA() ' http://www.eileenslounge.com/viewtopic.php?p=276107#p276107
Dim vTestTemp As Variant
Let vTestTemp = Application.Index(Range("$A$2:$A$28").Value, Array(16, 13, 24, 9, 18, 10, 13, 23, 20, 4, 6, 20, 14, 1, 6, 12, 1, 26, 19, 15, 21, 10, 11, 17, 6, 15, 12, 4, 18, 22), 1) ' Returns array P45calInVBA.JPG : https://imgur.com/HqpZXhD
Let vTestTemp = Evaluate("=INDEX($A$2:$A$28,{16,13,24,9,18,10,13,23,20,4,6,20,14,1,6,12,1,26,19,15,21,10,11,17,6,15,12,4,18,22},1)") ' returns a single number
Let vTestTemp = Evaluate("=INDEX($A$2:$A$28,N(IF(1,{16,13,24,9,18,10,13,23,20,4,6,20,14,1,6,12,1,26,19,15,21,10,11,17,6,15,12,4,18,22})),1)") ' returns a single number
Let vTestTemp = Evaluate("=Average(INDEX($A$2:$A$28,N(IF(1,{16,13,24,9,18,10,13,23,20,4,6,20,14,1,6,12,1,26,19,15,21,10,11,17,6,15,12,4,18,22})),1))") ' returns a single number of 44.9
End Sub
You will likely get slightly different results again from looking at parts of the formula via F9 in the formula bar
So , as I mentioned, it’s a total mess, and not worth trying to figure out what’s going on: The usual tools don’t necessarily tell you what’s going on: any arrays you are expecting are likely somewhere, but those arrays may or may not find their way to where or how you are trying to look at them.
But I will take a guess that the modified formula versions will work in all Excel versions. I can tell you that it works in all my 2007 and 2010 versions on various computers.
_.______________________
Just before I give you the modified formula, one other thing…
If I am not mistaken, we want inside the Randbetween( , ) two arrays. Both should have 30 elements. The first is full of 1s. The second is full of 27s. I can’t think of anything much better than what you did. I suppose I would have done it like thisp45cal wrote: ↑16 Oct 2020, 09:01.....So far (I may have brain block) I've come up with:but I feel there has to be a more elegant way.Code: Select all
=AVERAGE(INDEX($A$2:$A$28,RANDBETWEEN((ROW($A$1:$A$30)ROW($A$1:$A$30)+1),(ROW($A$1:$A$30)ROW($A$1:$A$30)+ROWS($A$2:$A$28)))))
RANDBETWEEN(ROW($1:$30)/ROW($1:$30),(ROW($1:$30)/ROW($1:$30))*27)
If I got that last bit correct, then the final formula, including the workaround to get Index to "find" the array , is ( CSE Entry )
Code: Select all
AVERAGE(INDEX($A$2:$A$28,N(IF(1,RANDBETWEEN(ROW($1:$30)/ROW($1:$30),(ROW($1:$30)/ROW($1:$30))*27))),1))
The RANDBETWEEN is most likely doing what it is supposed to and it produces the array what we want.
But the Index is not seeing it in your unmodified form.
(Some of my other investigations into these things suggest that when an Index is inside something, a dependency to part of a spreadsheet related matrix is lost). What we are doing in this modified form, is nesting the array inside something that does not change the contents of the array, but changes how Index sees it, or where it looks for it. No one knows for sure.
Exactly what you end up with is usually empirically found, or by chance.
Alan
Refs
_1 https://www.excelforum.com/tipsandtut ... ost5408376
_2 https://www.excelforum.com/excelprogra ... ost5410028
_3 https://excelxor.com/2014/09/05/indexr ... mment8893
_4 https://excelxor.com/2014/09/05/indexr ... ofvalues/
_5 https://stackoverflow.com/questions/471 ... turnarray
https://eileenslounge.com/viewtopic.php ... 84#p265384
https://www.mrexcel.com/board/threads/v ... up.806702/
You do not have the required permissions to view the files attached to this post.
Last edited by Doc.AElstein on 19 Oct 2020, 15:56, edited 1 time in total.
\ _ /

 Lounger
 Posts: 38
 Joined: 11 Jun 2012, 20:37
Re: Generate avg from column of numbers
Thanks for the feedback Alan; I went through it and the links  interesting stuff, but bearing in mind the current use by many, still, of the various versions of Excel mentioned, the sad conclusion for quite a few years hence may very well be:
and use Hans' udf.
Yes that's a fair bit shorter although I would keep the ROWS($A$2:$A$28) instead of the hardcoded 27 to save the user counting the rows involved.Doc.AElstein wrote: ↑17 Oct 2020, 22:01I suppose I would have done it like this
RANDBETWEEN(ROW($1:$30)/ROW($1:$30),(ROW($1:$30)/ROW($1:$30))*27)