Q. How to Count only Positive values in a given range
Combine Text in Cells
Here’s another little-known feature available in Excel that I bet you’ll find various uses for. It is the ability to combine text from multiple cells into one cell.
I use it frequently for joining text when I receive a file that has first and last names in separate columns. This feature allows me to easily combine the last name from one column with the first name of the other column and show the result as a full name in another column.
We have first name in column B and last name in column C
- Count only Positive values in a given range =countif(Data-Range,”>0″)
- This trick is useful when you need to calculate average of a bunch of numbers but need to exclude zeros and negative values =sum(Data-Range)/countif(Data-Range,">0")
- When you need to calculate average of numbers but need to exclude zeros only
Combine Text in Cells
Here’s another little-known feature available in Excel that I bet you’ll find various uses for. It is the ability to combine text from multiple cells into one cell.
I use it frequently for joining text when I receive a file that has first and last names in separate columns. This feature allows me to easily combine the last name from one column with the first name of the other column and show the result as a full name in another column.
We have first name in column B and last name in column C
- In cell D1 enter "Full Name" as your column heading.
- In cell D2 enter either of the following formulas:
- =B2 & " " & C2 (will give you "FirstName LastName")
- =C2 & ", " & B2 (will result in "LastName, FirstName")
Tag :
MicroSoft Office
