1

Suppose I have a table with 50 rows and 50 columns. Each row has a certain number of blank cells, often occurring in the last few columns, with the first few columns being relatively dense. I would like to find a way to count how many columns in a row, starting from the last column, are blank, ending the count with the first non-blank cell encountered. I would like to use this count to rank rows of the table. Is there an easy way to do this with formulas alone?

  • @Fixer1234 Thanks for that comment, I edited my question to clarify what I meant. You guessed correctly though! – Max Power Oct 29 '14 at 17:17
  • This appears to be a duplicate of this question: superuser.com/questions/279758/… (that one didn't receive a correct answer). – fixer1234 Oct 29 '14 at 17:20
  • @fixer1234 Is it still a duplicate if no correct answer was received? – Max Power Oct 29 '14 at 17:26
  • see here – Raystafarian Oct 29 '14 at 17:29
  • @Raystafarian Thanks. I tried that but received only #N/A errors though. I also tried the second solution with the same result. Is there anything I'll need to modify aside from the columns targeted? – Max Power Oct 29 '14 at 17:37
0

You can do this using an array formula. Say The 50 columns of data are in columns A through AX, row 2 is your first data row, and the formula is going into AZ2:

    =50-MAX(IF(NOT(ISBLANK(A2:AX2)),COLUMN(A2:AX2),0))

The formula needs to be entered using Ctrl-Shift-Enter so it goes in as an array formula. Once it is entered, you can copy and paste 49 more copies down column AZ. The result will be the number of consecutive blanks at the end of each row (50 minus the column of the last non-blank entry). I tested it and it works.

|improve this answer|||||
  • Thanks, I'll give this a try! EDIT: I receive an error when pasting it as an array formula or a regular formula. – Max Power Oct 29 '14 at 17:58
  • Have you had a chance to look over the issue? – Max Power Oct 29 '14 at 19:03
  • What type of error? I actually tested it in LibreOffice Calc. Not everything available in Excel is included in Calc, but the functions that are work the same way. I didn't get any error. – fixer1234 Oct 29 '14 at 19:13
  • I get a #NAME error. – Max Power Oct 29 '14 at 19:18
  • I just tried it in Excel. The problem is that I typed some extra spaces for readability. If you just copied and pasted the formula, those spaces will cause an error. I'll edit the answer to remove the extra spaces. – fixer1234 Oct 29 '14 at 19:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.