Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averaging only populated cells
I'm averaging cells that aren't next to each other, but i don't want the
average to include cells that aren't populated. =AVERAGE(F4:F6,F10,F14,F18,F21) f F4- 5 F6- 5 F10- F 14- 5 AVERAGE = 3.75 for example, its possible that, let say, F10 has no quantitiy, i don't want that to drag my average down. I want the results in the example above to average = 5 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averaging only populated cells
Well, try it out. AVERAGE ignores blank cells, so your result should
not be adversely affected. Pete On Sep 19, 2:08*pm, John wrote: I'm averaging cells that aren't next to each other, but i don't want the average to include cells that aren't populated. =AVERAGE(F4:F6,F10,F14,F18,F21) f F4- 5 F6- 5 F10- F 14- 5 AVERAGE = 3.75 * * for example, its possible that, let say, F10 has no quantitiy, i don't want that to drag my average down. I want the results in the example above to average = 5 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averaging only populated cells
Looks like you are free to drag, per Help on AVERGE.
a.. If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included. HTH Regards, Howard "John" wrote in message ... I'm averaging cells that aren't next to each other, but i don't want the average to include cells that aren't populated. =AVERAGE(F4:F6,F10,F14,F18,F21) f F4- 5 F6- 5 F10- F 14- 5 AVERAGE = 3.75 for example, its possible that, let say, F10 has no quantitiy, i don't want that to drag my average down. I want the results in the example above to average = 5 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averaging only populated cells
The AVERAGE function does not include blank cells. If you want to use the
AVERAGE function and ignore any ZERO's. Try something like this: =AVERAGE(IF(F4<0,IF(F6<0,IF(F10<0,IF(F14<0,F4) ,F6),F10),F14)) "John" wrote in message ... I'm averaging cells that aren't next to each other, but i don't want the average to include cells that aren't populated. =AVERAGE(F4:F6,F10,F14,F18,F21) f F4- 5 F6- 5 F10- F 14- 5 AVERAGE = 3.75 for example, its possible that, let say, F10 has no quantitiy, i don't want that to drag my average down. I want the results in the example above to average = 5 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averaging only populated cells
it is including those cells in the average. it drags my average retail down
whether its blank or 0. "L. Howard Kittle" wrote: Looks like you are free to drag, per Help on AVERGE. a.. If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included. HTH Regards, Howard "John" wrote in message ... I'm averaging cells that aren't next to each other, but i don't want the average to include cells that aren't populated. =AVERAGE(F4:F6,F10,F14,F18,F21) f F4- 5 F6- 5 F10- F 14- 5 AVERAGE = 3.75 for example, its possible that, let say, F10 has no quantitiy, i don't want that to drag my average down. I want the results in the example above to average = 5 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averaging only populated cells
i take that back. your right. i was putting 0 in it. i just need to leave it
blank. t thanks "L. Howard Kittle" wrote: Looks like you are free to drag, per Help on AVERGE. a.. If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included. HTH Regards, Howard "John" wrote in message ... I'm averaging cells that aren't next to each other, but i don't want the average to include cells that aren't populated. =AVERAGE(F4:F6,F10,F14,F18,F21) f F4- 5 F6- 5 F10- F 14- 5 AVERAGE = 3.75 for example, its possible that, let say, F10 has no quantitiy, i don't want that to drag my average down. I want the results in the example above to average = 5 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averaging only populated cells
thanks
"WShelton" wrote: The AVERAGE function does not include blank cells. If you want to use the AVERAGE function and ignore any ZERO's. Try something like this: =AVERAGE(IF(F4<0,IF(F6<0,IF(F10<0,IF(F14<0,F4) ,F6),F10),F14)) "John" wrote in message ... I'm averaging cells that aren't next to each other, but i don't want the average to include cells that aren't populated. =AVERAGE(F4:F6,F10,F14,F18,F21) f F4- 5 F6- 5 F10- F 14- 5 AVERAGE = 3.75 for example, its possible that, let say, F10 has no quantitiy, i don't want that to drag my average down. I want the results in the example above to average = 5 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy to new cells if populated | Excel Discussion (Misc queries) | |||
Cells to be populated from an workbook to another. | Excel Discussion (Misc queries) | |||
Counting populated cells in another worksheet | Excel Worksheet Functions | |||
changing average if cells populated | Excel Worksheet Functions | |||
how do populate empty cells with the contents of populated cells . | Excel Discussion (Misc queries) |