complex formula does something simple?

  • Thread starter Thread starter Dave F
  • Start date Start date
D

Dave F

{=IF(SUM(--ISBLANK(M1:M141))-(ABS(ROW(E101)-ROW(E141))-1)<>0,SUM(--
ISBLANK(M1:M141)),0)}

(Entered as an array formula.)

If the count of blank cells in M1:M141 minus (141-101) <> 0, then
count the blanks in M1:M141, else enter 0.

Doesn't this formula essentially count the blanks in M1:M141 and
compare that number to the number of rows between E101 and E141?

Found in a workbook I'm auditing...
 
Doesn't this formula essentially count the blanks in M1:M141
and compare that number to the number of rows between
E101 and E141?
Click to expand...

Essentially, yes, but it's overly complex. It counts empty cells, not
necessarily "blank" cells. For example, it doesn't count cells that contain
formula blanks (""). This non-array formula will do the same thing:

=IF(COUNTIF(M1:M141,"=")=40,0,COUNTIF(M1:M141,"="))

I can't figure out why, in the original formula, they're subtracting 1:

ABS(ROW(E101)-ROW(E141))-1
 
don't forget the -1 after the rows and dont forget that is blank counts "" as
not blank
and that the sum(--isblank()) will not give the same answer as countblank()
for the same range if "" is present

about the same as
=if(sum(--isblank(m1:M141))<>41,sum(--isblank(M1:M141)),0)
or
=if(sum(--isblank(m1:M141))=41,0,sum(--isblank(M1:M141)))
 
Ooops! Made a mistake:
This non-array formula will do the same thing:
=IF(COUNTIF(M1:M141,"=")=40,0,COUNTIF(M1:M141,"="))
Click to expand...

Should be:

=IF(COUNTIF(M1:M141,"=")=39,0,COUNTIF(M1:M141,"="))
 
Agreed it's overly complex. I more or less re-worked it to what you
suggest. The workbook was dumped in my lap by my boss: "This makes no
sense, review it and make it make sense."

Got to love that.

Thanks for the feedback.

Dave
 
You're welcome!

--
Biff
Microsoft Excel MVP


Dave F said:
Agreed it's overly complex. I more or less re-worked it to what you
suggest. The workbook was dumped in my lap by my boss: "This makes no
sense, review it and make it make sense."

Got to love that.

Thanks for the feedback.

Dave
Click to expand...
 
Back
Top