Online PDF to Word
Showing posts with label Office. Show all posts
Showing posts with label Office. Show all posts
Friday, February 1, 2019
Saturday, December 31, 2016
Common Used Excel Formulas in Work
一、数字处理
1、取绝对值
=ABS(数字)
2、取整
=INT(数字)
3、四舍五入
=ROUND(数字,小数位数)
二、判断公式
1、把公式产生的错误值显示为空
公式:C2
=IFERROR(A2/B2,"")
说明:如果是错误值则显示为空,否则正常显示。

Thursday, February 26, 2015
Excel Formula: Search a Column of Strings to Match Another Column
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, which row is it?
Formula is set at Column N.
=MATCH("*"&(O6)&"*",B:B,0)
As you can see from cell N6, the number is 7, which means the text in B7 includes O6's string.
N12's number is 5, which means the test in B5 includes O12's string.
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, which row is it?
Formula is set at Column N.
=MATCH("*"&(O6)&"*",B:B,0)
As you can see from cell N6, the number is 7, which means the text in B7 includes O6's string.
N12's number is 5, which means the test in B5 includes O12's string.
Thursday, February 27, 2014
Excel Formula : Convert a text to Number
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 see from following screen shot.
Note: trim will not work with the only space cell. Thanks reply from Hari Krishna.
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 see from following screen shot.
Note: trim will not work with the only space cell. Thanks reply from Hari Krishna.
Subscribe to:
Posts (Atom)