I would like to write a formula that I believe will be a SUMPRODUCT formula with a very complicated string of MATCH and INDEX (I think). I hope this can be written in one formula - but if I need a couple to get there, that would be okay too. I am modifying an existing workbook that currently does the calculation, but it need a separate sheet for each payment - up to 12, but it can't be easily modified for more, and I need to do about 120. I can do limited SUMPRODUCT formulas and can do some limited MATCH formulas, but I can't begin to figure out how I would string this formula together. Hopefully what I need to do is clear with the excel shot below. If not, I could post a sheet that does the calculation for one payment. Thank you for working any magic you can on this.
I will try to explain how this formula needs to work. The formula needs to calculate interest on a payment, from one date to another date using a set of fixed interest rates that are established for each quarter. So this is what the formula needs to do. The workbook has a table (see below) that identifies the quarter - dates from and to, the number of days in the quarter (may be needed in the formula, but maybe the formula would calcualte?) The first and last quarter would most likely not be applied for the full number of days, since the payment would be after the start of a quarter, and the end date may not be at the end of the quarter. It needs to determine if the payment falls in a quarter, how many days then are in that quarter, and apply the base dollar amount for the number of days at the daily interest rate. So it would need to look at all dates and do this calculation and add each of the quarters together until it gets to the "Calculate to date" (cell J5). In the sheet below, I would put the formulas for each paymant in cells K8 to K14.
******** ******************** ************************************************************************>Microsoft Excel - Test.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutD5F5D6F6D7F7D8F8D10F10D11F11D12F12D13F13D15F15D16F16D17F17D18F18D20F20D21F21D22F22D23F23D25F25D26F26D27F27D28F28=BCDEFGHIJK4QUARTER DAYS/QTRRATESDAILY RATE 51/1/20013/31/2001909.0%0.00024658 Calculate interest to: 3/31/2009 64/1/20016/30/2001918.0%0.00021918 PAYMENTDATES INTEREST BASE INTEREST77/1/20019/30/2001927.0%0.00019178 810/1/200112/31/2001927.0%0.00019178 112/2/2001 $ 2,424 9 21/11/2002 2,486 101/1/20023/31/2002906.0%0.00016438 32/17/2002 21,011 114/1/20026/30/2002916.0%0.00016438 43/19/2002 3,827 127/1/20029/30/2002926.0%0.00016438 54/22/2002 54,971 1310/1/200212/31/2002926.0%0.00016438 65/25/2002 1,255 14 76/24/2002 4,883 151/1/20033/31/2003905.0%0.00013699 164/1/20036/30/2003915.0%0.00013699 177/1/20039/30/2003925.0%0.00013699 1810/1/200312/31/2003924.0%0.00010959 19 201/1/20043/31/2004914.0%0.00010929 214/1/20046/30/2004915.0%0.00013661 227/1/20049/30/2004924.0%0.00010929 2310/1/200412/31/2004925.0%0.00013661 24 251/1/20053/31/2005905.0%0.00013699 264/1/20056/30/2005916.0%0.00016438 277/1/20059/30/2005926.0%0.00016438 2810/1/200512/31/2005927.0%0.00019178 Sheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
How to use the Sumproduct with index and match formula and then multipling by a set percentage.
I want to take all amounts in K10:K113 and have them add together based on specific criteria in A1:A113 and in B1:B113 and then multiply the result by a set percentage.
Now I'm trying to improve the functionality of the file attached above.I've attached the file again with another drop-down menu. What I'm trying to do is to have a sum of all the months between the two months indicated in the drop down menu.For example by picking two months from the drop down menus, I want to know the sum of the values between Jan-Mar (Jan, Feb and Mar), or for example Jul-Oct (Jul,Aug,Sep and Oct)...
I've tried to combine SUMPRODUCT with INDEX and MATCH but it doesn't work. I want to build upon this formula:
[Code]....
I forgot to mention that I have multiple strings (column E) which are NOT unique. Basically "VLOOKUP way" won't work, rather it should be "SUMIF way".
In the attached file I've added one additional row (r19), which is the same as r18, just to try how the summing will work.
See attached file, "Rate Sample Index-Match Formula".
I need a formula to return the value at the cross section of two (2) lookup values that match. This formula will be input into column D under, "RATE" on the 1st tab, "TEST FILE".
In the 1st tab, "TEST FILE" there are a series of columns as follows;
A = Service B = From C = To D = Rate
In the 2nd tab, "RATES" there is a series of rates with drivers From (green) & To (blue)
The formula needs to do the following;
1. Lookup the "From" value in column B on tab, "TEST FILE" and match to column B2:B59 on tab, "RATES" both highlighted in green 2. Then Lookup the "To" value in column C on tab, "TEST FILE" and match to row C1:BH1 on tab, "RATES" both highlighted in blue 3. Then return the value at the cross section of the match "From" (point 1 above) & "To" (point 2 above) in range C2:BH59
For Example;
The rate From SYD To CBR = 0.33. I have highlighted this in yellow on both tabs to show where the formula needs to lookup the data to return the answer.
Additionally, if we were to add service as an additional lookup match how would this work?
I currently have a spreadsheet with two sheets. The first is my reporting sheet and the second is called Stores. I currently am using the following formula in the reporting sheet:
Current I am using two spreadsheets for similar press and am combining the two. What I need help with this time is currently in cell c9 the formula is =ROUNDDOWN(17.7/C7,0) or =ROUNDDOWN(39.3/C7,0) depending on which press I am using. The presses are shown in cells d34 - q34. Indigo =17.7 gilboa = 39.3. I have added these numbers to a table in the scales tab a24:e26. Is there a way to write a formula in cell c9 on the input tab to combine the 2 above formulas and insert the correct 17.7 or 39.3 when required?
Guys im stuck on the fact of how to pull the correct information through for a named person.as from the example if i place say steve in the named box i would like all of steves scores to appear in the designated boxes.and so on.
I have attached a formula I have used to extract a value from another sheet (I have also attached where the formula is mean't to extract the value from) . My problem is that this formula is not returning what it should?
I am not sure where the error is in the formula. Also I would like to add one more condition to it.
Another problem- I created Drop Down using Data Validation. Now I need to make the Drop DOWN ICon visible. It becomes visible only when I click on the cell.
I am trying to tidy up a large workbook, and need some help with use of range names in the Match function.
I have the formula working on the attached sample, but wonder whether it's possible to get the Match function Lookup Array to pick itself from the range name.
My spreadsheet has duplicate lookups, which I need to sum. The INDEX MATCH formula will only bring back the first amount in the list, however I need to return the sum of all the matches. I'm assuming I can include a SUMIF, however not sure how to incorporate.
Current Formula Below:
=INDEX('3.5 IFRS Instr Curr YTD in CHF'!$M$27:$CC$400,MATCH('Detail Reconciliation'!$C12,'3.5 IFRS Instr Curr YTD in CHF'!$A$27:$A$400,0),MATCH('Detail Reconciliation'!M12,'3.5 IFRS Instr Curr YTD in CHF'!$M$26:$CC$26,0))
In column B I enter a date when a student attended tutoring. In column C I enter the name of a student attending the tutoring.
In cell D4, I will be entering a student name
In cells D5:D55 I'd like a formula that transposes the dates (if any) that a student attended tutoring. So if a student has their name appear beside the dates ... 14-Feb, 22-Mar, 24-Mar .... then I'd like those three dates to appear in cells D%, D6 and D7.
How to formulate this ... I'm guessing it's going to involve an index-match formula.
Col. A = Article, Col.B = Part Number, Col.C = Location
I am currently using an index & match formula to create a search function. For example if I want to know what article number PLU547 has then I use
=INDEX(A3:A12,MATCH("*"&A19&"*",B3:B12,0))
This use of wildcard is fine until it comes to part number searches such as PLU54 (which is not in the list). In this instance the formula returns the first article number from the list that starts with PLU54. However, part number PLU54 is a code in its own right and I fear that using the wildcard approach needs a modification to limit the returns.
Now this returns the correct value for the first row only, then after that I get the famous #REF! error. Any ideas why it only works for the first row?
a) Column A: 900 serial nos of Work Orders. Each is unique.
b) Column B: Parties responsible for each Work Order no. Certain parties will have more than 1 serial no in Column A.
Note: Column A & B are the main reference.
c) Column C, D & E: List of parties from three different locations performing works base on serial nos in Column A.
I would like a list of serial nos performed by parties in Column C, D & E base on Column A. I can't use the autofilter or sort function on each individual parties as this is very time consuming and moreover the info keeps changing ever 2-3 days. What I require is a simple summary list where I just need to paste the info from Column C, D & E into a special table and it would list down the serial nos from Column A. I had tried the INDEX & MATCH functions but it won't work as I have some parties performing more than 1 serial number. Would it be possible to create a table to perform the said task. These data entry and retrieval has taken minimum 4 hours of my time everyday and it takes longer when certain lists are revised.
I am currently using the following formula and it gives the result P-10, P-10, P-11 below dates that are manually entered. I can not figure out why the date 10/31/09 returns a P-11 value when it matches a P-10 value? All other dates work expcept for the 12 period ending dates. Any help would be greatly appreciated.
Imagine 5 periods of sales, growing by $550 per year, from $250 to $2,250. There is a columnar table with sales in increments of $500 to $2,000 paired with margin percentages.
Using INDEX(MarginColumn,MATCH(Sales,SalesColumn,1)) successfully retrieves the correct margin % for each period where Sales is that year's sales.
So, I tried to construct an array formula in the following way:
Sales is not changing, so that only the 1st year's margin percentage is being applied to each year's sales. I confirmed this by extending the array to a multi-cell layout.
How can I correct the formula so that the margin percentage is looked up as if it were using each year's sales independently? I know I can just SUM the array once this works, but this is my current road block.
P.S. If you see this answer immediately, how might I multiply the percentages by the sales that increase by $500 and sum the whole expression into one cell?
I have the following arrangement PVCu_Woodgrain_DoorF190210300#REF!
PVCu _woodgrain_Door is a drop down list that selects from a number of named ranges.
The Cell that shows (F1) and is called DOORSTYLE with 6 choices F1, F2 ... F6 (I cannot change these to numeric only values)
300 is the 2 previous cells added together. It is called DRSZ1 and will match a numeric value in the named array.
My formula cell with the issue has this formula; =INDEX(INDIRECT(ARRAYDR1),MATCH(DOORSTYLE,INDEX(INDIRECT(ARRAYDR1),0,1)),MATCH(DRSZ1,INDEX(INDIRECT(ARRAYDR1),1,0)))
I know MATCH(DOORSTYLE,INDEX etc. should be a VLOOKUP to find F1 in the named array, but I just cannot get it right.
I am working on a formula that, eventually, I would like to be inserted into cells using VBA's FormulaArray Range function. Unfortunately, in its present state, the formula is too long and clumsy to meet the 255 character limit on that function. While I know that I can easily shorten the current formula with simple adjustments, such as using named ranges, I still am forced to ask whether the design of the formula itself could not be improved.
The heart of the formula is an Index/Match combination which will search if any line on the 'Archive' sheet meets the three criteria on the line specified in the 'Data Log' sheet. If so, it returns the value from a particular column of the matching row on the archive sheet. If not, it returns N/A.
In an effort to circumvent this unsightly N/A result I have attempted to use the ISNA function paired with an IF statement. The idea is that if the result is N/A then "Pending" will display instead. Otherwise the result of the indexing formula will display. This approach is outlined below:
=IF(ISNA(index function), "pending", index function)
The problem, of course, is that the indexing formula is rather long, and, with this approach, I am required to put its entire contents in the formula twice. I would greatly appreciate any help in streamlining this formula. Remember, the final goal is simply to search the 'Archive' sheet for a single line meeting three criteria and return a value from a fourth column, or if not return "pending".
I am trying to compare two fields using the INDEX MATCH formula. One field needs to match exactly and the second field should return the closest match to a date. I have attached an example where I am trying to find the closest production date (column F) for the matching ID that is before the complaint date.
I am working on a spreadsheet where I need to have data from different sheets added but based on the instructor whose data I want to see in the summary sheet. I want to be able to type the name of the instructor in a cell and have all of the data appear below. I believe that an index match formula would work, but I do not know how to have the data from the different sheets added nested in the formula.
I am trying to get an Index-Match formula to search a different sheet depending on the value selected in a particular cell using a dropdown list. See the attached workbook.
That's all fine but some of the items in the list will not be able to match the lookup table and in this case I want it to say 0 (zero), but instead its saying #N/A which I understand means data unavailable. Is there a way of saying if so and so doesn't match put 0 instead of #N/A?
