Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Excel/vba average and lists

0

Hello, I need help with this:

1. Display the average of the n largest elements in a list.

Example: if the list {2,5,7,2,3,4} and the value 3 are presented, the function should present the average of the 3 largest values, in this case {7,5,4}

I used choose function and it didn't work, please help me I need this for tomorrow

and this : 

2. Receive two lists, of equal size, with integer values. Also receive a x value to look for. Result of the function: add the value present in the second list where the position corresponds to a position with value equal to x in list 1.

Example: if the input is L1 = {1,2,8,2,1,6}, L2 = {1,3,2,4,2,3} and x = 2, the output will be: 3 + 4. The positions in L1 equal to x are position 2, which in L2 has the value 3 and the position 4, which in L2 has the value 4.

Answer
Discuss

Answers

0

To average the 3 largest values, you can use a formula like this:

=AVERAGE(LARGE(A1:A5,1),LARGE(A1:A5,2),LARGE(A1:A5,3))

This is quick and easy.

The second way to do this might seem confusing but its like this:

=AVERAGE(LARGE(A1:A5,{1,2,3}))

For the second question, it looks like you should use some sort of Vlookup or other Lookup function depending on the specific situation/data-setup. So, ask your teacher that question! :P

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login