| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need blank if no values in range getting value at the mo'
Hi everyone,
Could anyone help and tell me what's the matter with this please? =if(p1:p4<,"",AVERAGE(IF(P1:P40,P1:P4,FALSE)) Scratching my head - feels like a Friday not a Thursday :-) -- Mifty |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need blank if no values in range getting value at the mo'
Oops sorry should have switched brain on first
I keep handy formulas on a spreadsheet which I dip into and then alter the formula to fit. I just read the note with this one and the original formula =AVERAGE(IF(A1:A50,A1:A5,FALSE)) which averages values above 0 in a range says to enter as an array. When I follow the instructions it works and doesn't need the if bit at the front! Sorry -- Mifty "Mifty" wrote: Hi everyone, Could anyone help and tell me what's the matter with this please? =if(p1:p4<,"",AVERAGE(IF(P1:P40,P1:P4,FALSE)) Scratching my head - feels like a Friday not a Thursday :-) -- Mifty |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need blank if no values in range getting value at the mo'
=if(iserror(average(P1:p4)),"",average(p1:p4))
"Mifty" wrote: Hi everyone, Could anyone help and tell me what's the matter with this please? =if(p1:p4<,"",AVERAGE(IF(P1:P40,P1:P4,FALSE)) Scratching my head - feels like a Friday not a Thursday :-) -- Mifty |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need blank if no values in range getting value at the mo'
Hi Duke,
Can I add iserror to an array formula? Still having problems! When I use the original formula as an array and there is no data I get a #DIV/0 error. The formula you gave doesn't have the 0 bit, is that why it has to be entered as an array? -- Mifty "Duke Carey" wrote: =if(iserror(average(P1:p4)),"",average(p1:p4)) "Mifty" wrote: Hi everyone, Could anyone help and tell me what's the matter with this please? =if(p1:p4<,"",AVERAGE(IF(P1:P40,P1:P4,FALSE)) Scratching my head - feels like a Friday not a Thursday :-) -- Mifty |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need blank if no values in range getting value at the mo'
Yes, the 0 test requires the array, and yes, you can add the iserror() to an
array formula. The 0 test will exclude zero VALUES and negative values from the average, but if you're only worried about BLANK cells, then my non-array formula will work. "Mifty" wrote: Hi Duke, Can I add iserror to an array formula? Still having problems! When I use the original formula as an array and there is no data I get a #DIV/0 error. The formula you gave doesn't have the 0 bit, is that why it has to be entered as an array? -- Mifty "Duke Carey" wrote: =if(iserror(average(P1:p4)),"",average(p1:p4)) "Mifty" wrote: Hi everyone, Could anyone help and tell me what's the matter with this please? =if(p1:p4<,"",AVERAGE(IF(P1:P40,P1:P4,FALSE)) Scratching my head - feels like a Friday not a Thursday :-) -- Mifty |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need blank if no values in range getting value at the mo'
Hi Duke,
I want cells with zero in to be ignored so I do need it to be an array. I've been trying in vain to add the iserror bit but get message back re ( ) - then when I add enough of these I get a message saying there is an error in the formula. Here's one of my tries =IF(ISERRORAVERAGE(IF(P1:P40,P1:P4)),"",AVERAGE(I F(P1:P40,P1:P4,FALSE)) Help! -- Mifty "Duke Carey" wrote: Yes, the 0 test requires the array, and yes, you can add the iserror() to an array formula. The 0 test will exclude zero VALUES and negative values from the average, but if you're only worried about BLANK cells, then my non-array formula will work. "Mifty" wrote: Hi Duke, Can I add iserror to an array formula? Still having problems! When I use the original formula as an array and there is no data I get a #DIV/0 error. The formula you gave doesn't have the 0 bit, is that why it has to be entered as an array? -- Mifty "Duke Carey" wrote: =if(iserror(average(P1:p4)),"",average(p1:p4)) "Mifty" wrote: Hi everyone, Could anyone help and tell me what's the matter with this please? =if(p1:p4<,"",AVERAGE(IF(P1:P40,P1:P4,FALSE)) Scratching my head - feels like a Friday not a Thursday :-) -- Mifty |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need blank if no values in range getting value at the mo'
If you're just trying to stop the division by 0 error:
=if(count(p1:p4)=0,"",average(p1:p4)) Mifty wrote: Hi everyone, Could anyone help and tell me what's the matter with this please? =if(p1:p4<,"",AVERAGE(IF(P1:P40,P1:P4,FALSE)) Scratching my head - feels like a Friday not a Thursday :-) -- Mifty -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need blank if no values in range getting value at the mo'
try
=IF(ISERROR(AVERAGE(IF(P1:P40,P1:P4))),"",AVERAGE (IF(P1:P40,P1:P4))) "Mifty" wrote: Hi Duke, I want cells with zero in to be ignored so I do need it to be an array. I've been trying in vain to add the iserror bit but get message back re ( ) - then when I add enough of these I get a message saying there is an error in the formula. Here's one of my tries =IF(ISERRORAVERAGE(IF(P1:P40,P1:P4)),"",AVERAGE(I F(P1:P40,P1:P4,FALSE)) Help! -- Mifty "Duke Carey" wrote: Yes, the 0 test requires the array, and yes, you can add the iserror() to an array formula. The 0 test will exclude zero VALUES and negative values from the average, but if you're only worried about BLANK cells, then my non-array formula will work. "Mifty" wrote: Hi Duke, Can I add iserror to an array formula? Still having problems! When I use the original formula as an array and there is no data I get a #DIV/0 error. The formula you gave doesn't have the 0 bit, is that why it has to be entered as an array? -- Mifty "Duke Carey" wrote: =if(iserror(average(P1:p4)),"",average(p1:p4)) "Mifty" wrote: Hi everyone, Could anyone help and tell me what's the matter with this please? =if(p1:p4<,"",AVERAGE(IF(P1:P40,P1:P4,FALSE)) Scratching my head - feels like a Friday not a Thursday :-) -- Mifty |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need blank if no values in range getting value at the mo'
FANTASTIC !
Thank you so much Duke :-) -- Mifty "Duke Carey" wrote: try =IF(ISERROR(AVERAGE(IF(P1:P40,P1:P4))),"",AVERAGE (IF(P1:P40,P1:P4))) "Mifty" wrote: Hi Duke, I want cells with zero in to be ignored so I do need it to be an array. I've been trying in vain to add the iserror bit but get message back re ( ) - then when I add enough of these I get a message saying there is an error in the formula. Here's one of my tries =IF(ISERRORAVERAGE(IF(P1:P40,P1:P4)),"",AVERAGE(I F(P1:P40,P1:P4,FALSE)) Help! -- Mifty "Duke Carey" wrote: Yes, the 0 test requires the array, and yes, you can add the iserror() to an array formula. The 0 test will exclude zero VALUES and negative values from the average, but if you're only worried about BLANK cells, then my non-array formula will work. "Mifty" wrote: Hi Duke, Can I add iserror to an array formula? Still having problems! When I use the original formula as an array and there is no data I get a #DIV/0 error. The formula you gave doesn't have the 0 bit, is that why it has to be entered as an array? -- Mifty "Duke Carey" wrote: =if(iserror(average(P1:p4)),"",average(p1:p4)) "Mifty" wrote: Hi everyone, Could anyone help and tell me what's the matter with this please? =if(p1:p4<,"",AVERAGE(IF(P1:P40,P1:P4,FALSE)) Scratching my head - feels like a Friday not a Thursday :-) -- Mifty |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need blank if no values in range getting value at the mo'
Hi Dave,
Thanks for replying. Duke's last post sorted the problem when I remembered (again) to add as an array! Thanks -- Mifty "Dave Peterson" wrote: If you're just trying to stop the division by 0 error: =if(count(p1:p4)=0,"",average(p1:p4)) Mifty wrote: Hi everyone, Could anyone help and tell me what's the matter with this please? =if(p1:p4<,"",AVERAGE(IF(P1:P40,P1:P4,FALSE)) Scratching my head - feels like a Friday not a Thursday :-) -- Mifty -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count Non-Blank Values in a Table but only for a range | Excel Worksheet Functions | |||
| Sum of unique values within a range with blank cells | Excel Worksheet Functions | |||
| Average Formula to display blank cell if named range is blank | Excel Worksheet Functions | |||
| If certain cells not blank, and cells in range are, set values to | Excel Discussion (Misc queries) | |||
| Not showing blank and non blank items in filter mode for values | Excel Worksheet Functions | |||
Linear Mode
