+ Reply to Thread
Results 1 to 5 of 5

too complex formula

  1. #1
    Registered User
    Join Date
    08-07-2014
    Location
    South Africa
    MS-Off Ver
    2010
    Posts
    24

    Unhappy too complex formula

    I go around in a circle:
    I have a row of headings in the range A1:NA1 where the headings are dates from 1 Jan to 31 Dec, i.e. 365 days
    I want to use COUNTA to count a number of cells in a range that are not empty, i.e. in the row A2:NA2. This range includes a "P" in every cell below the corresponding date that a person was present.
    However, whereas the start of the COUNTA range would be $A$2, I want the end of the range to correspond with the actual system date of the computer. For this I thought to use NOW.
    But, whereas I can find the cell reference for NOW (in row 1), I would need something like OFFSET to find the relevant cell reference in row 2 for the COUNTA function to calculate. If I get this right, then every day the spreadsheet would tell me how many "P" days there were from 1 Jan to the present date - on every new day
    And right here I get lost so much that when I turn around I walk into myself .
    Can a good soul out there please point me in a direction?
    Appreciated.

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: too complex formula

    Try

    =COUNTA(OFFSET(A2,0,0,1,TODAY()-"1/1"+1))

  3. #3
    Registered User
    Join Date
    08-07-2014
    Location
    South Africa
    MS-Off Ver
    2010
    Posts
    24

    Re: too complex formula

    Thanks. It works well.

  4. #4
    Registered User
    Join Date
    08-07-2014
    Location
    South Africa
    MS-Off Ver
    2010
    Posts
    24

    Re: too complex formula

    Ah Jonmo1, one more thing if you'd be so kind please:
    How would you write that formula in text?
    I read A2 is the reference point, "0" because we are not moving up or down, next "0" means what?, "1" I assume to mean width and height, TODAY() for today's date, and what does -"1/1"+1 mean in the formula?
    Thank you kindly.

  5. #5
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: too complex formula

    Quote Originally Posted by Bab1 View Post
    "0" because we are not moving up or down, next "0" means what?
    Because we're not moving Left or Right either.
    The ultimate range resulting from the Offset still begins in the originating cell A2.

    Quote Originally Posted by Bab1 View Post
    "1" I assume to mean width and height
    Actually, the 1 means Height only.

    The "1/1" represents January First
    The +1 means exactly what it looks like it means, Plus 1.

    So TODAY()-"1/1"+1 is representing the Width of the range returned by offset.
    And it translates as Today's Date Minus January 1st Plus 1
    How many days from January first until Today's date.

    Hope that helps.
    Last edited by Jonmo1; 08-08-2014 at 08:36 AM.

+ 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] Use an existing cell formula for creating a new more complex formula
    By dubcap01 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-23-2014, 07:41 AM
  2. [SOLVED] Complex IF Formula
    By tomvh444 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-12-2014, 10:31 AM
  3. Complex IF formula
    By elanum in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-12-2013, 08:12 PM
  4. Replies: 2
    Last Post: 04-04-2012, 01:50 PM
  5. [SOLVED] Complex Formula
    By Max in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 07-11-2006, 09:50 PM

Tags for this Thread

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