doing calculations on specific columns of array

Question

I populated a dynamic array using the data from a Table specific only on those rows that have a specific value in a specific column. Until this moment no probs. I'm unable though to do operations referred to one entire column (and only that column).

I thought about something like this

Dim arr as variant
Dim avg as double
Dim i as integer
i = InputBox( "The column I want to calculate the average of")
 avg = Application.Worksheetfunction.Average(arr.column(i))

Is it possible?

Do you have some sample data to go with this? Along with expected output and current result. — JvdV
– JvdV, Commented Nov 29, 2019 at 9:44
I DID IT! the code i used was: avg = application.worksheetfunction.average(application.index(array,0,i)) — Filippo Mhri
– Filippo Mhri, Commented Nov 29, 2019 at 9:56
@FilippoMhri As this is now solved can you either close your question or better yet, write your comment as an answer and accept it? — Tom
– Tom, Commented Nov 29, 2019 at 10:35

piele · Accepted Answer · 2019-11-29 11:27:04Z

1

All you need is to change "column" to "columns"

Solution:

avg = Application.Worksheetfunction.Average(arr.columns(i))

answered Nov 29, 2019 at 11:27

piele

786 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tom Over a year ago

How does this work? I can't get it to work with an array

piele Over a year ago

Are you trying to calculate the average of an array? Simply do: Application.Worksheetfunction.Average(ArrayName)

Tom Over a year ago

that doesn’t work on a non-2D array which is what the question is asking

Filippo Mhri · Accepted Answer · 2019-11-29 12:29:45Z

1

another possibility, worse in this case is the code i used was:

avg = application.worksheetfunction.average(application.index(array,0,i)) –

answered Nov 29, 2019 at 12:29

Filippo Mhri

254 bronze badges

Collectives™ on Stack Overflow

doing calculations on specific columns of array

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related