+ Reply to Thread
Results 1 to 3 of 3

Change this formula to ignore blanks?

  1. #1
    Forum Contributor
    Join Date
    07-09-2018
    Location
    Germany
    MS-Off Ver
    2016
    Posts
    257

    Change this formula to ignore blanks?

    This formula is intended to calculate the sequence of positive numbers from Q4:Q60, but is calculating Q4:Q60 blanks produced by another formula as a positive number, so instead of counting a sequence of 4positive numbers is counting 36 including blanks. I want to change this formula to ignore blanks from Q4:Q60?

    "=MAX(FREQUENCY(IF(Sheet1!Q4:Q60>0;ROW(Sheet1!Q4:Q60));IF(Sheet1!Q4:Q60<=0;ROW(Sheet1!Q4:Q60))))"

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Change this formula to ignore blanks?

    =MAX(FREQUENCY(IF((Sheet1!Q4:Q60>0)*ISNUMBER(Sheet1!Q4:Q60);ROW(Sheet1!Q4:Q60));IF(Sheet1!Q4:Q60<=0;ROW(Sheet1!Q4:Q60))))

  3. #3
    Forum Contributor
    Join Date
    07-09-2018
    Location
    Germany
    MS-Off Ver
    2016
    Posts
    257

    Re: Change this formula to ignore blanks?

    Hi, the formula worked ,
    Thank you

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Simple IF Formula to Ignore Blanks
    By Gtrtim112 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-04-2018, 01:14 PM
  2. [SOLVED] MIN formula to ignore 0 and blanks
    By D-smoke in forum Excel General
    Replies: 3
    Last Post: 06-05-2018, 08:56 AM
  3. Formula to return unique value and ignore blanks..
    By tangmere.milli in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-23-2015, 10:45 AM
  4. [SOLVED] Formula to ignore blanks and no numeric data
    By ungers in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-05-2014, 04:47 PM
  5. [SOLVED] Formula to Compute Averages - Ignore Zero (Blanks)
    By Neane in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-13-2012, 07:08 PM
  6. Ignore blanks in sumproduct formula
    By Rhyl in forum Excel General
    Replies: 4
    Last Post: 02-02-2012, 11:42 AM
  7. USING IGNORE BLANKS IN FORMULA
    By Roger H. in forum Excel General
    Replies: 5
    Last Post: 04-06-2005, 12:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1