% Rool-Upon another sheet

Erik Johnson, PE

I have created a construction schedule where i have many lines of of tasks that apply in a mixed format to different subcontractors.  Each task has it own % value.  I have created another sheet where I have listed the Subcontractors and am trying to roll up the %of every line (associated with a specific subcontractor into a single % on this sheet.  I have started off to with using the formula =SUM(COLLECT) and that seems to work, But i need the average not the sum.  I have tried =AVG(COLLECT), but with no luck.  Maybe I am not using the AVG correctly?

Any ideas?

Paul Newcome

What is the actual AVG(COLLECT()) formula you are trying to use?

Erik Johnson, PE

=AVG({Sheet - CF ES Timeline Range 2}COLLECT({Sheet - CF ES Timeline Range 4}, {Sheet - CF ES Timeline Range 1}, Vendors1))

Erik Johnson, PE

This is what my formula looks like and i have no idea if it is right.  Diving into new territory here

=AVG({Sheet - CF ES Timeline Range 2}*countifs({Sheet - CF ES Timeline Range 2})COLLECT({Sheet - CF ES Timeline Range 4}, {Sheet - CF ES Timeline Range 1}, Vendors1))

Paul Newcome

Are you getting an error?

Erik Johnson, PE

it returns #UNPARSEABLE.

It seems to work fine with SUM but not AVG

Paul Newcome

Try using =AVG(COLLECT({Range of numbers to be averaged}, {Criteria Range 1}, Criteria 1, {Criteria Range 2}, Criteria 2))

Erik Johnson, PE

I tried that and got #INCORRECT ARGUMENT SET...

=AVG(COLLECT({Sheet - CF ES Timeline Range 2}, {Sheet - CF ES Timeline Range 3,Vendors1}))

Erik Johnson, PE

Paul I re-did it and got it to work thanks for all your help! 

Paul Newcome

Excellent. Happy to help.