Ms excel: if function (WS)
MS Excel: Two-Dimensional Lookup (Example #4)
Yüklə 456,44 Kb.
|
- Bu səhifədəki naviqasiya:
- MS Excel
- Syntax
- Example (as Worksheet Function)
- Value Explanation
- Example (as VBA Function)
- Frequently Asked Questions
MS Excel: Two-Dimensional Lookup (Example #4)This Excel tutorial explains how to perform a two-dimensional lookup (with screenshots and step-by-step instructions). This is example #4. Question: In Microsoft Excel, I want to match the values in two columns on sheet A (size and frequency) to a column and a row on sheet B (size and frequency) to return a value on sheet B. For example, size=2 and frequency=2 on Sheet A would return $23.00 from the chart on Sheet B (size=2, frequency=2). I would like to have these values displayed in Column D on sheet A. Answer: In effect, what we are trying to do is perform a 2-dimensional lookup in Excel. To find a value in Excel based on both a column and row value, you will need to use both the vlookup function and the match function. Let's look at an example. Download Excel spreadsheet (as demonstrated below)
In the spreadsheet above, we have a chart on sheet B with Size and Frequency. 
Then on Sheet A, we have Size and Frequency values in columns A and B. We want to return in column D, the correct value from the chart in Sheet B using these Size and Frequency values. So if you looked up a Size=2 and Frequency=2 in the chart in sheet B, it should return $23.00. To do this, we will use the following formula in cell D2: =VLOOKUP(A2,B!$A$2:$G$8,MATCH(B2,B!$A$2:$G$2,0),FALSE) This formula returns $23.00. In cell D3, if we wanted to look up a Size=2 and Frequency=1, it should return $22.00. To do this, we will use the following formula: =VLOOKUP(A3,B!$A$2:$G$8,MATCH(B3,B!$A$2:$G$2,0),FALSE) This formula return $22.00. MS Excel: DB Function (WS) This Excel tutorial explains how to use the Excel DB function with syntax and examples. Description The Microsoft Excel DB function returns the depreciation of an asset for a given time period based on the fixed-declining balance method. Syntax The syntax for the DB function in Microsoft Excel is: DB( cost, salvage, life, period, [number_months] ) Parameters or Arguments cost The original cost of the asset. salvage The salvage value after the asset has been fully depreciated. life The useful life of the asset or the number of periods that you will be depreciating the asset. period The period that you wish to calculate the depreciation for. Use the same units as for the life. number_months Optional. It is the number of months in the first year of depreciation. If this parameter is omitted, the DB function will assume that there are 12 months in the first year. Applies To The DB function can be used in the following versions of Microsoft Excel:
Type of Excel Function The DB function can be used in Microsoft Excel as the following type of function:
Example (as Worksheet Function) Let's look at an example to how you would use the DB examples and explore how to use the DB function as a worksheet function in Microsoft Excel: This first example returns the depreciation for an asset that costs $10,000, with a salvage value of $5,000. The useful life of the asset is 5 years. The depreciation is being calculated for the first year, and there are 12 months in the first year. =DB(10000, 5000, 5, 1, 12) Result: $1,290.00 This next example returns the depreciation for an asset that costs $10,000, with a salvage value of $5,000. The useful life of the asset is 5 years. The depreciation is being calculated for the second year, and there are 12 months in the first year. =DB(10000, 5000, 5, 2, 12)
This next example returns the depreciation for an asset that costs $10,000, with a salvage value of $5,000. The useful life of the asset is 5 years. The depreciation is being calculated for the third year, and there are 10 months in the first year. =DB(10000, 5000, 5, 3, 10)
This Excel tutorial explains how to use the Excel PV function with syntax and examples. Description The Microsoft Excel PV function returns the present value of an investment based on an interest rate and a constant payment schedule. Syntax The syntax for the PV function in Microsoft Excel is: PV( interest_rate, number_payments, payment, [FV], [Type] ) Parameters or Arguments interest_rate The interest rate for the investment. number_payments The number of payments for the annuity. payment The amount of the payment made each period. If this parameter is omitted, you must enter a FV value. FV Optional. It is the future value of the payments. If this parameter is omitted, it assumes FV to be 0. Type Optional. It indicates when the payments are due. If the Type parameter is omitted, it assumes a Type value of 0. Type can be one of the following values:
Applies To The PV function can be used in the following versions of Microsoft Excel:
Type of Excel Function The PV function can be used in Microsoft Excel as the following type of function:
Example (as Worksheet Function) Let's look at some PV examples and explore how to use the PV function as a worksheet function in Microsoft Excel: This first example returns the present value of an investment that pays $250 at the end of every month for 2 years. The money paid out will earn 7.5% annually. =PV(7.5%/12, 2*12, 250, , 0) Result: -$5,555.61 This next example returns the present value of an investment that pays $50 at the beginning of every week for 4 years. The money paid out will earn 6% annually. =PV(6%/52, 4*52, 50, , 1)
This next example returns the present value of an investment that pays $100 at the end of every year for 10 years. The money paid out will earn 5.25% annually. =PV(5.25%/1, 10*1, 100, , 0)
The PV function can also be used in VBA code in Microsoft Excel. Let's look at some Excel PV function examples and explore how to use the PV function in Excel VBA code: LValue = PV(0.0525/1, 10*1, 100, , 0) In this example, the variable called LValue would now contain the value of ($762.88) MS Excel: SYD Function (WS, VBA) This Excel tutorial explains how to use the Excel SYD function with syntax and examples. Description The Microsoft Excel SYD function returns the depreciation of an asset for a given time period based on the sum-of-years' digits depreciation method. Syntax The syntax for the SYD function in Microsoft Excel is: SYD( cost, salvage, life, period ) Parameters or Arguments cost The original cost of the asset. salvage The salvage value after the asset has been fully depreciated. life The useful life of the asset or the number of periods that you will be depreciating the asset. period The period that you wish to calculate the depreciation for. Use the same units as for the life. Applies To The SYD function can be used in the following versions of Microsoft Excel:
Type of Excel Function The SYD function can be used in Microsoft Excel as the following type of function:
Example (as Worksheet Function) Let's look at some SYD examples and explore how to use the SYD function as a worksheet function in Microsoft Excel: This first example returns the depreciation for an asset that costs $10,000, with a salvage value of $5,000. The useful life of the asset is 5 years. The depreciation is being calculated for the first year. =SYD(10000, 5000, 5, 1) Result: $1,666.67 This next example returns the depreciation for an asset that costs $10,000, with a salvage value of $5,000. The useful life of the asset is 5 years. The depreciation is being calculated for the second year. =SYD(10000, 5000, 5, 2)
This next example returns the depreciation for an asset that costs $8,000, with a salvage value of $0. The useful life of the asset is 7 years. The depreciation is being calculated for the third year. =SYD(8000, 0, 7, 3)
The SYD function can also be used in VBA code in Microsoft Excel. Let's look at some Excel SYD function examples and explore how to use the SYD function in Excel VBA code: Dim LValue As Double LValue = Syd(8000, 0, 7, 3) In this example, the variable called LValue would now contain the value of 1428.571429. MS Excel: CONCATENATE Function (WS) This Excel tutorial explains how to use the Excel CONCATENATE function with syntax and examples. Description The Microsoft Excel CONCATENATE function allows you to join 2 or more strings together. Syntax The syntax for the CONCATENATE function in Microsoft Excel is: CONCATENATE( text1, text2, ... text_n ) Parameters or Arguments text1, text2, ... text_n The strings that you wish to join together. There can be up to 255 strings that are joined together, up to a maximum of 8,192 characters. Applies To The CONCATENATE function can be used in the following versions of Microsoft Excel:
Type of Excel Function The CONCATENATE function can be used in Microsoft Excel as the following type of function:
Example (as Worksheet Function) Let's look at some Excel CONCATENATE function examples and explore how to use the CONCATENATE function as a worksheet function in Microsoft Excel: 
Based on the Excel spreadsheet above, the following CONCATENATE examples would return: =CONCATENATE(A1, A2)
=CONCATENATE("Tech on the ", "Net") Result: "Tech on the Net" =CONCATENATE(A1, "bet soup") Result: "Alphabet soup" Concatenate Space Characters When you are concatenating values together, you might want to add space characters to separate your concatenated values. Otherwise, you might get a long string with the concatenated values running together. This makes it very difficult to read the results. Let's look at an easy example. 
Based on the Excel spreadsheet above, we can concatenate a space character within the CONCATENATE function as follows: =CONCATENATE(A1, " ", A2)
In this example, we have used the second parameter within the CONCATENATE function to add a space character between the values in cell A1 and cell A2. This will prevent our values from being squished together. Instead our result would appear as follows: "TechOnTheNet.com resource" Here, we have concatenated the values from the two cells (A1 and A2), separated by a space character. Concatenate Quotation Marks Since the parameters within the CONCATENATE function are separated by quotation marks when they are string values, it isn't straight forward how to add a quotation mark character within the result of the CONCATENATE function. Let's start with a fairly easy example that shows how to add a quotation mark to the resulting string using the CONCATENATE function. 
Based on the Excel spreadsheet above, we can concatenate a quotation mark as follows: =CONCATENATE("One ", """", " Two")
In this example, we have used the second parameter within the CONCATENATE function to add a quotation mark into the middle of the resulting string. Since our parameters are enclosed in quotation marks, we use 2 additional quotation marks within the surrounding quotation marks to represent a quotation mark in our result as follows: """"
Then when you put the whole function call together: =CONCATENATE("One ", """", " Two") You will get the following result: One " Two Frequently Asked Questions Question:For an IF statement in Excel, I want to combine text and a value. For example, I want to put an equation for work hours and pay. If I am paid more than I should be, I want it to read how many hours I owe my boss. But if I work more than I am paid for, I want it to read what my boss owes me (hours*Pay per Hour). I tried the following: =IF(A2<0,"I owe boss" abs(A2) "Hours","Boss owes me" abs(A2)*15 "dollars") Is it possible or do I have to do it in 2 separate cells? (one for text and one for the value)
=IF(A2<0,"I owe boss " & ABS(A2) & " Hours","Boss owes me " & ABS(A2)*15 & " dollars") Or the second method is to use the CONCATENATE function: =IF(A2<0,CONCATENATE("I owe boss ", ABS(A2)," Hours"), CONCATENATE("Boss owes me ", ABS(A2)*15, " dollars")) Yüklə 456,44 Kb. Dostları ilə paylaş:
|
