I keep tabs on my league's standings and I would love to make a formula to keep track of record over the last 10 games. However, the part that's stumping me is that the right end of the range consistently increases (the last cell with a value will go from J4 to K4 to L4 and so on). How to create a formula that will auto-update (something like 10 cells from the last entered value) as I add more information to the worksheet? The two values in the cells are "W" and "L". I only need a formula to return the "W" values within the last 10 filled cells from the last filled cell of the row.
I need a formula that will return the minimum value from a column range that flexs without having to manually go in an change row references. There is a blank row between each section of data in order to separate info. As an example:....
Need to formula to flex to include rows 5 thru 7 in the first section but expand to include rows 9 thru 13 in the second section. =MIN(A5.A7) works but how do I get next section to flex to =MIN(A9.A13) without manually changing the cell references? There is always a blank row in between the sections to separate.
In Q3 I have a formula which determines the "next" date from today. In P3 I need to enter a formula which will return the value of the range (P6:P37) which is in the same row but different column as the value calculated in Q3.
Range A1:A10 contains no formulas, only constants.
Only one constant value at most would ever be in that range, sometimes nothing in that range.
The value might be text like "Tom", or it might be a number like "1234".
The value could be in any of the 10 cells.
So far, and this is where the "missing something obvious" part comes in, I have only seen array solutions such as =INDEX(A1:A10,MATCH(TRUE,A1:A10"",0))
My question is, can this result be achieved with a non array / non sumproduct formula.
I need to do this for 6000 cells in a project, that's the design and that's the way it is. I don't want that many arrays in the workbook. I already did this with VBA so I am not asking for a VBA or UDF solution, just hoping to know from the formula wizards what a non-array formula could be that returns the first found (and in this case only-existing) constant value in a range.
I'm building a table (see attached) where I could have a value in cells D6 to P6 that ranges from 0 upwards. In the respective cell beneath each 'value' cell (D7 to P7) I need a formula that will return a figure of 10 if the value in the cell above is 0.4 or below reducing to 0 if the value in the cell above is 1.0 or above.
I have a named range which refers to: =MATCH(LARGE($G$5:$G$68,ROW(G5)-4),G$5:G$68,0)+4. It is supposed to find the largest figure in the adjacent column, list its row in cell H5, then the second largest for cell H6 and so on until the end of the data. The data in column G is dynamic and some cells will contain "0" with certain sections of data thus why I need a formula and cannot just use sort each time.
My problem is that when I use the formula as a named range I get "#N/A" and when I evaluate the formula I will get the answer but in array brackets (e.g.{17}) before the final step turns it to "#N/A". The formula will work fine if inputted directly into the cell but as the same formula will be used over three columns and numerous rows I would much prefer to use a named range.
I have a Training Matrix with "Main", "Leavers" and "Reports" sheets. (can email it but can't upload it here; it's 534kb and I can't make it smaller)
In the "Reports" sheet I need to fill "Site Procedural Training" - "Number of people trained in period".
The periods are : 25/12/11 - 31/03/12 01/04/12 - 30/06/12 01/07/12 - 29/09/12 30/09/12 - 29/12/12
The source of data will be the "Main" and "Leavers". The data are dates in these sheets; each date = 1
The formula needs to look in to "Main" and "Leavers" and if it finds a date which fits in the required period in "Reports" then return it as 1. If it finds 300 dates then returns 300.
I want Excel to see where today's date falls and return how many work days it is from the date boundaries.For example, today is 2/8, which falls between 1/13/2014 and 2/12/2014
But I want to also grab the 2/12/2014 date and get the number of workdays from that- all in one equation.If not one equation then 2 but not separated for each border date like I have above
I am looking for a formula that will enable me to determine which one out of five thresholds a percentage falls within and the retuns a specific value for the corresponding threshold. Here is an example of the data (only showing three thresholds):
Where the values in A1 and B1 are the low and high ends of one range. I would like the formula to determine which range the value falls within and return the value in collumn C (C1 for the A1:B1 range).
I can do this using multiple 'if' statements, but am looking for a much more streamlined way to determine the proper value in column C.
I am trying to find out how many weeks our current inventory will support our sales. I am trying to research formulas that will do this, and coming back with things like OFFSET, MATCH, INDEX but am not certain the best way to put them together to get what I need. I use excel daily, but this is a bit mroe advanced than I am used to
Mini.xlsx
Starting in cell B4, I would like to count how many weeks of Demand can be covered by the specific Available On Hands in row 3 for that week, put the number of "Weeks Covered" into cell B2, and then fill over to the right in row 2. Right now, the values in row 2 are from my own manual calculations, but I would like a dynamic formula that will sum up the values in row 4 up to (but not greater than) the value in B3, give the count of cells that reached that sum (or even better with decimals to show the percentage covered), which I will copy over into B3:B13. Not sure if that makes sense, or if I can explain in a better way. The yellow cells are what I am trying to create a formula for and am currently stuck.
I am working in excel 2010. I have a tracking document that lists free tickets and their expiry dates. In the adjacent columns we track redemption details of these free tickets. What I want to do is return the oldest expiration date from A only if the ticket has not been used (i.e. B is empty). This will allow me to see the date the upcoming tickets about to expire so we can make sure they are used.
A________________B Expiry Date________Redeemed by 15/08/2014 15/02/2014 15/08/2014________John 15/02/2010________Marc 15/02/2011________Bob
I was just recently forced to create my first UDF and after how well it worked I now am very interested in learning more. I am trying to create a function to sort a range by the values in a specific column and return the range. I know this should be really simple but for some reason my code dies whenever it gets to my inner-most loop. I need to use this in a larger function but for now this is my only question. I did find that Excel 2007 has built in Functions for this but my company still uses 2003.
My Public Function SortRange(rngToSort As Range, valCol As Integer) Dim Swapper As Variant Dim i As Integer, _ j As Integer, _ k As Integer
For i = 1 To rngToSort.Rows.Count For j = 1 To rngToSort.Rows.Count - i If rngToSort(j + 1, valCol) < rngToSort(j, valCol) Then For k = 1 To rngToSort.Columns.Count Swapper = rngToSort(j, k) rngToSort(j, k) = rngToSort(j + 1, k) rngToSort(j + 1, k) = Swapper Next k End If Next j Next i SortRange = rngToSort End Function
I need to format a spread sheet to return a value from a range of 4 values (A,B,C,D) by testing a data range against a table that establishes the true value for each separate possible value within the data range. In other words, ItemXXX will have a value that is contained within a table on a separate worksheet within the book. I need to test the value of ItemXXX against the range of values on Sheet B and return the corresponding value from sheet B to the main spread sheet.
I have a lot of data to process on an ongoing project. I need to be able to perform a calculation on the last two numbers on a row including the date the reading was collect. Through the year additional data will be collected and I want the spreadsheet to calculate a value based the last two entered data points for each piece of equipment. how to get the last number in an array but I do not know how to get the second to last number. I included an example.
I have 10 columns and 10 rows of data. Not all of the 100 cells contain data.
I am looking for a formula that will return the value contained in the right most non zero or blank cell
Eg Row 1 = 1, 2, 4, 2, blank, 4, 3, blank, 0, blank In Column 11 I want a formula that returns 3. i.e. it reads accross until it finds a value and returns it.
The target data is not neccesarily unique, the highest or the lowest.
I have a row with many numbers. I would like to write a formula that will identify the the first occurence of a non-zero number. I just need to identify what this first non-zero number is.
I have a range of cells and I want to return the first value, penultimate value and last value. I have a formula to return the first value and another for the final value but I'm struggling to get a formula to work that will return the penultimate value.
If, for example, the range is P4:AD4 then not every cell will necessarily have a value as some cells are blank.
Q1: In the range for the lookup I'm performing, the column with the possible match is the 2nd column. I've tested my function with this range, but it fails (range is $A$x:$K$x, with possible matches residing in column B). If I change the range so the 1st column has the desired data ($B$x:$K$x), it works. Can I modify the function to search using the 2nd column so I don't have to rearrange the columns in my worksheet?
Q2: Can I return an entire rold of data? If yes, how? I only know how to return one cell.
I am trying to create a function in VBA that will return a range, but I don't want the range to point to cells on a spreadsheet. I want the range to return a vba created range. Is this possible or impossible? Is a variable of type range required to point to cells on a spreadsheet?
In worksheet1 I have a set of data. Column A is a value, column B and C are dates. In worksheet2 I have dates in Row 1 going out horizontially. I would like to create a fomula that returns the value in column A in worksheet1, if the date is between column B and C. I couldn't get my array to work. it's been a couple of years since I've wrote any indepth formulas.
I have a list of names in sheet1 column A and 3 more list of names on sheet2, sheet3 and sheet3. What I'm trying to do is find a match for each name on sheet1 column A on the other sheets and depending on which sheet it was found, I need a specific value returned to sheet1 column B.
For example, if A1 was found on sheet2, then on B1 I would need to return the value "Morning" but if it was found on sheet3, then I need the value "Afternoon" and if found on sheet3 I need the value "evening" returned to B1 and then the same for every other name on the list found on sheet1 column A.
What I tried was this.
This goes in Sheet1 B1 (then I would drag it down to search for the rest)
Now the problem with this is that it only works if there is a match on sheet2 but if there is no match, it just returns "N/A" and it does not move on to sheet3 and/or sheet4.
I also tried with vLOOKUP instead of MATCH and the same thing happens.
I also tried this and the only thing I get is "Un-Assigned"
2 Clomun-1: Numbers written serially from 1-10 based on some logic. Column-2: Start from non empty column, increment if next column is empty and increment until a non-empty column found. Column-3: The row where max number need to be written. column-4: Expected max number to be written from column-2.
I can managed to write max number on the same row where column have the max number in the range. As you can see column-2 dynamic size of range need to be used by the max function. I can solve the problem using macro but trying solve using cell formula.
Clomun-1: Numbers written serially from 1-10 based on some logic. Column-2: Start from non empty column, increment if next column is empty and increment until a non-empty column found. Column-3: The row where max number need to be written. column-4: Expected max number to be written from column-2.
I can managed to write max number on the same row where column have the max number in the range. As you can see column-2 dynamic size of range need to be used by the max function.
i have a combobox that uses a named range as the rowsource. does anyone know how I can limit the return list to only the third column of the range? My partial code below is to be used to pick the number of days associated with a particular project type. The range"Project_Durationlbx" has three columns in it. THe first is the Project type and the third is the number of days. can the rowsource of a combobox be written to hold a formula?
If Me.cbxProjType.Value "Non Sourcing Activity" Then Me.cbxNSAWorkDays.RowSource = "Project_Durationlbx"
I have seen a few entries where the rowsource is made dynamic but was unable to adapt the reasoning myself.
