Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
If all cells in a range are N/A then ...
I have a formula that counts all the cells in a range that have "N" and
divides them by the number in cell N/10. =COUNTIF(N7:N9,"N")/N10 However, sometimes all the cells in a range are N/A. I want a formula that returns an "N/A" if all the cells in the range are N/A. and only performs the above formula when all the cells are not N/A. I hope that's clear. Any help is greatly appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
If all cells in a range are N/A then ...
=IF(COUNTIF(N7:N9,"#N/A")=COUNTA(N7:N9)+COUNTBLANK(N7:N9),NA(),COUNTIF(N 7:N9,"N")/N10)
-- --- HTH Bob (change the xxxx to gmail if mailing direct) "Anastasio Jimenez" wrote in message ... I have a formula that counts all the cells in a range that have "N" and divides them by the number in cell N/10. =COUNTIF(N7:N9,"N")/N10 However, sometimes all the cells in a range are N/A. I want a formula that returns an "N/A" if all the cells in the range are N/A. and only performs the above formula when all the cells are not N/A. I hope that's clear. Any help is greatly appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
If all cells in a range are N/A then ...
=IF(COUNTIF(N7:N9,"#N/A")=3,"#N/A",COUNTIF(N7:N9,"N")/N10)
"Anastasio Jimenez" wrote: I have a formula that counts all the cells in a range that have "N" and divides them by the number in cell N/10. =COUNTIF(N7:N9,"N")/N10 However, sometimes all the cells in a range are N/A. I want a formula that returns an "N/A" if all the cells in the range are N/A. and only performs the above formula when all the cells are not N/A. I hope that's clear. Any help is greatly appreciated. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
If all cells in a range are N/A then ...
Thanks guys, that did the trick!
"Teethless mama" wrote: =IF(COUNTIF(N7:N9,"#N/A")=3,"#N/A",COUNTIF(N7:N9,"N")/N10) "Anastasio Jimenez" wrote: I have a formula that counts all the cells in a range that have "N" and divides them by the number in cell N/10. =COUNTIF(N7:N9,"N")/N10 However, sometimes all the cells in a range are N/A. I want a formula that returns an "N/A" if all the cells in the range are N/A. and only performs the above formula when all the cells are not N/A. I hope that's clear. Any help is greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
multiply range of cells by a fluctuating exchange rate | Excel Discussion (Misc queries) | |||
Macro to hide blank cells in a range | Excel Discussion (Misc queries) | |||
Display first, second, etc Nonblank Cells in a Range | Excel Worksheet Functions | |||
Counting empty cells within a range of cells | New Users to Excel | |||
Counting empty cells within a range of cells | New Users to Excel |