Excel - COLUMN Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

Hi, I am trying to find an average of one column 'C', where another column = a particular value, or a third column = another particular value.
Solved by T. C. in 19 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 14/08/2018 - 05:20
HI
User 14/08/2018 - 05:20
Hello
Excelchat Expert 14/08/2018 - 05:20
Welcome to Got it Pro
Excelchat Expert 14/08/2018 - 05:21
Please share the data and tell more about the requirement so that i can help you better
Excelchat Expert 14/08/2018 - 05:21
can you put some data on the spreadsheet so that i can help you with the formula
Excelchat Expert 14/08/2018 - 05:21
or do you want me to create some sample data?
User 14/08/2018 - 05:21
sure
User 14/08/2018 - 05:22
The data is confidential so I can't share unfortunately
Excelchat Expert 14/08/2018 - 05:22
Ok
Excelchat Expert 14/08/2018 - 05:22
I will put some data and explain
User 14/08/2018 - 05:23
I have a list of employees, I am trying to find the average age of those employees , where J2:J1193 =*ONA* OR if L2:L1193 = RPN.
User 14/08/2018 - 05:23
does that help?
Excelchat Expert 14/08/2018 - 05:24
You will have to share excel file for that
Excelchat Expert 14/08/2018 - 05:24
or else i have created a sample data on spreadsheet
Excelchat Expert 14/08/2018 - 05:24
Please have a look at that
User 14/08/2018 - 05:25
okay, using the sample data I would need a formula that will average the Marks when Subject =Maths and Name = B
Excelchat Expert 14/08/2018 - 05:25
See formula in J
User 14/08/2018 - 05:25
sorry that should have been Subject =Maths OR Name = B
Excelchat Expert 14/08/2018 - 05:26
See cell J8
User 14/08/2018 - 05:27
I believe the formula in J8 is an AND vs. an OR?
Excelchat Expert 14/08/2018 - 05:27
Excelchat Expert 14/08/2018 - 05:27
when you talk about average you dont say OR
User 14/08/2018 - 05:27
I'm sorry I did correct myself above
Excelchat Expert 14/08/2018 - 05:27
because if you say average of Maths or average of B then it means two different average
Excelchat Expert 14/08/2018 - 05:28
When you say average of B in maths that means AND
Excelchat Expert 14/08/2018 - 05:28
Do you understand?
User 14/08/2018 - 05:28
Okay let me try to explin again
Excelchat Expert 14/08/2018 - 05:28
Yes tell
User 14/08/2018 - 05:30
I would want to know the average mark if the subject is MATH OR if the Name is B (In the context of this sample data I know that would be a strange thing to do, but it makes sense in the context of my data) So for this same it would take the average from lines 4, 8, 12, 16, because they are math and it would also include lines 6, 8, 9 because the name is B
User 14/08/2018 - 05:30
does that make more sense?
Excelchat Expert 14/08/2018 - 05:30
Ok let me see
Excelchat Expert 14/08/2018 - 05:33
I have broken the formula and calculated average based on OR
Excelchat Expert 14/08/2018 - 05:33
See the formula and let me know if that helps
Excelchat Expert 14/08/2018 - 05:34
Please do give good rating and reviews
Excelchat Expert 14/08/2018 - 05:34
It would help a lot
Excelchat Expert 14/08/2018 - 05:35
Let me know if this helps you
Excelchat Expert 14/08/2018 - 05:35
Are you there?
Excelchat Expert 14/08/2018 - 05:35
User 14/08/2018 - 05:35
in the sum formula, did you include the does not = math so that it doesn't double count for rows where it is B and Math?
Excelchat Expert 14/08/2018 - 05:35
No
Excelchat Expert 14/08/2018 - 05:35
I have used <> Maths
Excelchat Expert 14/08/2018 - 05:36
so it excludes that have been previously counted
Excelchat Expert 14/08/2018 - 05:36
you can try to manually sum the rows
Excelchat Expert 14/08/2018 - 05:36
I will color code them
User 14/08/2018 - 05:36
yes, okay, that's what I was asking
Excelchat Expert 14/08/2018 - 05:36
sum of yellow is 234
Excelchat Expert 14/08/2018 - 05:36
Does that help now?
User 14/08/2018 - 05:37
sorry I just need a moment to read and understand the formulas
Excelchat Expert 14/08/2018 - 05:37
Sure
User 14/08/2018 - 05:38
okay, I've got it!
Excelchat Expert 14/08/2018 - 05:38
Great
User 14/08/2018 - 05:38
Thanks so much :)
Excelchat Expert 14/08/2018 - 05:38
Please do give good rating and reviews
Excelchat Expert 14/08/2018 - 05:38
It would mean a lot
User 14/08/2018 - 05:38
sure..where do I do that?
Excelchat Expert 14/08/2018 - 05:38
You can end the session to give your rating and reviews
Excelchat Expert 14/08/2018 - 05:38
When you end the session you will get an option to rate
User 14/08/2018 - 05:39
okay, will do. Thanks again!
Excelchat Expert 14/08/2018 - 05:39
Have a great day
Excelchat Expert 14/08/2018 - 05:39
Take care
User 14/08/2018 - 05:39
You too!

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.