Need a formula for finding average only if greater than 0

[Julesdude]

Hello all,

I was wondering if there was a particular command to do the following:
I have a master workbook which links to 12 individual workbooks by month.
Is there a formular which allows me to find the mean average of the totals for the specified cells in each workbook, but if an amount in one workbook is 0 then it will discard it.

For example:
12 workbooks for each month
11 have a value in referenced cell that is greater than 0
Divide SUM of those 12 workbooks by 11
= the average

Is there a formula that will do this?

 

[TiaXL]

Hello all,

I was wondering if there was a particular command to do the following:
I have a master workbook which links to 12 individual workbooks by month.
Is there a formular which allows me to find the mean average of the totals for the specified cells in each workbook, but if an amount in one workbook is 0 then it will discard it.

For example:
12 workbooks for each month
11 have a value in referenced cell that is greater than 0
Divide SUM of those 12 workbooks by 11
= the average

Is there a formula that will do this?

=IF(F15>0,AVERAGE(A:A),"")

Change the cell references as required

 

[Julesdude]

Hi TiaXL, thanks for that. Is there perhaps a more refined way of writing that formula? I ask because to check that the cells are greater than 0 I would need to reference 12 sheets for the IF statement and then 12 sheets again for the AVERAGE statement.

 

[TiaXL]

Hi TiaXL, thanks for that. Is there perhaps a more refined way of writing that formula? I ask because to check that the cells are greater than 0 I would need to reference 12 sheets for the IF statement and then 12 sheets again for the AVERAGE statement.

do it with VBA

open each workbook. check value of the cell and use an IF statement to define what you want to do if the value is true/false

 

[GlennUK]

Hi TiaXL, thanks for that. Is there perhaps a more refined way of writing that formula? I ask because to check that the cells are greater than 0 I would need to reference 12 sheets for the IF statement and then 12 sheets again for the AVERAGE statement.

Can I ask whether you mean workbooks or sheets within a single workbook? You seem to be using both terms.

 

[TiaXL]

workbooks, you said you had 12 individual workbooks.

do workbooks.open for each workbook

select sheet in each workbooks

check reference cell as above.

use IF statement to determine what to do and then use formula above to get the figure you want and put it where you want it to go

for example

sub workbooks()
 
 
workbooks.open("Jan.xls")
Sheets("Sheet1").select
range("f10").select
 
if activecell.value >0 then
  range("F18").SELECT
  ACTIVECELL.FORMULA = "=AVERAGE(A:A)"
  workbooks.("Main").Sheets("Sheet1").range("AB1").VALUE = ACTIVECELL.VALUE
end if
 
next workbook

You can do it like this or loop through each sheet and do it that way (which is better as less code)

 

[Julesdude]

Sorry Glenn, yes I meant workbooks - I need to reference a sheet in each of the 12 workbooks.

I was hoping to avoid VBA code and have a macro free master sheet and just have a formula with the 12 links to monthly workbooks. Guess it is a bit too complex for that though. Thing is it would be just as complex for the VBA code too as other cells also have to draw on 12 workbooks. We're actually talking about over 100 cells which reference these and then try to draw an average.

 

[GlennUK]

Sorry Glenn, yes I meant workbooks - I need to reference a sheet in each of the 12 workbooks.

I was hoping to avoid VBA code and have a macro free master sheet and just have a formula with the 12 links to monthly workbooks. Guess it is a bit too complex for that though. Thing is it would be just as complex for the VBA code too as other cells also have to draw on 12 workbooks. We're actually talking about over 100 cells which reference these and then try to draw an average.

Bring your required data into ranges by doing single cell links, and then do formulas on the linked data, rather than trying to do formulas with embedded links in.