Formulas and Functions

Archives for July 2018

Discussion List

  • unexpected behavior with collect formula
    In Formulas and Functions
    I believe this formula should work, and when I break it apart it seems to work but doesn't when together.   Copy from sheet =JOIN(COLLECT({Range 1}, {Range 2}, IF($[By Shift]$1 = "All", @cell = @cell, @cell = $[By Shift]$1), {…
    116 7
    116 views 7 comments Most recent by L_123 Formulas and Functions
  • formulating dates excluding weekends
    In Formulas and Functions
    I have 3 columns with different dates. My goal is to enter 1 date, and have the other 2 dates formulated based off the 1st date. I created a formula to satisfy this need, but I am ending up with some dates falling on weekends, which I cann…
    25 2
    25 views 2 comments Most recent by Maz Uddin Formulas and Functions
  • Formulas
    In Formulas and Functions
    I'm trying to move an excel project timeline to the Gaant template.  I currently calculate my dates backwards from the desired in stock date.  I've put in my duration for each step, but wanted to know how i can set aformula to deduct the d…
    1 2
    1 view 2 comments Most recent by J. Craig Williams Formulas and Functions
  • Countifs + find formula
    In Formulas and Functions
    I have a formula I'm having an issue with.   I'm trying to count how many projects are assigned to a individual in the "Assigned To" column that contains a partial description "green Ties" in the "task Name" c…
    454 11
    454 views 11 comments Most recent by J. Craig Williams Formulas and Functions
  • Ignoring blanks in Countifs
    In Formulas and Functions
    I am trying to create a formula to look at multiple columns and count if they meet a specific criteria.  I have a formula that works when there is data in the sheet but it causes an error whenever data is missing.  I need to create a templ…
    1012 8
    1K views 8 comments Most recent by Mike Wilday Formulas and Functions
  • collect with conditional statement to return all values
    In Formulas and Functions
    I am attempting to use a collect with no criteria, that is it grabs everything and am having some problems.   =JOIN(COLLECT(Return:Return, a:a, IF(ISBLANK(a10), true)))   my desired behavior is that if cell A10 is blank, all of the val…
    146 2
    146 views 2 comments Most recent by L_123 Formulas and Functions
  • Nested IF with COUNTIF
    In Formulas and Functions
    I need to use a nested IF with COUNTIF.  Is that possible?  The scenario is that I need to count the number of “Red,” “Yellow,” and “Green” balls that are in a particular row in another sheet.  Then,   * If count of “Red”>=1, return “…
    8847 3
    8.8K views 3 comments Most recent by jparada55691 Formulas and Functions
  • countif
    In Formulas and Functions
    Hello,  I have a formula that I'm attempting to count the number of projects within the Task Name column that have "hot CX" as part of the name. Task name is customer- Hot CX  My formula returns a 0 count.   What changes do I n…
    11 6
    11 views 6 comments Most recent by Mike Wilday Formulas and Functions
  • Index(Collect add to formula page
    In Formulas and Functions
    Can we get an index(collect example added to the collect formula page? I have found it extremely useful in finding specific iterations of data, and I only stumbled upon it trying random things to solve a specific issue I had a while back. …
    11 1
    11 views 1 comment Most recent by Shaine Greenwood Formulas and Functions
  • Date Calculation
    In Formulas and Functions
    =IFERROR(NETWORKDAYS([NAI Project Start Date]7, [NAI Project End Date]7), 0)   Cannot get this to calculate    The first field has =Another_Cell but the property is set to date.  Is this the problem?   Im testing the date in the C1 f…
    1 3
    1 view 3 comments Most recent by J. Craig Williams Formulas and Functions