Saturday, 29 March 2014

YEARFRAC FUNCTION IN EXCEL






What Does It Do?
These functions are used to calculate the difference between two dates and express of the numerical values the result as a decimal fraction. We are change the formatting according to choose i.e. number or percentage.
Syntax
=YEARFRAC(StartDate,EndDate,Basis)
Basis: Defines the Calendar system to be used in the function.
0: number or omitted USA style 30 days per month divided by 360.
1: The 1 is use for 29 or 30 or 31 days per month divided by 365.
2: The 2 is use for 29 or 30 or 31 days per month divided by 360.
3: The 3 is use for 29 or 30 or 31 days per month divided by 365.
4: The 4 is use in European 29 or 30 or 31 days divided by 360.
Formatting
This result will be show as a decimal fraction, but we can be formatted as a percent. 
Example
These tables were used by a company which hired some people on short term contracts for a part of the years. These pro Rata of salary which are represents the annual salary entered. Its start and End dates of the contract are entered, then the company was used this =YEARFRAC() functions is used to calculate the actual salary for the portion of the year.





Note
We are extra 1 added to the End date to compensate for the fact that the =YEARFRAC() function calculates from the Start date up to, but don’t including the End date.

YEAR FUNCTION IN EXCEL






What Does It Do?
These functions are used to extracts the year number from a date.
Syntax
=YEAR(Date)
Formatting
These functions have result show as a number.

WORKDAY FUNCTION IN EXCEL






What Does It Do?
These functions are used to calculate a past or future date based on an opening date and an individual number of days. These functions have excludes weekends and holidays and we can therefore be used to calculate the delivery date or invoice dates.
Syntax
=WORKDAY(StartDate,Days,Holidays)
Formatting
These functions are show the result normally as a number which can be formatted to a normal date by using the Format, Cells, Number and Date.
Example
These examples were show how these functions are used to calculate delivery dates based upon an initial order date and expected Delivery days.


WEEKDAY FUNCTION IN EXCEL






What Does It Do?
These functions are used to show the day of the week from a date.
Syntax
=WEEKDAY(Date,Type)
Type: These are used to indicate the week day in numbering system.
1: number will be set the Sunday as 1 through to Saturday as 7
2: number will be set Monday as 1 through to Sunday as 7
3: number will set Monday as 0 through to Sunday as 6.
Whenever, we are not entered any specified number, Excel will use 1.
Formatting
These functions are showing the result as a normal number format. We are show the result as the name of the day, use Format, Cells, and Custom and set the Type to ddd or dddd.
Example
These functions are used by a hotel which rented a function room. The hotel management is charged different type of rates of rooms depending upon which day of the week the booking was for. These are booking Date is entered. This Actual Day is calculated. These booking cost were picked from a list of rates using the =LOOKUP() function.



VELOOKUP FUNCTION IN EXCEL






What Does It Do?
These functions are used to search down the row headings at the side of a data to find a particular item. When it is found the item, it then search across to pick a cell entry.
Syntax
=VLOOKUP(Lookup_Value,Table_array,Column_Index_Num,Range_Lookup)
The Lookup_Value is use a single item individual by user. The Table_array is the row headings with the range of data in the left hand side. The Column_index_num is use to how far across the table the function should show to pick from. The Range_Lookup is used to the column headings are sorted. TRUE for yes, FALSE for no.
Formatting
These functions have no any special formatting needed.
Example 1
These tables were used to find a value based on a specified name and month. These =VLOOKUP() functions are used to look into behind to find the name in the data. These problem are arises when we need to look across to find the month column. We are used this =MATCH() function for to solve the problem.
This =MATCH() function is use to show through the list of names to find the month we require. It then calculates the position of the month in the list. Because these list of months are not as wide as the lookup range find, then the =MATCH() number is 1 less than our require, so and extra number 1 is added to give back. These =VLOOKUP() functions with used this =MATCH() function to show the number across the columns and picks out the Wright cell entry. These =VLOOKUP() functions are used to FALSE for indicate to Excel in the end of the function that the row headings are not sorted.





Example 2
These example look to how the =VLOOKUP() are used by a spare part shop for pick the cost of different makes of cars. These =VLOOKUP() functions are scans down the row headings in the column F for the spare part entered in the column C. When we are want to found make part, the =VLOOKUP() then scans across to find the price, and we are using the =MATCH() function to find the position of the maker of cars parts.
These functions are used the absolute ranges indicated by the dollar symbol. When we are copied this formula to more cells, then the ranges for =VLOOKUP() and =MATCH() do not change.





Example 3
These examples are used by a builders merchants offering discount in the large order. Unit cost table holds of cost 1 units of Brick, Wood and Glass. This discount table holds the various discounts of different quantity of each product. These order tables are used to entered the orders and calculates the total. In the orders table all the calculations take place. We are typed the item name in the column C of the order table. These unit costs of the item are show in the units cost table. The FALSE options have been used to end of functions to indicate that the product names down the unit cost table are not sorted. We are using the FALSE option forces the function to search for an extract match. Whenever the match is not fount, then the functions is show the error. These discounts are then showed in the Discount Table. If the ordered quantity matches a values at the side of the discount the =VLOOKUP() will show across to find the correct discount. These TRUE options have been used to indicate at the end that the values shows down the side of the Discount Table are sorted. We are use the TRUE for allow the function to make an approximate match. If the Ordered quantity does not match a value at the side of the Discount Table, then the next lowest value is used. When we are try to match an order of 125 will be drop down to 100, and the discount from the 100 row is used.


VERP FUNCTION IN EXCEL






What Does It Do?
These functions are calculating the variance of a list of values. These variances are calculated on the basis that this value corresponds to the whole population.
Syntax
=VARP(Range1,Range2,Range3 through to Range30)
Formatting
These functions have no any special formatting needed.
Example
These tables are used by a company to interest in buying a new machine for packing washing powder. The company is trial run a just packing four boxes per machine produces in one day. These boxes were weighed and =VARP() functions was used as these boxes represented the entire to text run. These machine were smallest variance was the most consistent.






Explanation of formula:                               
            This finds the lowest value.     =(MIN(H32:H34)               
            This finds the position of the lowest value.
  =MATCH(MIN(H32:H34),H32:H34,0)               
            This looks down the Machine column to     =INDEX(C32:C34,MATCH(MIN(H32:H34),H32:H34,0))               
            find the machine name.                   


VAR FUNCTION IN EXCEL






What Does It Do?
These functions are used to calculate the population disagreement of a data of values. The population is used these function when the list of values represents a sample of a population.
Syntax
=VAR(Range1,Range2,Range3 through to Range30)
Formatting
These functions have no any special formatting needed.
Example
These tables were used by a company attracted in buying a new machine for pack washing powder. Three machines were undersized listed and allow to run for a day in a company. On the end of the day four boxes of soap powder were packed at random from the production of each machine. These boxes were weighed and the =VAR() function is used for these boxes to represented a sample of the complete the product in a day. These machine are smallest variance was the most consistent.






Explanation of formula:                               
            This finds the lowest value.     =MIN(H34:H36)               
            This finds the position of the lowest value. =MATCH(MIN(H34:H36),H34:H36,0)               
            This looks down the Machine column to      =INDEX(C34:C36,MATCH(MIN(H34:H36),H34:H36,0))               
            find the machine name.                   


VALUE FUNCTION IN EXCEL






What Does It Do?
These functions are used to converts a piece of text which write a number into an actual value. If these number are entered in the middle of a long piece of text then it will have to be extracted using other text functions such as =SEARCH(), =MID(), =FIND(), =SUBSTITUTE(), =LEFT() or =RIGHT().
Syntax
=VALUE(TextToConvert)
Formatting
These functions have no any special formatting needed. These results will be shown as a value format such same as entered with the text original values. If we are included this £ sign than these functions are ignore it. If we are entered the % sign with text, than these functions are get the result decimal fraction which we can then be formatted as a percentage. And if we are entered the hh:mm format with text, the result will be a time format. The same will be true for other recognized formats.
Explanation of formula shown above.
When a value is enter in the following text it is very complicated to extract. These actual percentage value are of inconsistent length, it can be either show the one, two or three digits long. The only way to identify the values is the fact it always ends with the % sign. These are not manner to make out the starting of the value, other than it is preceded by a space. These are main problem of calculating the length of the value to take out. If we are extract assumes the maximum length of three digits and include the % sign, then result will be occur when the percentage is only one digit long, the number is included of alphabetic characters. We get around the problem =SUBSTITUTE() function was used to improve the size of the spaces in the text. Now when the insertion takes place any unnecessary characters will be spaces that is ignored by the=VALUE() functions.


UPPER FUNCTION IN EXCEL


What Does It Do?
These functions are used to converts all characters in a piece of text to upper case.
Syntax
=UPPER(TextToConvert)
Formatting
These functions have no any special formatting needed.

TRIM FUNCTION IN EXCEL






What Does It Do?
These functions are used to removes the unwanted spaces between pieces of text. These functions are completely removed the spaces before and after the text. When the multiple spaces are the text will be trimmed to a single space.
Syntax
=TRIM(TextToTrim)
Formatting
These functions have no any special formatting needed.

TREND FUNCTION IN EXCEL






What Does It Do?
These functions are used to calculate values based upon three sets of related values. These predictions are based upon the Linear Trend of the original values. These functions are an array function and we must be entered using Ctrl+Shift+Enter.
Syntax
=TREND(KnownYs,KnownXs,RequiredXs,Constant)
These KnownYs are the range of values, such as the Sales Figures.
These KnownXs are the intervals used when collecting the data, such as months.
These RequiredXs are the range for which we want to make the prediction the data, such as Months.
Formatting
These functions have no any special formatting needed.
Example
These tables were used by a company when they make a profit list of every month. Their bank manager told the companies we are provide this facility only for one year so, you are made the next year profit itself. To the bank manager proved that the previous year profit data, the company would to start to make the next year profit list, than the company used these functions =TREND(). These past years historical data was entered 1 to 12 months. The company predict the months of 13 to 24. These =TREND() functions were show, it will be get the profit below the 22 months.





How to Enter An array Formula
We are selecting the all cells where the array is required, such as F41 to F52. Then the formula type such as =TREND(C41:C52,B41:B52,E41:E52), but then do not press Enter. Hold the Ctrl+Shift keys down. We are press Enter to enter the formula as an array.

TRANSPOSE FUNCTION IN EXCEL






What Does It Do?
We are use this function for copies data from a range, and pastes in this data in another new range, spinning it so that the data in the beginning in columns is now in rows, and the data originally in rows is in columns. These transpose range must be the same size of the original range. These functions are needs to be entered same and selection formula. When we are enter an selective formula we must first highlight all the cells where these formula is mandatory. Next type the formula, such as =TRANSPOSE(A1:A5). Finally we press the Ctrl+Shift+Enter used to confirm it.
Syntax
=TRANSPOSE(Range)
Formatting
These functions have no any special formatting needed.

TODAY FUNCTION IN EXCEL






What Does It Do?
These functions are used to show the current time.
Syntax
=TODAY()
Formatting
These functions have a formatting show the DD-MM-YYYY.
Example
These tables are showed how use the today function is calculate the present date and a value difference.





Note that the actually date number before the today date. To calculate a result which include the current date an extra 1 will be added.





Example
These examples show the number of days from today until the year 2000.

TIMEVALUE FUNCTION IN EXCEL






What Does It Do?
These functions are showing an actual time value on a piece of text which looks like a time. It is useful when data is imported from other applications, such as from mainframe computer, which convert all values to text.
Syntax
=TIMEVALUE(Text)
Formatting
These functions are show as a number representing the time a fraction of the day. These functions formatting can be applied for either the 12 or 24 hour clock system.

TIME CALCULATION FUNCTION IN EXCEL

Excel can work and time very easily. We can be entered in various types of formats and calculations performed the time. There are one or two oddities, but nothing which should put you off working with it. See the Time Sheet example for an example.
Typing time
When we are entered into worksheet we should be entered with a column between the hour and the minutes, such as 12:30, rather than 12.30
Excel can be managing with either the 12hour or 24hour system or the am/pm system. When we are use the am/pm system we must enter the am or pm after the time. We must leave a space between the number and the text value.

Finding the difference between two times
We can subtract the two time values to gets the time-span of times between the values of difference of two time value.






Adding time
We want to find a total time, then we are added the time. These works are rest until and the take to more above time of 24 hours. For totals works time is greater than 24 hours we may need to apply some special formatting.





Formatting time
When we are added time together the result may be go away from 24 hours. Regularly these give an incorrect result, same in the example below. To get the correct this error, we want the result needs to be formatted with a convention format.




How To Apply Custom Formatting                       
The custom format for time use a pair of square brackets [hh] on either side                       
of the hours indicators.                       
1. Click on the cell which needs the format.   
2. Choose the Format menu.           
3. Choose Cells.           
4. Click the Number tag at the top right.           
5. Choose Custom.           
6. Click inside the Type: box.           
7. Type [hh]:mm as the format.           
8. Click OK to confirm.