AVERAGEIF

D Willett
SilverLounger
Posts: 1728
Joined: 25 Jan 2010, 08:34
Location: Stoke on Trent - Staffordshire - England

AVERAGEIF

Post by D Willett »

=Averageif('Salary Calculation INC 1.5'!C3:C52+'Salary Calculation Max 1.25'!C3:C42+'Salary Calculation £20 Capped'!C3:C19+'Admin 45 hrs week'!C3:C19,"<>0")

Excel tells me there's an error in the above formula, I can't see it, unless it is the use of the +

Can anyone see the fault?

(And I've just seen that AVERAGEIF will not work over multiple sheets :groan: )

=SUM('Salary Calculation INC 1.5'!C3:C52,'Salary Calculation Max 1.25'!C3:C42,'Salary Calculation £20 Capped'!C3:C19,'Admin 45 hrs week'!C3:C19)/index(frequency('Salary Calculation INC 1.5'!C3:C52,'Salary Calculation Max 1.25'!C3:C42,'Salary Calculation £20 Capped'!C3:C19,'Admin 45 hrs week'!C3:C19,0),2)

Says too many arguments....
Cheers ...

Dave.

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: AVERAGEIF

Post by StuartR »

The format for Averageif is Range, criteria, average-range. You seem to have the criterion at the end of your formula.
StuartR


D Willett
SilverLounger
Posts: 1728
Joined: 25 Jan 2010, 08:34
Location: Stoke on Trent - Staffordshire - England

Re: AVERAGEIF

Post by D Willett »

Thanks Stuart, this is what I don't understand.
Cheers ...

Dave.

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: AVERAGEIF

Post by StuartR »

Can you explain in words what you are trying to achieve. A common use of Averageif would be something like this.
You do not have the required permissions to view the files attached to this post.
StuartR


User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: AVERAGEIF

Post by HansV »

The range argument can only be a single range, not an expression, so 'Salary Calculation INC 1.5'!C3:C52+'Salary Calculation Max 1.25'!C3:C42+'Salary Calculation £20 Capped'!C3:C19+'Admin 45 hrs week'!C3:C19 is not allowed.
You could use

=(SUM('Salary Calculation INC 1.5'!C3:C52) + SUM('Salary Calculation Max 1.25'!C3:C42) + SUM('Salary Calculation £20 Capped'!C3:C19) + SUM('Admin 45 hrs week'!C3:C19)) / (COUNTIF('Salary Calculation INC 1.5'!C3:C52,"<>0") + COUNTIF('Salary Calculation Max 1.25'!C3:C42,"<>0") + COUNTIF('Salary Calculation £20 Capped'!C3:C19,"<>0") + COUNTIF('Admin 45 hrs week'!C3:C19,"<>0"))
Best wishes,
Hans

D Willett
SilverLounger
Posts: 1728
Joined: 25 Jan 2010, 08:34
Location: Stoke on Trent - Staffordshire - England

Re: AVERAGEIF

Post by D Willett »

The formula was to find the average of a range across Multiple sheets and to ignore zero's Stuart.

Is this what your formula does Hans?

Thanks guys.
Cheers ...

Dave.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: AVERAGEIF

Post by HansV »

It should, Dave, if I haven't messed it up.
Best wishes,
Hans

D Willett
SilverLounger
Posts: 1728
Joined: 25 Jan 2010, 08:34
Location: Stoke on Trent - Staffordshire - England

Re: AVERAGEIF

Post by D Willett »

Thanks Hans, I appreciate the help.
Cheers ...

Dave.