Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index, Match, Lookup HELP PLEASE!
First I want to say I have read many posts and tried many suggestions on
those post before posting my question hoping I wouldn't have to ask a question that has been answered several times already. I am creating an Excel workbook for my business math class. On one page is the actual math problems and on the other two sheets, each one has a table. One table is Present value of $1 and the other is Future Value of a $1. When I try and put a formula in the math problem sheet that is referenced to one of the tables I get #NA or one of the many other error messages. Here is an example of what I need: Math problem is: what is the present value if in 3 years I have $15,000.00 that received 6% interest compounded quarterly. To figure this out I have to find the compound interest and the periods of that interest. To do this I have to take R/T or 6%/4 which is 1.5% and then multiply the years by the compound periods 3*4 which is 12. Once I work this out on my math sheet I need to go to the PVTable which is my present value table and fine the periods which is 12 in the first column, and the compound interest which is 1.5% in the first row and get the table value where the two intersect. Because I need to be able to change the interest and compound periods and priciple or future amounts without having to solve all over again. I thought it was going to be much simpler than it has proven to be but no matter which formula I use I get an error. This was the last few I used from other post suggestions: =INDEX(table range including headers,MATCH(value in column,column range,0),MATCH(row value,row range,0)) =INDEX(your range,MATCH(row value,rows to look in),MATCH(column value,columns to look in)) =INDEX('Penalty Table'!$A$7:$F$26, MATCH($A2,'Penalty Table'!$A$7:$A$26,), MATCH(B$1,'Penalty Table'!$A$7:$F$7,)) And several others as well. Please help me before I go crazy. I even took an excel class two terms ago that went over all of this and I got an A, so much for that. Thank you Candie |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index, Match, Lookup HELP PLEASE!
That's an awful lot of work...why not use the single function FV?
=FV(6%/4,3,,-15000) Note that all of these values could be replaced with cell references to make a nice fast formula. From XL help file: Syntax FV(rate,nper,pmt,pv,type) For a more complete description of the arguments in FV and for more information on annuity functions, see PV. Rate is the interest rate per period. Nper is the total number of payment periods in an annuity. Pmt is the payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes. If pmt is omitted, you must include the pv argument. Pv is the present value, or the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt argument. Type is the number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0. -- Best Regards, Luke M "goodgirlinterrupted" wrote in message ... First I want to say I have read many posts and tried many suggestions on those post before posting my question hoping I wouldn't have to ask a question that has been answered several times already. I am creating an Excel workbook for my business math class. On one page is the actual math problems and on the other two sheets, each one has a table. One table is Present value of $1 and the other is Future Value of a $1. When I try and put a formula in the math problem sheet that is referenced to one of the tables I get #NA or one of the many other error messages. Here is an example of what I need: Math problem is: what is the present value if in 3 years I have $15,000.00 that received 6% interest compounded quarterly. To figure this out I have to find the compound interest and the periods of that interest. To do this I have to take R/T or 6%/4 which is 1.5% and then multiply the years by the compound periods 3*4 which is 12. Once I work this out on my math sheet I need to go to the PVTable which is my present value table and fine the periods which is 12 in the first column, and the compound interest which is 1.5% in the first row and get the table value where the two intersect. Because I need to be able to change the interest and compound periods and priciple or future amounts without having to solve all over again. I thought it was going to be much simpler than it has proven to be but no matter which formula I use I get an error. This was the last few I used from other post suggestions: =INDEX(table range including headers,MATCH(value in column,column range,0),MATCH(row value,row range,0)) =INDEX(your range,MATCH(row value,rows to look in),MATCH(column value,columns to look in)) =INDEX('Penalty Table'!$A$7:$F$26, MATCH($A2,'Penalty Table'!$A$7:$A$26,), MATCH(B$1,'Penalty Table'!$A$7:$F$7,)) And several others as well. Please help me before I go crazy. I even took an excel class two terms ago that went over all of this and I got an A, so much for that. Thank you Candie |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index, Match, Lookup HELP PLEASE!
It appears to me that the third example, which probably closely matches the
reality of your spreadsheet, should probably work. BUT you need to test the two MATCH portions to see that both of them are working properly. Just pick a couple of cells on the same sheet with that formula and pull out the two MATCH() statements, pop an = symbol into a cell with the MATCH() statement behind them and see which of them is or isn't working. It may be that you have a mismatch in the values in your label row or column. Since the label column (number of periods) is probably integer numbers in both cases, then it's probably not the problem. But the match of the % may not be working because it's actually a percentage on the calculation sheet, but maybe you're just displaying it as a decimal value in A7:F7 on the Penalty Table sheet. To try to explain the possible error another way: on the calculation sheet, you've actually got a percentage, as 1.5% (or .015) while on the table sheet you may have a row with simply numbers representing the percentages, but in fact are whole numbers with a decimal value, as 1.5 2.0 2.25 2.5 and numbers like that won't match: 1.5 = 150% not 1.5% "goodgirlinterrupted" wrote: First I want to say I have read many posts and tried many suggestions on those post before posting my question hoping I wouldn't have to ask a question that has been answered several times already. I am creating an Excel workbook for my business math class. On one page is the actual math problems and on the other two sheets, each one has a table. One table is Present value of $1 and the other is Future Value of a $1. When I try and put a formula in the math problem sheet that is referenced to one of the tables I get #NA or one of the many other error messages. Here is an example of what I need: Math problem is: what is the present value if in 3 years I have $15,000.00 that received 6% interest compounded quarterly. To figure this out I have to find the compound interest and the periods of that interest. To do this I have to take R/T or 6%/4 which is 1.5% and then multiply the years by the compound periods 3*4 which is 12. Once I work this out on my math sheet I need to go to the PVTable which is my present value table and fine the periods which is 12 in the first column, and the compound interest which is 1.5% in the first row and get the table value where the two intersect. Because I need to be able to change the interest and compound periods and priciple or future amounts without having to solve all over again. I thought it was going to be much simpler than it has proven to be but no matter which formula I use I get an error. This was the last few I used from other post suggestions: =INDEX(table range including headers,MATCH(value in column,column range,0),MATCH(row value,row range,0)) =INDEX(your range,MATCH(row value,rows to look in),MATCH(column value,columns to look in)) =INDEX('Penalty Table'!$A$7:$F$26, MATCH($A2,'Penalty Table'!$A$7:$A$26,), MATCH(B$1,'Penalty Table'!$A$7:$F$7,)) And several others as well. Please help me before I go crazy. I even took an excel class two terms ago that went over all of this and I got an A, so much for that. Thank you Candie |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index, Match, Lookup HELP PLEASE!
Couple changes needed actually on this formula, he states $15k is the FUTURE
value, so he needs to discount to PRESENT value, so we need the present value function: PV(). Also nper should be 12 because there are 3 years of quarterly compounds, or 3*4, so the formula should be: =PV(6%/4,12,,-15000) -- Regards, Dave "Luke M" wrote: That's an awful lot of work...why not use the single function FV? =FV(6%/4,3,,-15000) Note that all of these values could be replaced with cell references to make a nice fast formula. From XL help file: Syntax FV(rate,nper,pmt,pv,type) For a more complete description of the arguments in FV and for more information on annuity functions, see PV. Rate is the interest rate per period. Nper is the total number of payment periods in an annuity. Pmt is the payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes. If pmt is omitted, you must include the pv argument. Pv is the present value, or the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt argument. Type is the number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0. -- Best Regards, Luke M "goodgirlinterrupted" wrote in message ... First I want to say I have read many posts and tried many suggestions on those post before posting my question hoping I wouldn't have to ask a question that has been answered several times already. I am creating an Excel workbook for my business math class. On one page is the actual math problems and on the other two sheets, each one has a table. One table is Present value of $1 and the other is Future Value of a $1. When I try and put a formula in the math problem sheet that is referenced to one of the tables I get #NA or one of the many other error messages. Here is an example of what I need: Math problem is: what is the present value if in 3 years I have $15,000.00 that received 6% interest compounded quarterly. To figure this out I have to find the compound interest and the periods of that interest. To do this I have to take R/T or 6%/4 which is 1.5% and then multiply the years by the compound periods 3*4 which is 12. Once I work this out on my math sheet I need to go to the PVTable which is my present value table and fine the periods which is 12 in the first column, and the compound interest which is 1.5% in the first row and get the table value where the two intersect. Because I need to be able to change the interest and compound periods and priciple or future amounts without having to solve all over again. I thought it was going to be much simpler than it has proven to be but no matter which formula I use I get an error. This was the last few I used from other post suggestions: =INDEX(table range including headers,MATCH(value in column,column range,0),MATCH(row value,row range,0)) =INDEX(your range,MATCH(row value,rows to look in),MATCH(column value,columns to look in)) =INDEX('Penalty Table'!$A$7:$F$26, MATCH($A2,'Penalty Table'!$A$7:$A$26,), MATCH(B$1,'Penalty Table'!$A$7:$F$7,)) And several others as well. Please help me before I go crazy. I even took an excel class two terms ago that went over all of this and I got an A, so much for that. Thank you Candie . |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index, Match, Lookup HELP PLEASE!
Thank you for you suggestions, but these two formula's:
FV(rate,nper,pmt,pv,type) and =PV(6%/4,12,,-15000) don't include the compound interest that I am trying to get from my table. the 6%/4 is the 1.5% I am trying to index or look up on the row part of my table and the 12 is the periods of interest I need to look up on my column part of my table and then where the row with 12 and the column with 1..5% intersect is the cell with the final part of the equation which is 0.83639. So then I take my future value of $15,000*0.83639 which is $12,545.85 which is the present value of my problem. Does the PV or FV functions already have that interest table built into some how and I don't need my table and I am wrong in my last paragraph? That would be okay with me since it would be much easier you are absolutely right, but my luck nothing is going to be that easy when it comes to this class and assignment....lol I will try the last suggestion I have received so far later today after school and let you know how it goes. Again thank you everyone and anyone who wants to still give me a sugestion or help with this. "JLatham" wrote: It appears to me that the third example, which probably closely matches the reality of your spreadsheet, should probably work. BUT you need to test the two MATCH portions to see that both of them are working properly. Just pick a couple of cells on the same sheet with that formula and pull out the two MATCH() statements, pop an = symbol into a cell with the MATCH() statement behind them and see which of them is or isn't working. It may be that you have a mismatch in the values in your label row or column. Since the label column (number of periods) is probably integer numbers in both cases, then it's probably not the problem. But the match of the % may not be working because it's actually a percentage on the calculation sheet, but maybe you're just displaying it as a decimal value in A7:F7 on the Penalty Table sheet. To try to explain the possible error another way: on the calculation sheet, you've actually got a percentage, as 1.5% (or .015) while on the table sheet you may have a row with simply numbers representing the percentages, but in fact are whole numbers with a decimal value, as 1.5 2.0 2.25 2.5 and numbers like that won't match: 1.5 = 150% not 1.5% "goodgirlinterrupted" wrote: First I want to say I have read many posts and tried many suggestions on those post before posting my question hoping I wouldn't have to ask a question that has been answered several times already. I am creating an Excel workbook for my business math class. On one page is the actual math problems and on the other two sheets, each one has a table. One table is Present value of $1 and the other is Future Value of a $1. When I try and put a formula in the math problem sheet that is referenced to one of the tables I get #NA or one of the many other error messages. Here is an example of what I need: Math problem is: what is the present value if in 3 years I have $15,000.00 that received 6% interest compounded quarterly. To figure this out I have to find the compound interest and the periods of that interest. To do this I have to take R/T or 6%/4 which is 1.5% and then multiply the years by the compound periods 3*4 which is 12. Once I work this out on my math sheet I need to go to the PVTable which is my present value table and fine the periods which is 12 in the first column, and the compound interest which is 1.5% in the first row and get the table value where the two intersect. Because I need to be able to change the interest and compound periods and priciple or future amounts without having to solve all over again. I thought it was going to be much simpler than it has proven to be but no matter which formula I use I get an error. This was the last few I used from other post suggestions: =INDEX(table range including headers,MATCH(value in column,column range,0),MATCH(row value,row range,0)) =INDEX(your range,MATCH(row value,rows to look in),MATCH(column value,columns to look in)) =INDEX('Penalty Table'!$A$7:$F$26, MATCH($A2,'Penalty Table'!$A$7:$A$26,), MATCH(B$1,'Penalty Table'!$A$7:$F$7,)) And several others as well. Please help me before I go crazy. I even took an excel class two terms ago that went over all of this and I got an A, so much for that. Thank you Candie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup / Index/Match | Excel Worksheet Functions | |||
Lookup, index, match? | Excel Worksheet Functions | |||
Not sure what to use? Lookup / Index / Match etc | Excel Discussion (Misc queries) | |||
Match Index Lookup | Excel Discussion (Misc queries) | |||
MATCH, INDEX, LOOKUP - Help! | Excel Worksheet Functions |