Challenge Accepted?! IF,VLOOKUP,AVERAGEIF,.....

[ZueriBoca]

Hey people,
first of all thanks a lot - you're saving my butt today!
Here, I got stuck. I have a file with with two sheets. First, sheet is a list of all swiss communities (Column A) and their distinctive community number (B). Column C denotes the electricity provider that operates in that city. Often there is only one provider for a city but sometimes there are multiple. Then the city is named in two (or more) rows. What I am trying for quiete a while now is to calculate the city's average electricity price (if there are more than one provider) or only put the electricity price of the monopolist. The prices are in the second sheet. There Column A denotes all electricity providers. Column D denotes the price for private consumers.

So in sheet one I want to put the community average (or monopolist price). My path was to do that with the IF formula. Therefore, I so far got in D5 (sheet 1):

=IF(B5<>$B:$B;VLOOKUP(E5;'Tariferhebung ElCom 2013'!1:65536;'Tariferhebung ElCom 2013'!D:D;FALSE);

What I do not know is what to put in the case the IF is false (so that there are more than one providers and take the average).

Hopefully, someone can give me a hint!

PS: I cut the excel already so extremely down but it was still 1.6MB that is why I had to zip it - sry for that!

[Ron Coderre]

I can't quite figure out what the references are supposed to be. Your formula references Col_E on the "Gemeinden und Strombetreiber" tab, which is blank.
However, I think you might be able to use a combination of SUMIF and COUNTIF, or maybe even AVERAGEIF, but we need a working example with expected results.
Can you post a small sample that exhibits what you want to calculate?