=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 )
=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....
AVERAGEIF
-
- Administrator
- Posts: 12619
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: AVERAGEIF
The format for Averageif is Range, criteria, average-range. You seem to have the criterion at the end of your formula.
StuartR
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
-
- Administrator
- Posts: 12619
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: AVERAGEIF
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
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: AVERAGEIF
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"))
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
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: AVERAGEIF
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.
Is this what your formula does Hans?
Thanks guys.
Cheers ...
Dave.
Dave.
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England