Press "Enter" to skip to content

Posts published in “Office”

Common Used Excel Formulas

0

一、数字处理


1、取绝对值
=ABS(数字)
2、取整
=INT(数字)
3、四舍五入
=ROUND(数字,小数位数)

二、判断公式

1、把公式产生的错误值显示为空
公式:C2
=IFERROR(A2/B2,"")
说明:如果是错误值则显示为空,否则正常显示。

Excel Formula: Search a Column of Strings to Match Another Column

0

Once a while, I have to work on Excel sheet manually. Today I have to search exported hundreds of ip addresses in a pre-defined excel spreadsheet to see if there is a match. It seems a easy work, but it took me almost an hour to find right formula. Column O includes all exported ip addresses. I have to manually search if those column O’s ip addresses are appearing in column B’s text. If yes,…

Excel Formula : Convert a text to Number

0

Got a Excel file from other resource witch Column A is set as text. At the tail and start of number there are some spaces filled in, for example cell A1 is ‘  4 ‘. My challenge is to convert whole column to numbers which can be used to do sum or other math calculation. With some research, I constructed this formula for a new column :=VALUE(TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160),” “)))) it perfectly resolved this challenge as you can…

You cannot copy content from 51sec.org.