| Home |
| Search |
| Today's Posts |
|
#9
|
|||
|
|||
PERFECT! Thank you SO MUCH!
"Peo Sjoblom" wrote: OK, got you =IF(COUNTIF(H3:H6,0)=COUNT(H3:H6),0,AVERAGE(IF(H3: H6<0,H3:H6))) entered with ctrl + shift & enter will work if all cells empty and all if cells are 0 and will disregard zeros in the average Regards, Peo Sjoblom "Teri" wrote: I entered the formula per your instructions. It worked fine, however, if I replaced the 31 with a zero, I got the #DIV/0 error again. "Peo Sjoblom" wrote: It doesn't matter, my formula will disregard the zeros and as an extra precaution it will not return an error if all cells are empty Regards, Peo Sjoblom "Teri" wrote: Actually, no. The range isn't empty. Currently it has the value of 31, 0, 0, and 0. Sorry I didn't mention that before. "Peo Sjoblom" wrote: I am assuming you get these errors because the range is empty, you can use =IF(COUNTBLANK(H3:H6)=ROWS(H3:H6),0,AVERAGE(IF(H3: H6<0,H3:H6))) entered with ctrl + shift & enter Regards, Peo Sjoblom "Teri" wrote: Here is my array formula. I would like it to return a ZERO instead of #DIV/0. Have spent way too much time trying to make it work and thought some kind soul out there would be willing to help me: {=AVERAGE(IF(H3:H6<0, H3:H6,""))} Thanks in advance! |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula to return tomorrow's date. | New Users to Excel | |||
| Array Formula | Excel Worksheet Functions | |||
| Need help troubleshooting an array formula XLXP on Win2K | Excel Worksheet Functions | |||
| What instead of an array formula part 2 | Excel Discussion (Misc queries) | |||
| What instead of an array formula? | Excel Discussion (Misc queries) | |||
Threaded Mode
