Hello, all - I've been lurking here for quite some time, and finally have a question that I'm not readily able to find an answer to! As such, I'd like to petition the collective knowledge of the community here for insight and perhaps even a full-fledged solution! The question is this - I'm working on an Excel spreadsheet for my Lieutenant, and a feature that I need to incorporate into it is an ability for the spreadsheet to search a given range of cells (as an example, B8 through AF8) for a value (the number 126, for example), and extrapolate the data from the cell directly above each cell which meets the criteria. I then need the spreadsheet to total up the extrapolated data and output it into a blank cell. I'd also like to have the database output the other range of data I discussed (the '126' example) above the cell containing the total. How would I go about accomplishing all of this? I know this is a rather lengthy and perhaps complex request, but I've exhausted my resources and can think of no better place than here to find a solution! I appreciate whatever feedback you all might be able to provide. Best regards, Bryce

Your query is beyond my abilities but be patient as I would not be surprised if a couple folks here can provide a solution. (I could never get the hang of the 'OFFSET' funtion) I believe it will also need a macro. Bryan

=SUMIF(B8:AF8, 126, B7:AF7) If you don't want hardcoded values in the range, then you probably want to look at the INDIRECT function. (OFFSET might work, though). I'm not sure what you mean by "returning the other range of data." Unless you go with a VB Macro, you would probably need to use either array formulas or VLOOKUP. Here's a good site with info

Although I'm not certain what you mean by extrapolate... This is basic but it might give you some ideas. This would ask for input, then find all the occurences in the selected range for the number you input, total those numbers, plus total the numbers in the cell directly above each occurence. Then place the totals in H1 and H2. (NOTE: You would have to select a range first. You would get an error if the input number was in a cell with nothing above it, A1 for instance) Good luck. Sub extrapolate() Dim searchnum As Integer Dim cel As Range searchnum = InputBox("Enter search number") For Each cel In Selection If searchnum = cel Then totcel = totcel + cel.Value extracel = extracel + cel.Offset(-1, 0).Value End If Next Range("H2") = totcel Range("H1") = extracel End Sub

Thanks, everyone, for your input! In retrospect, I realize that my query was a bit muddled, but nevertheless, the information you've provided me has been of great help. The second point I had brought up is the one still stumping me. I've taken a screenshot of the actual project and highlighted the areas in question so that my follow-up inquiry might make a bit more sense. The image is here: http://www.beagleactiveprobe.com/images/shot.jpg Now, the field you're looking at is the field that was generated by using the code the community provided me - specifically, the =SUMIF(B8:AF8, 126, B7:AF7) code. From this, as you can see in figure 1, I've determined how to calculate the high performer using the =MAX code. Now, what I need to know how to do from there is tell the spreadsheet to use the =MAX code again, and, having determined who the high performer is, go two cells up from their information in the 29th row and get the high performer's name (figre 2). This information would then need to appear in cell B32, as seen in the illustration. Your input is GREATLY appreciated. If there's anything else I can do to further clarify what I'm trying to achieve, please let me know.

Additionally, is there a way to use the 'MAX' function to determine the second-highest number in a range, as well as a way to use the "MIN" function to find the lowest number in a range, ignoring 'zero' (so that if 0.25 was the lowest number, it would pick it up as opposed to 0 or a whole number)?

* Waklthrough the steps: addressWhereMaxIs =CELL("address",INDEX(B29:AE29,MATCH(B33,B29:AE29,0))) The result of the CELL function is a literal text string such as 'B29', so we need to convert the text to a ref with the INDIRECT function. Then, we take the ref and use OFFSET to find the value in the cell two rows above (negative two columns and zero rows offset) =OFFSET(INDIRECT(addressWhereMaxIs), -2, 0) Therefore, the final formula that goes in B32 should be something like this: =OFFSET(INDIRECT(CELL("address",INDEX(B29:AE29,MATCH(B33,B29:AE29,0)))), -2, 0) * Find the second largest number with =LARGE(B29:AE29, 2) * find lowest non-zero value here