Hello,

I am really struggling to work out a formula to perform the following task:

In column A, I have a number, in Column B, I have a number, in Column C, I have the average of Column A and B, in Column D, I have a name.

This is repeated on every row.

Now what I am trying to do is get a return in Column E of the name in Column D if Column C is above a certain number.

To complicate it slightly, if the average in Column D is below the certain number, I don't need the result at all, and all I want to see in my final column of results is the actual results (ie to leave out all blanks).

I've tried so many things and I just can't get it to work! Any help appreciated!

Thank you!  Reply With Quote

2. Good morning,

You can use something to this effect:

In E1: = IF ( C1 < BaselineNumber , "", D1)

The way I normally remove blanks is with a helper column. In this case:

In F1: =IF(E1="","",Row())

This will number each non-blank line with the row it's on (just for sequencing).

Then in G1, or wherever: =INDEX(E:E,MATCH(SMALL(F:F,ROW()),F:F,0)) and drag down. Basically, the first row of the formula will find the smallest value in the helper column; the second will find the 2nd smallest, and so on.

Best of luck,  Reply With Quote

concatenate, range, vlookup 