Average based on 1 criteria

MyShell222 · 11/04/22

I'm sure this is user error but I'm trying to average out compensation based on the job code then I'd like to create either a data mesh or vlookup to fill in the blank compensation with these averages. I've tried having a seperate sheet that has the job codes and then I'm trying to use the averageif formula. However I keep getting Incorrect Argument set when i type in =AVERAGEIF({Comp}, {Job Code2}, [Job Code]@row)

Thoughts from anyone on how to do this?

Christian G. · 11/04/22

I think you are usigne the wrong order of argument:

Try this. =AVERAGEIF({Job Code2}, [Job Code]@row,{Comp})

Christian G. · 11/04/22

I think you are usigne the wrong order of argument:

Try this. =AVERAGEIF({Job Code2}, [Job Code]@row,{Comp})

MyShell222 · 11/04/22

@Christian G.that didn't work....all $ are zero

Christian G. · 11/04/22

Let's try another approch;

Collect all the Compasation for the jobcode at row

collect({comp},{job code 2},=[job code]@row)

Then Average it

=AVG(collect({comp},{job code 2},=[job code]@row))

MyShell222 · 11/04/22

@Christian G.This doesn't work, I'm getting an error of #INVALID COLUMN VALUE. I even tried something simple like countif to count how many times the job code is showing up and nothing is returning. The job code is in a format of '000682 to keep all the zeros at the front. Do you think that is making the difference?

Christian G. · 11/04/22

Can you post screenshots of the sheets where the compensation and job code are, and where you try to get the formula?

Average based on 1 criteria

Best Answer

Answers

Help Article Resources

Categories