Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula
I have a spreadsheet where, cell U6 is an average of Z6-AE6. i also need U6
to =0 if any cell within Z6 - AE6 is a 0. Can anyone help please |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula
I have a spreadsheet where, cell U6 is an average of Z6-AE6. i also
need U6 to =0 if any cell within Z6 - AE6 is a 0. ... One way: =IF(PRODUCT(Z6:AE6)=0,0,AVERAGE(Z6:AE6)) Caution: an empty cell doesn't count as zero with this formula. Neither does text. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula
Thank you that is brilliant.
"MyVeryOwnSelf" wrote: I have a spreadsheet where, cell U6 is an average of Z6-AE6. i also need U6 to =0 if any cell within Z6 - AE6 is a 0. ... One way: =IF(PRODUCT(Z6:AE6)=0,0,AVERAGE(Z6:AE6)) Caution: an empty cell doesn't count as zero with this formula. Neither does text. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula
Try =IF(COUNTIF(Z6:AE6,"0")0,0,AVERAGE(Z6:AE6))
"IF the number of 0s in the range Z6:AE6 is greater than 0, THEN 0, ELSE take the average of the range Z6:AE6." Dave -- Brevity is the soul of wit. "kano71" wrote: I have a spreadsheet where, cell U6 is an average of Z6-AE6. i also need U6 to =0 if any cell within Z6 - AE6 is a 0. Can anyone help please |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula
MyVeryOwnSelf
One way: =IF(PRODUCT(Z6:AE6)=0,0,AVERAGE(Z6:AE6)) Won't that return zero if there is a zero anywhere in Z6:AE6? Perhaps: =IF(SUM(Z6:AE6)=0,0,AVERAGE(Z6:AE6)) or =IF(SUM(Z6:AE6),AVERAGE(Z6:AE6),0) -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "MyVeryOwnSelf" wrote in message ... I have a spreadsheet where, cell U6 is an average of Z6-AE6. i also need U6 to =0 if any cell within Z6 - AE6 is a 0. ... One way: =IF(PRODUCT(Z6:AE6)=0,0,AVERAGE(Z6:AE6)) Caution: an empty cell doesn't count as zero with this formula. Neither does text. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula
Yes it would return 0 if a 0 is in that range.
But that's what the original poster asked for. Dave -- Brevity is the soul of wit. "Sandy Mann" wrote: MyVeryOwnSelf One way: =IF(PRODUCT(Z6:AE6)=0,0,AVERAGE(Z6:AE6)) Won't that return zero if there is a zero anywhere in Z6:AE6? Perhaps: =IF(SUM(Z6:AE6)=0,0,AVERAGE(Z6:AE6)) or =IF(SUM(Z6:AE6),AVERAGE(Z6:AE6),0) -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "MyVeryOwnSelf" wrote in message ... I have a spreadsheet where, cell U6 is an average of Z6-AE6. i also need U6 to =0 if any cell within Z6 - AE6 is a 0. ... One way: =IF(PRODUCT(Z6:AE6)=0,0,AVERAGE(Z6:AE6)) Caution: an empty cell doesn't count as zero with this formula. Neither does text. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reusing formula | Excel Discussion (Misc queries) | |||
2 Nesting questions | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions |