I have a list of people and their rates of pay.The names are listed in Column A, The Rate of Pay is in Column D. The rate of pay is the result of a formula and changes often, based on criteria that changes weekly.
At the bottom of the list, I have formulas that display the: Highest Rate of Pay, Second Highest Rate, Average Rate of Pay, Lowest Rate of Pay,and Second Lowest Rate of Pay.
How can I make the Lowest Rate of Pay (D53) ignore the cells that are blank? I used the formula: =SMALL(D2:D46,1) but if any of the cells are empty it just wants to show me 0 instead of displaying the lowest actual dollar amount. (The ISBLANK function and I seldom agree, I am confident I use it wrong and it is decidedly unforgiving)
Also, what would be the easiest way to make Excel show the Name from Column A that matches the result of the Highest and Lowest rates? I have experimented around with "Match" and "Lookup" etc, and the problem I am facing is if 2 people have the same rate of pay I need it to display both names.(Which may not even be possible.)
:thanx: for helping