Average based on 1 criteria

MyShell222

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.

I think you are usigne the wrong order of argument:

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

Christian G.

I think you are usigne the wrong order of argument:

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

MyShell222

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

Christian G.

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

@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.

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