Difficult Average Calculation <HELP>

[JK]

This is what I'm trying to do. Hopefully simple to you, but tough for me....
Calculate averages while skipping certain rows and all zero values...

(Example)

MILEAGE TOTAL
A1 100
A2 200
A3 300
A4 TOTAL: 600 (ignore this row)
A5 100
A6 0
A7 200
A8 TOTAL: 300 (ignore this row)
etc.. (down)

All of these cells are linked to another workbook. For some reason it
inserts a zero by default (even if the cells in the other workbook are
blank). The goal is to calculate the averge miles traveled. I'm trying to
have the function ignore all of the zero values. I've tried two suggested
formulas, but it looks like I have too many arguments?

=Average(If(G12:G15=0,"",G12:G15))
=(SUM(G12:G15))/(COUNTIF(G12:G15, "<>0"))

Any help would be much appreciated...

Thx!
Jason K.

[Sloth]

Try this formula

=SUMPRODUCT(A1:A8)/SUMPRODUCT((A1:A8>0)*(ISNUMBER(A1:A8)))


Another option would be to put a condition in all cells like this

=IF(Sheet1!A1=0,"",Sheet1!A1)

that way they don't show as zeros, and will be ignored by the AVERAGE
function.


"JK" wrote:

> This is what I'm trying to do. Hopefully simple to you, but tough for me....
> Calculate averages while skipping certain rows and all zero values...
>
> (Example)
>
> MILEAGE TOTAL
> A1 100
> A2 200
> A3 300
> A4 TOTAL: 600 (ignore this row)
> A5 100
> A6 0
> A7 200
> A8 TOTAL: 300 (ignore this row)
> etc.. (down)
>
> All of these cells are linked to another workbook. For some reason it
> inserts a zero by default (even if the cells in the other workbook are
> blank). The goal is to calculate the averge miles traveled. I'm trying to
> have the function ignore all of the zero values. I've tried two suggested
> formulas, but it looks like I have too many arguments?
>
> =Average(If(G12:G15=0,"",G12:G15))
> =(SUM(G12:G15))/(COUNTIF(G12:G15, "<>0"))
>
> Any help would be much appreciated...
>
> Thx!
> Jason K.

[JK]

Thanks for the reply. But I'm not sure this will work.

The cells that I need to average while ignoring zeros are:
(G12:G15,G18:G21,G24:G28,G31:G34,G37:G41,G44:G47,G50:G53,G56:G60)

And this is what I have in each cell: ='[ACTIVITY SUMMARY
REPORT_DATA.xls]ASR'!$G$276

All of the cells that I'm trying to average point to another workbook so I
can't put (=IF(Sheet1!A1=0,"",Sheet1!A1) condition in each cell. Right?

Am I missing something or is something else I can try?

=====================================================
"Sloth" wrote:

> Try this formula
>
> =SUMPRODUCT(A1:A8)/SUMPRODUCT((A1:A8>0)*(ISNUMBER(A1:A8)))
>
>
> Another option would be to put a condition in all cells like this
>
> =IF(Sheet1!A1=0,"",Sheet1!A1)
>
> that way they don't show as zeros, and will be ignored by the AVERAGE
> function.
>
>
> "JK" wrote:
>
> > This is what I'm trying to do. Hopefully simple to you, but tough for me....
> > Calculate averages while skipping certain rows and all zero values...
> >
> > (Example)
> >
> > MILEAGE TOTAL
> > A1 100
> > A2 200
> > A3 300
> > A4 TOTAL: 600 (ignore this row)
> > A5 100
> > A6 0
> > A7 200
> > A8 TOTAL: 300 (ignore this row)
> > etc.. (down)
> >
> > All of these cells are linked to another workbook. For some reason it
> > inserts a zero by default (even if the cells in the other workbook are
> > blank). The goal is to calculate the averge miles traveled. I'm trying to
> > have the function ignore all of the zero values. I've tried two suggested
> > formulas, but it looks like I have too many arguments?
> >
> > =Average(If(G12:G15=0,"",G12:G15))
> > =(SUM(G12:G15))/(COUNTIF(G12:G15, "<>0"))
> >
> > Any help would be much appreciated...
> >
> > Thx!
> > Jason K.

[Sloth]

Both methods I suggested should work, but you have to change the references
(keep in mind they are two seperate methods). I just went with the example
you gave.

For the first method, try this..
=SUMPRODUCT(G12:G60)/SUMPRODUCT((G12:G60>0)*(ISNUMBER(G12:G60)))

This will ignore any cell with text (you do have the word "Total" in cells
you want to ignore right?), and it will ignore all zeros.


And for the second method you could try this, but it might get cumbersome
for so many cells.

=IF('[ACTIVITY SUMMARY REPORT_DATA.xls]ASR'!$G$276=0,"",'[ACTIVITY SUMMARY
REPORT_DATA.xls]ASR'!$G$276)

"JK" wrote:

> Thanks for the reply. But I'm not sure this will work.
>
> The cells that I need to average while ignoring zeros are:
> (G12:G15,G18:G21,G24:G28,G31:G34,G37:G41,G44:G47,G50:G53,G56:G60)
>
> And this is what I have in each cell: ='[ACTIVITY SUMMARY
> REPORT_DATA.xls]ASR'!$G$276
>
> All of the cells that I'm trying to average point to another workbook so I
> can't put (=IF(Sheet1!A1=0,"",Sheet1!A1) condition in each cell. Right?
>
> Am I missing something or is something else I can try?
>
> =====================================================
> "Sloth" wrote:
>
> > Try this formula
> >
> > =SUMPRODUCT(A1:A8)/SUMPRODUCT((A1:A8>0)*(ISNUMBER(A1:A8)))
> >
> >
> > Another option would be to put a condition in all cells like this
> >
> > =IF(Sheet1!A1=0,"",Sheet1!A1)
> >
> > that way they don't show as zeros, and will be ignored by the AVERAGE
> > function.
> >
> >
> > "JK" wrote:
> >
> > > This is what I'm trying to do. Hopefully simple to you, but tough for me....
> > > Calculate averages while skipping certain rows and all zero values...
> > >
> > > (Example)
> > >
> > > MILEAGE TOTAL
> > > A1 100
> > > A2 200
> > > A3 300
> > > A4 TOTAL: 600 (ignore this row)
> > > A5 100
> > > A6 0
> > > A7 200
> > > A8 TOTAL: 300 (ignore this row)
> > > etc.. (down)
> > >
> > > All of these cells are linked to another workbook. For some reason it
> > > inserts a zero by default (even if the cells in the other workbook are
> > > blank). The goal is to calculate the averge miles traveled. I'm trying to
> > > have the function ignore all of the zero values. I've tried two suggested
> > > formulas, but it looks like I have too many arguments?
> > >
> > > =Average(If(G12:G15=0,"",G12:G15))
> > > =(SUM(G12:G15))/(COUNTIF(G12:G15, "<>0"))
> > >
> > > Any help would be much appreciated...
> > >
> > > Thx!
> > > Jason K.

[JK]

No, the word 'totals' is not in the total cells. Just the number amount. The
cells point to a sum cell in another workbook.

I used your suggestion though, I just did it in sections. I pointed each
section (month) to a cell at the bottom of my worksheet and then averaged all
of the sections (months) after that. Not as clean as I'd like it to be but it
does the trick. If need be, I could always hide the entire section.

Thanks for your help.

"Sloth" wrote:

> Both methods I suggested should work, but you have to change the references
> (keep in mind they are two seperate methods). I just went with the example
> you gave.
>
> For the first method, try this..
> =SUMPRODUCT(G12:G60)/SUMPRODUCT((G12:G60>0)*(ISNUMBER(G12:G60)))
>
> This will ignore any cell with text (you do have the word "Total" in cells
> you want to ignore right?), and it will ignore all zeros.
>
>
> And for the second method you could try this, but it might get cumbersome
> for so many cells.
>
> =IF('[ACTIVITY SUMMARY REPORT_DATA.xls]ASR'!$G$276=0,"",'[ACTIVITY SUMMARY
> REPORT_DATA.xls]ASR'!$G$276)
>
> "JK" wrote:
>
> > Thanks for the reply. But I'm not sure this will work.
> >
> > The cells that I need to average while ignoring zeros are:
> > (G12:G15,G18:G21,G24:G28,G31:G34,G37:G41,G44:G47,G50:G53,G56:G60)
> >
> > And this is what I have in each cell: ='[ACTIVITY SUMMARY
> > REPORT_DATA.xls]ASR'!$G$276
> >
> > All of the cells that I'm trying to average point to another workbook so I
> > can't put (=IF(Sheet1!A1=0,"",Sheet1!A1) condition in each cell. Right?
> >
> > Am I missing something or is something else I can try?
> >
> > =====================================================
> > "Sloth" wrote:
> >
> > > Try this formula
> > >
> > > =SUMPRODUCT(A1:A8)/SUMPRODUCT((A1:A8>0)*(ISNUMBER(A1:A8)))
> > >
> > >
> > > Another option would be to put a condition in all cells like this
> > >
> > > =IF(Sheet1!A1=0,"",Sheet1!A1)
> > >
> > > that way they don't show as zeros, and will be ignored by the AVERAGE
> > > function.
> > >
> > >
> > > "JK" wrote:
> > >
> > > > This is what I'm trying to do. Hopefully simple to you, but tough for me....
> > > > Calculate averages while skipping certain rows and all zero values...
> > > >
> > > > (Example)
> > > >
> > > > MILEAGE TOTAL
> > > > A1 100
> > > > A2 200
> > > > A3 300
> > > > A4 TOTAL: 600 (ignore this row)
> > > > A5 100
> > > > A6 0
> > > > A7 200
> > > > A8 TOTAL: 300 (ignore this row)
> > > > etc.. (down)
> > > >
> > > > All of these cells are linked to another workbook. For some reason it
> > > > inserts a zero by default (even if the cells in the other workbook are
> > > > blank). The goal is to calculate the averge miles traveled. I'm trying to
> > > > have the function ignore all of the zero values. I've tried two suggested
> > > > formulas, but it looks like I have too many arguments?
> > > >
> > > > =Average(If(G12:G15=0,"",G12:G15))
> > > > =(SUM(G12:G15))/(COUNTIF(G12:G15, "<>0"))
> > > >
> > > > Any help would be much appreciated...
> > > >
> > > > Thx!
> > > > Jason K.