small function

Hello excel experts, I want to grab a list of unit numbers based on criteria in another column. If a row in column G contains "Tax Credit", return the corresponding row value in column B. There is a formula that accomplishes this task: =INDEX(Sheet1!$B$8:$B$86,SMALL(IF("Tax...

Hi guys, I have a task that I need to get the 95th item of a certain product. I need to get it in a database type table. Getting it using a formula array like this is easy task. =SMALL(IF(Product_Range = "Prod1",Value_range),ROUND(COUNTIF(Product_Range,"Prod1")*95,0)) But my question is, can...

Hi All, Thank you for taking time to look over my excel challenge. I have a compact data, which should be extended in a larger sheet. below are both charts. I have given first chart and I would like the second chart to be populated. Prefer formulas, but if it's VBA, that's fine as well...

I have data (on a table aptly named 'Data') for region, department, sub-department and profit/loss. I've made a drop-down list in cells A1, A2, A3, which filter respectively for region, department, and sub-department. Each drop-down list includes an option for no filter - an "All" option. I'd...

I have a list of names along with a list of golfers they bet on. Of the 5 golfers each better picked, I need to count the lowest 3 scores and add them together. I tried two formulas and both aren't giving me the desired result. Just as a test, I'm trying to return the 3'rd smallest RANK based...

So I've gotten the basic search to work: {=INDEX('Combined Data'!$A$2:$C$1000000,SMALL(IF('Combined Data'!$B$2:$B$1000000=$B5,ROW('Combined Data'!$B$2:$B$1000000)),ROW(1:1))-1,3)} Right now, it's looking just at the $B column of the array, and as it's a huge array with up to 30 instances of the...

Hello all you guru's, I'm trying to find a VBA solution to a problem I'm having where I need to find a succession of lowest numbers not lower than 50. Application.WorksheetFunction.Small(ActiveSheet.Range("C3:C" & LastRow), 1) This gives me 2.62 which is indeed the smallest figure in my...

Hello wise ones, I am doing some cohort analysis, and it is my first time trying functions like SMALL, LARGE, AGGREGATE, etc. This is as far as I got, and I'm stuck! I've included a Dropbox link to an Excel file with select data. In summary - (See Screenshot #1) For work, I am doing cohort...

Hi everyone, I have a spreadsheet tracking monthly insurance payments. I want to create a list at the bottom of my spreadsheet that will populate with the individuals names that have not paid for each month. Each person spans across 4 rows. When someone loses coverage, I merge the 4 rows for...

Please take a look at the spreadsheet. 1. I have two arrays (array formula 1 & 2). And a large function which references the two arrays. =LARGE(I4:I5, 2) It does not work at all. :( What should I do? 2. I want to calculate the sum of each section. The name "Check" or "Verify" acts like a...

Hi guys, So I used this function before and it was working fine, however at the moment it is returning all error messages. =IFERROR(SMALL(IF(NameOfRange1 = $AU$28, ROW(NameOfRange1)), ROW(1:1)), "") and then press Shift+Ctrl+Go. Thanks a bunch for your help.

My issue is that I have the function/formula below =small($J$3:$J:9,1) Which I have placed in column K this should iterate the Kth value 1,2,3,4 as the formula is dragged down till it gets to the K9. the values in $J$3:$J$9 are calculated using the function/formula below...

I require a formula to calculate the 5th highest value in the same cell of a large number of workbooks. Each workbook contains a large amount of data and are too big to be combined within one file. I have attempted to achieve this by using the large function with an array such as the following...

I have been working on this for days and have searched and searched. Of the forums where I have found help this has been the best. I have two columns of data: Name: Performance Value: A 1 B 2 C 0 D 2 E 5 F 6 G 3 In another spot I am...