Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Difficult Average Calculation <HELP
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Difficult Average Calculation <HELP
Try this formula
=SUMPRODUCT(A1:A8)/SUMPRODUCT((A1:A80)*(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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Difficult Average Calculation <HELP
Thanks for the reply. But I'm not sure this will work.
The cells that I need to average while ignoring zeros a (G12:G15,G18:G21,G24:G28,G31:G34,G37:G41,G44:G47,G 50: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:A80)*(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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Difficult Average Calculation <HELP
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:G600)*(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 a (G12:G15,G18:G21,G24:G28,G31:G34,G37:G41,G44:G47,G 50: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:A80)*(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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Difficult Average Calculation <HELP
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:G600)*(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 a (G12:G15,G18:G21,G24:G28,G31:G34,G37:G41,G44:G47,G 50: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:A80)*(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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula result #value! needs to equal zero for average calculation | Excel Worksheet Functions | |||
Calculation help from average total | Excel Worksheet Functions | |||
calculation a average dependated of values in seperate column | Excel Worksheet Functions | |||
A difficult conditonal calculation | Excel Worksheet Functions | |||
Difficult (for me) formula/UDF calculation | Excel Worksheet Functions |