investntrade
New Member
- Joined
- Jul 26, 2017
- Messages
- 11
- Office Version
- 2016
Hi,
I have data in excel which contains alphanumeric values like "NA", blanks and spaces, as well as numbers as percentage:
For example, I have the 17 values in a column which includes 3 percentage numbers, 11 NA and rest blank values:
<colgroup><col></colgroup><tbody>
</tbody>
I want to get the count of only those cells whose values are less than a specific range. In the above example, say I want to count values which are greater than 1%. So I put the 1% value in cell B2, and then use the following formula:
=COUNTIF(K2:K88,">-$B$2")
Unfortunately, the above is returning all counts of NA (i.e. 11), as it seems that NA is being considered as greater than the number 1%.
Expected output should be the count of 2 (for value of 2.37% and 1.37%) in the whole range.
Can the experts please help in getting the right formula?
Thanks
I have data in excel which contains alphanumeric values like "NA", blanks and spaces, as well as numbers as percentage:
For example, I have the 17 values in a column which includes 3 percentage numbers, 11 NA and rest blank values:
2.37% |
NA |
NA |
NA |
NA |
NA |
NA |
1.37% |
NA |
NA |
NA |
-0.49% |
NA |
NA |
<colgroup><col></colgroup><tbody>
</tbody>
I want to get the count of only those cells whose values are less than a specific range. In the above example, say I want to count values which are greater than 1%. So I put the 1% value in cell B2, and then use the following formula:
=COUNTIF(K2:K88,">-$B$2")
Unfortunately, the above is returning all counts of NA (i.e. 11), as it seems that NA is being considered as greater than the number 1%.
Expected output should be the count of 2 (for value of 2.37% and 1.37%) in the whole range.
Can the experts please help in getting the right formula?
Thanks