Monday, December 31, 2012

ROUNDDOWN formula

=ROUNDDOWN(A2,1)
The excel formula above rounds the value in cell A2 down by 1 decimal place. Let's say the value in A2 is 1.27. Well the value that will be displayed as a result of the rounddown formula is 1.2. The comma followed by the number one in the formula above tells excel to round the value in A2 down by 1 decimal place.
Read More..

Saturday, December 29, 2012

Power formula

=POWER(2,3)
The excel formula above is stating the same as 2 to the power of 3. Or =2^3. It basically takes the 2 and multiplies it by itself 3 times. So it would be the same as 2 x 2 x 2, which is 8.
Read More..

Sunday, December 9, 2012

ROUNDUP formula

=ROUNDUP(A2,1)
The excel formula above rounds the value in cell A2 up by 1 decimal place. Let's say the value in A2 is 1.27. Well the value that will be displayed as a result of the roundup formula is 1.3. The comma followed by the number one in the formula above tells excel to round the value in A2 up by 1 decimal place.
Read More..

Saturday, November 24, 2012

Round formula

=Round(A2,1)
The excel formula above rounds the value in cell A2 up by 1 decimal place. Let's say the value in A2 is 1.27. Well the value that will be displayed as a result of the round formula is 1.3. The comma followed by the number one in the formula above tells excel to round the value in A2 up by 1 decimal place.
Read More..

Number of characters or digits formula

=len(A2)
The excel formula above will tell you the total length of characters in cell A2. The formula also considers spaces as part of it's count of the number characters. So, if you had the word count with a single space after it like this:  "Count ". It will sum the number of  characters in the word count and add the single space as well, which would come to 6 as opposed to 5. Also if you had the word "Mark" with no spaces in it. it would give you the sum of 4 as the length of characters being that there are only 4 characters in the word Mark.
Read More..

Sunday, November 18, 2012

Else if statement

Let us say column A has you prices and you want to add 25% between 10 and 19, add 35% between 20 and 29 and so on. The best thing to do is an else if statement like this:

if(a2
Read More..

Saturday, November 17, 2012

Convert text to initial capital (proper)

=proper(A2)
The excel formula above will convert the text in the cell A2 to initial capital. So, the text "debt" will be converted to "Debt".
Read More..

Wednesday, November 14, 2012

Convert text to lower case or small letters

=lower(A2)
The excel formula above will convert any text to lower case. For example the text "CAN" would be converted to "can".
Read More..

Convert string to upper case

=upper(A2)
The excel formula above will convert text of any format to capital letters. For instance, if the text in cell a2 said "trust", then the formula will convert it to "TRUST".
Read More..

Trim spaces

=trim(A2)
The excel formula above will remove spaces in front and behind any text or number or date value. It does not remove spaces in between.
Read More..

Saturday, November 10, 2012

Next month date function for Excel

=month(now())+1
The excel formula above will display the next month from the current month in excel. So, if this month is February, the formula will display "March".
Read More..

Previous month date function in Excel

=month(now())-1
The excel formula above will display the previous month from the current within excel. So, if the current month is February. Then Excel will display the word "January".
Read More..

Sunday, November 4, 2012

Month date function in Excel

=month(now())
The excel formula above will display the current month of the year once inputted within excel. So, if we are in the month of February, the excel formula will display the word "February".
Read More..

Saturday, November 3, 2012

One week in the future date function in Excel

=today()+7
The excel formula above will didplay the date in one week from the current date within excel.
Read More..

Next year's date function in Excel

=year(now())+1
The excel formula above will display next year's date within excel.  So, if the current year is 2012 then the formula will display 2013.
Read More..

Tuesday, October 30, 2012

Using Weekday function in Excel

=Weekday(now())
The above formula will display the current day of the week. For example, if you entered this formula in a cell within excel on a Monday. Then the formula will display the word "Monday".
Read More..

Using Tomorrow date function in Excel

=today()+1
The excel formula above will display tomorrow's date within excel. So, if today was the 01/01/2014 the formula would display 02/01/2014.
Read More..

Sunday, October 28, 2012

Year function

=year(now())
The excel formula above will display the current year as set on your computer.
Read More..

Month function in Excel

=month(now())
The excel formula above will give you a display of the current month in excel as set on your computer.
Read More..

Using Addition in Excel

=A2+B2
The formula above will add the value of cell A2 to the value of cell B2.
Read More..

Yesterday's Date Function in Excel

=today()-1
The formula above will give you yesterday's date.  It does this by subtracting one day from today's date.
Read More..

One week ago date function in Excel

=today()-7
The excel formula above will the date of 1 week ago. It will do this be subtracting seven days from todays date.
Read More..

Current Date Function in Excel

=today()
The formula above when inputted in excel will give you the current date as displayed on your computer.
Read More..

Absolute formula in Excel

The excel absolute formula returns the value of the referenced cell as a positive value whether it is positive or negative. So take the following two formulas: =ABS(20) will display the value 20. =ABS(-20) will also display the value 20.
Read More..

Division

=A2/B2
Using the foward slash as a division symbol...the formula above will divide the value in cell A2 by the value in cell B2.
Read More..

Multiplication in Excel

=A2*B2
Using the astrix, the formula above will multiply the value in cell A2 by the value in cell B2.
Read More..

Using Subtraction in MS Excel

=A2-B2
The formula above will subtract the value of cell B2 from cell A2.
Read More..

Date and Time function in Excel

=Now()
The formula above will extract the current date and time in excel of the computer it is on.
Read More..

Using IF Statements in MS Excel

An if statement didplays a specific output based on the value of a particular cell.   An else if statement displays more than one output...So it is not giving a yes or no answer, but several possible answers.
For example, say you want to dislay the word child for the age less than 18 and the word working adult for ages less than 65 and the word retired for ages equal to and over 65. Let us say column A has the range of ages.
The formula below is an example of the above query:
=IF(A2<18,"Child",IF(A2<65,"Working Adult","Retired"))
Note: quotes are always used for text or alpha numeric data. Do not include quotes for number values.
Read More..

IF Search Statement (wildcard if statement)

An if search statement diplays a specific output based on a value that is embedded within a text string that contains other values.
For example, say you want to dislay the number 1.78 everytime the value in any of the cells in column A contains the value of 16:9 or otherwise display the word "Check Material". Let's also say an example text in cell A2 is "625 PAL - 16:9 WIDESCREEN"
The formula below is an example answer to the above query:
=IF(NOT(ISERROR(SEARCH("16:9",A2,1),1.78,"Check Material")
Note: quotes are always used for text or alpha numeric data. Do not include quotes for number values.
Read More..

IF Statements...continued

An if statement didplays a specific output based on the value of a particular cell.
For example, say you want to dislay the word "Yes" everytime the value in any of the cells in column A say "Edit-Base" or otherwise display the word "No".
The formula below is an example of the above query:
=IF(A2="Edit-Base","Yes","No")
Note: quotes are always used for text or alpha numeric data. Do not include quotes for number values.
Read More..

Using IF Statements in MS Excel

An if statement didplays a specific output based on the value of a particular cell.
For example, say you want to dislay the word "No" everytime the value in any of the cells in column A say "Clean Version" or otherwise display the word "Yes".
The formula below is an example of the above query:
=IF(A2="Clean Version","No","Yes")
Note: quotes are always used for text or alpha numeric data. Do not include quotes for number values.
Read More..

Using Minimum Formula ub Excel

=Min("A:A")
The formula above will find the cell with the minimum or lowest value in column A. It will also display this value.
Read More..

Using VLOOKUP Exact Match Formula in Excel

The theory for vlookups is as follows:
=VLOOKUP(cell reference, corresponding range of cells to link to, column number to output, false)
An example is as follows:
=VLOOKUP(A2,$C$2:$D$30,2,FALSE)
The formula above will link cell A2 to cells C2 to C30. Note the values in cells C2 to C30 must be the same type as the values in column A where cell A2 exists. The number 2 means that the data returnerd from the vlookup formula would be from the 2nd column to column C, which would be Column D.  The most appropriate data to use as the link reference or lookup cell reference would be unique identifiers. So column A and cells C2 to C30 would work perfectly with them both being customer id columns or employee id columns. That would be the most appropriate way to match any data.  The word: "False" means: Find an exact match.
Read More..

Using Maximum formula in Excel

=MAX("A:A")
The formula above will find the cell with the maximum or highest value in column A. It will also display this value.
Read More..

Using CountIF Formula in Excel

=CountIF(A:A,"Test")
The formula above will count the number of times the value Test appears in the whole of column A and also display the number.
Read More..

Using Sum Formula in Excel

=Sum("A:A")
The formula above will give you the total value of column A (e.g in the case that you have sales figures in column A). It will display this value.
Read More..

Sunday, July 1, 2012

password protect in Excel



1 Click on File>
2 Click on Prepare>
3 Click on Encrypt Document








4 Enter a password of your choice> then click OK






5 Enter the same password again for confirmation> then click OK




Read More..

www.cineclouds.com. Powered by Blogger.