Keeping cells waiting for new data to not be read as zero or be ranked.

[Carlos90]

I am trying to rank a series of data. I have most of this figured out. However, the data that is being ranked is based on a sum of a range of cells. I have lines that are waiting for new information to be put in related to the values to be ranked and the value is reference a sum of blank cells currently since there is no data there and it is showing up as zero and therefore being ranked number 1 in my list. I basically want the ranking to rank the values 1-11 while putting all values that are zero to be put at the bottom of the ranking. Please let me know if there is a way to do this without macros. I have been using the VLOOKUP and RANK functions for my ranking purposes.

I have tried using an if statement that made the cells #N/A if they were zero, however they remained at the top of my ranking.

Thank you.

[JBeaucaire]

1) With a formula like this: =SUM(A2:I2)
...that could possibly result in 0 and you want that row ignored, change that formula to:

=IF(SUM(A2:I2)=0, "", SUM(A2:I2))

This will put a NULL string into the cell instead of a number. Strings are not counted in a RANK formula.

Let's say that formula went into J2.

2) Then assuming a rank formula that looked like this: =RANK(J2, $J$2:$J$6)

...you will get a #VALUE error in cells that have strings from the formula above. So you test for that first:

=IF(ISNUMBER(J2), RANK(J2, $J$2:$J$6), "")



Since you didn't post a sample workbook you'll have to adapt all that to your own formulas/columns/rows/cells.

[Carlos90]

Thank you! That is exactly what I wanted. I was wondering if there was a way to add a number to that formula. For instance for a hours scheduling program, a new person comes in with 80 hours of overtime, however the program only reads what it is put into it once they start working the new job. I want to be able to add their hours in to be considered in the ranking without deleting that formula.

I tried simply doing =IF(SUM(A2:I2)=0, "", SUM(A2:I2))+5, but that did not work since you probably can't add numbers to strings. Any suggestions?

[JBeaucaire]

You add the +5 to the inner SUM formula, not to the whole thing.
=IF(SUM(A2:I2)=0, "", SUM(A2:I2)+5)

If that resolves the original question, select Thread Tools from the links above to mark the thread as SOLVED. Thanks.

[Carlos90]

Sorry for the confusion, I mean that if there is no values in Cells A2:A12 and I want to add 5 towards what is being counted once values are put in that range. Basically if someone has overtime hours before being entered into my spreadsheet how can I input those values without eliminating =IF(SUM(A2:I2)=0, "", SUM(A2:I2)).

Basically, it is true that the value should be "" but the person originally has 5 hours but it is just not in the spreadsheet because it was during a previous week not recorded on the spreadsheet.

your solution simply adds to the values that are recorded in A2:A12, but I need the 5 to come up before any values are put into A2:A12.

[JBeaucaire]

Where in row 2 will "prior overtime hours" be stored?

[Carlos90]

I would like the prior overtime hours to be within the cell that has the =IF(SUM(A2:I2)=0, "", SUM(A2:I2)), that way the user can add the prior overtime hours without deleting the formula to total the hours that are going to be inputted.

[JBeaucaire]

In that case the original formula is all you need.