Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
complex formula does something simple?
{=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... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
complex formula does something simple?
Doesn't this formula essentially count the blanks in M1:M141
and compare that number to the number of rows between E101 and E141? 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 -- Biff Microsoft Excel MVP "Dave F" wrote in message ups.com... {=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... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
complex formula does something simple?
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))) "Dave F" wrote: {=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... |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
complex formula does something simple?
Ooops! Made a mistake:
This non-array formula will do the same thing: =IF(COUNTIF(M1:M141,"=")=40,0,COUNTIF(M1:M141,"=" )) Should be: =IF(COUNTIF(M1:M141,"=")=39,0,COUNTIF(M1:M141,"=") ) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Doesn't this formula essentially count the blanks in M1:M141 and compare that number to the number of rows between E101 and E141? 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 -- Biff Microsoft Excel MVP "Dave F" wrote in message ups.com... {=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... |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
complex formula does something simple?
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 On Jul 31, 5:04 pm, "T. Valko" wrote: Doesn't this formula essentially count the blanks in M1:M141 and compare that number to the number of rows between E101 and E141? 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 -- Biff Microsoft Excel MVP "Dave F" wrote in message ups.com... {=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...- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
complex formula does something simple?
You're welcome!
-- Biff Microsoft Excel MVP "Dave F" wrote in message ups.com... 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 On Jul 31, 5:04 pm, "T. Valko" wrote: Doesn't this formula essentially count the blanks in M1:M141 and compare that number to the number of rows between E101 and E141? 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 -- Biff Microsoft Excel MVP "Dave F" wrote in message ups.com... {=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...- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
complex & simple database on a single form | Excel Discussion (Misc queries) | |||
Complex If/Then formula? | Excel Discussion (Misc queries) | |||
Complex Formula | Excel Worksheet Functions | |||
Simple, yet complex problem! Using results as new data during calculations? | Excel Worksheet Functions | |||
I Need Help with complex formula ? | Excel Worksheet Functions |