Generate avg from column of numbers

gailb
StarLounger
Posts: 94
Joined: 09 May 2020, 14:00

Generate avg from column of numbers

Post by gailb »

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.
Avg Random numbers.xlsx
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 69114
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Generate avg from column of numbers

Post by HansV »

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.
Regards,
Hans

Nabeel
2StarLounger
Posts: 170
Joined: 26 Jan 2017, 07:24

Re: Generate avg from column of numbers

Post by Nabeel »

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
Capture.PNG
Nabeel
You do not have the required permissions to view the files attached to this post.

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

Re: Generate avg from column of numbers

Post by p45cal »

Without Office 365 you can do it with the likes of:

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})))
, 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

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}
So far (I may have brain block) I've come up with:

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)))))
but I feel there has to be a more elegant way.

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

Re: Generate avg from column of numbers

Post by p45cal »

Nabeel wrote:
16 Oct 2020, 08:20
may be>> =AVERAGE(RANDARRAY(1,30,MIN($A$2:$A$28),MAX($A$2:$A$28),FALSE))
Nabeel
Nabeel, this will produce an array of numbers to average, many of which don't exist in cells A2:A28

Perhaps:
=AVERAGE(INDEX($A$2:$A$28,RANDARRAY(1,30,1,27,TRUE)))

gailb
StarLounger
Posts: 94
Joined: 09 May 2020, 14:00

Re: Generate avg from column of numbers

Post by gailb »

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.

User avatar
HansV
Administrator
Posts: 69114
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Generate avg from column of numbers

Post by HansV »

I don't see a way to do this with built-in formulas only. Here is a custom function:

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
Enter the following formula in a cell in row 2:

=AVERAGE(RandomArray($A$2:$A$28,30))

and fill down.
Regards,
Hans

gailb
StarLounger
Posts: 94
Joined: 09 May 2020, 14:00

Re: Generate avg from column of numbers

Post by gailb »

Thank you Hans. This seems to work great.

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

Re: Generate avg from column of numbers

Post by p45cal »

p45cal wrote:
16 Oct 2020, 09:01
you may have to commit to the sheet using Ctrl+Shift+Enter, rather than the more usual Enter, then copy down.
You did try with the above didn't you?

User avatar
HansV
Administrator
Posts: 69114
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Generate avg from column of numbers

Post by HansV »

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

gailb
StarLounger
Posts: 94
Joined: 09 May 2020, 14:00

Re: Generate avg from column of numbers

Post by gailb »

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.

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

Re: Generate avg from column of numbers

Post by p45cal »

HansV wrote:
16 Oct 2020, 13:22
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.
That's a pain because it works here in 365 without CSE. I'll try on an old Excel 2003 machine later.

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

Re: Generate avg from column of numbers

Post by p45cal »

In Excel 2003 I can't even get a RANDBETWEEN with 2 arrays to come up with anything but #VALUE!. :(

User avatar
HansV
Administrator
Posts: 69114
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Generate avg from column of numbers

Post by HansV »

RANDBETWEEN was introduced in Excel 2007.
Regards,
Hans

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

Re: Generate avg from column of numbers

Post by p45cal »

Ahh, right… then I can't test because I have no Excel 2007 anywhere.

gailb
StarLounger
Posts: 94
Joined: 09 May 2020, 14:00

Re: Generate avg from column of numbers

Post by gailb »

Thank you for all your effort p45cal, but Hans' UDF works great.

User avatar
Doc.AElstein
BronzeLounger
Posts: 1201
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Generate avg from column of numbers

Post by Doc.AElstein »

p45cal wrote:
16 Oct 2020, 19:36
Ahh, right… then I can't test because I have no Excel 2007 anywhere.
@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…
p45cal wrote:
16 Oct 2020, 09:01
.....So far (I may have brain block) I've come up with:

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)))))
but I feel there has to be a more elegant way.
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 this
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)) 
As I mentioned, explaining that workaround fully is beyond anyone.
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/tips-and-tut ... ost5408376
_2 https://www.excelforum.com/excel-progra ... ost5410028
_3 https://excelxor.com/2014/09/05/index-r ... mment-8893
_4 https://excelxor.com/2014/09/05/index-r ... of-values/
_5 https://stackoverflow.com/questions/471 ... turn-array
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.
\ -_- / :heavy:

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

Re: Generate avg from column of numbers

Post by p45cal »

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:
Doc.AElstein wrote:
17 Oct 2020, 22:01
It seems pointless trying to figure out what is going on.
and use Hans' udf.
Doc.AElstein wrote:
17 Oct 2020, 22:01
I suppose I would have done it like this
RANDBETWEEN(ROW($1:$30)/ROW($1:$30),(ROW($1:$30)/ROW($1:$30))*27)
Yes that's a fair bit shorter although I would keep the ROWS($A$2:$A$28) instead of the hard-coded 27 to save the user counting the rows involved.