+ Reply to Thread
Results 1 to 4 of 4

Help with functions to make cells calculate data or show blank cell based on other cells

  1. #1
    Registered User
    Join Date
    05-12-2017
    Location
    Indianapolis, IN
    MS-Off Ver
    2016
    Posts
    4

    Help with functions to make cells calculate data or show blank cell based on other cells

    Hello! First time poster here, but it appears that this may be a great resource to help me with my Excel woes!

    So, I am creating a spreadsheet to track events and averages of the days between those events. What I would like to do is make it so that data (average days between events) is calculated from one date to another if the person is a new intake, and also have the option in the same row for the average amount of days to be calculated if the person is not a new intake but starts with the IES [L]. This means that I will have to have IF statements involved and I do not really know what I am doing. My issue is that there are wacky numbers that come up if data is put in one box, but not another. I have it so that, if the person entering data responds "no" to whether or not the client is a new intake [D], you cannot put any information in the intake date cell [K]. But, when I put in the formula to make it so that crazy numbers aren't generated in the average days between dates cell [M].

    I would like it so that, when column D has "yes", a date can be put into column K without anything in column L and it not put in a crazy number. The issue with this can be seen in Row 12.

    I also would like it so that, when column D has "no", a date can be put into column L without anything in column K and it also not put in a crazy number. This issue can be seen in Row 11.

    I have two different "IF" statements in column M in rows 11 & 12, but I would like to combine those if statements so the crazy numbers aren't generated with either option that is chosen.

    Finally, I would like it to calculate in column U the average days between column K to R. This I can do but I separated the average date function for the other option for column V. Then in column W, a number comes out which is calculated to a total average number at the bottom of the spreadsheet. The issue with this is that I only want a number to calculate in EITHER column U or column V, NOT BOTH. Therefore, IF there is a date in column K, I would like it to calculate the average days between dates in columns K & R in column U, while leaving column V blank. Transversely, IF there is NOT a date in column K, I would like it to calculate the average days between dates in columns L & R, while leaving column U blank.

    I know this is a lot, but if someone could please provide me some guidance, I will be forever grateful!!!

    Please let me know if you have any suggestions or questions! I've attached the spreadsheet here.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    05-12-2017
    Location
    Indianapolis, IN
    MS-Off Ver
    2016
    Posts
    4

    Re: Help with functions to make cells calculate data or show blank cell based on other cel

    Perhaps a bit better explained, the first part of what I would like to do is combine these two statements into one cell:

    =IF(ISBLANK(L9)," ",DAYS(L9,K9))

    =IF(ISBLANK(K9)," ",DAYS(L9,K9))

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Help with functions to make cells calculate data or show blank cell based on other cel

    Try this:

    =IF(OR(ISBLANK(L9),ISBLANK(K9)),"",DAYS(L9,K9))

    Note that you do not need the space between "".

    Hope this helps.

    Pete

  4. #4
    Registered User
    Join Date
    05-12-2017
    Location
    Indianapolis, IN
    MS-Off Ver
    2016
    Posts
    4

    Re: Help with functions to make cells calculate data or show blank cell based on other cel

    Quote Originally Posted by Pete_UK View Post
    Try this:

    =IF(OR(ISBLANK(L9),ISBLANK(K9)),"",DAYS(L9,K9))

    Note that you do not need the space between "".

    Hope this helps.

    Pete

    GENIUS!!!! Thank you so much! That worked just as I needed it to.

    Any thoughts on the second part of my initial inquiry?

+ 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. Replies: 1
    Last Post: 07-22-2016, 04:03 PM
  2. How to make cells show blank based the cell to the left
    By ScottKiwi in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-18-2015, 03:57 PM
  3. [SOLVED] Make Sum Calculate BLANK cells as 0
    By Extreme184X in forum Excel General
    Replies: 4
    Last Post: 03-30-2015, 05:47 PM
  4. Replies: 6
    Last Post: 07-10-2014, 05:08 PM
  5. [SOLVED] Using = to another sheet, how to make blank cells show nothing
    By simple? in forum Excel General
    Replies: 3
    Last Post: 08-15-2013, 11:06 AM
  6. Using nested functions to calculate totals based on labels in adjacent cells
    By Long Tom Coffin in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-02-2012, 03:20 PM
  7. Find a blank cell and calculate the average of all cells above till the next blank
    By joshnathan in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-27-2012, 11:51 AM

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