+ Reply to Thread
Results 1 to 5 of 5

Formula switching from IF TRUE to IF FALSE

  1. #1
    Registered User
    Join Date
    03-18-2020
    Location
    Dallas, USA
    MS-Off Ver
    2020
    Posts
    33

    Lightbulb Formula switching from IF TRUE to IF FALSE

    Hi there!

    I am trying to find the average distance of the distances in column A if that trip falls between dates of Period 1. (Please see attached file).
    This is my formula: =AVERAGE(IF(($A:$A>=G18)*($A:$A<=H18), $C:$C)) and it works!

    The issue is when I copy this formula to follow dates of Period 2, then Period 3 etc it provides a 0 as the answer which is incorrect.
    This is my formula: =AVERAGE(IF(($A:$A>=G19)*($A:$A<=H19), $C:$C))

    As you can see the only thing that has changed is cells in column G and H which denotes the dates that I need to average calculation to fall between.

    Please help!
    Attached Files Attached Files
    Last edited by TSACov; 03-18-2020 at 12:52 PM.

  2. #2
    Registered User
    Join Date
    03-18-2020
    Location
    Dallas, USA
    MS-Off Ver
    2020
    Posts
    33

    Re: Formula switching from IF TRUE to IF FALSE

    P.S if you download the file, the formula in question is located in J4 to J15

  3. #3
    Registered User
    Join Date
    06-24-2018
    Location
    India
    MS-Off Ver
    2016
    Posts
    45

    Re: Formula switching from IF TRUE to IF FALSE

    Use below formula: (See attachment for details)

    =AVERAGE(IF(($A:$A>=G18)*($A:$A<=H18), $C:$C)) and Ctrl+Shift+Enter

    Hope it helped you!!
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    03-18-2020
    Location
    Dallas, USA
    MS-Off Ver
    2020
    Posts
    33

    Re: Formula switching from IF TRUE to IF FALSE

    Thank you!

    Can you explain why Ctrl+Shift+Enter works? because the formula itself is exactly the same.

  5. #5
    Registered User
    Join Date
    06-24-2018
    Location
    India
    MS-Off Ver
    2016
    Posts
    45

    Re: Formula switching from IF TRUE to IF FALSE

    You have used array formula, hence it needs be entered with "Ctrl+Shift+Enter"

+ 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. Help with TRUE/FALSE formula
    By mikkelno in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-28-2014, 12:31 PM
  2. What is best to use for helper cells 0 or 1, typed true or false or =true =false
    By Gingermuppet in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-20-2014, 10:41 PM
  3. [SOLVED] in this TRUE OR FALSE function, i want desired name instead of display true or false
    By vengatvj in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-15-2013, 06:44 PM
  4. Display true false with IF formula
    By pcoutlaw in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-22-2012, 09:48 AM
  5. Excel 2007 : If formula using True and False
    By casdaq in forum Excel General
    Replies: 2
    Last Post: 05-06-2011, 03:59 PM
  6. Can TRUE/FALSE be replaced by a formula?
    By BCB in forum Excel General
    Replies: 4
    Last Post: 08-04-2006, 01:29 AM
  7. Replies: 3
    Last Post: 03-15-2006, 11:00 AM

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