+ Reply to Thread
Results 1 to 6 of 6

Requesting help with some what-if analysis and counting. :)

  1. #1
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Requesting help with some what-if analysis and counting. :)

    Hi all,

    I hope you're not tired of me just yet.

    I'm attaching an Excel sheet of a problem I'm trying to solve. I have two columns and I want to do a what-if analysis/conditional query based on column 1. What I have is a set of time data ranging from 09:30 to 16:00. I have set this up as intervals with 09:30-10:00, 10-11:00, ..., 15-16:00.

    So, in F5, I want to check how many times when the LOD is between 09:30 and 10:00 the same is true for the HOD:

    LOD <= 10:00 + HOD <= 10:00

    I checked manually and this should be N = 5, I think.

    In G5 I want to check how many times the HOD is between 10-11:00 when the LOD is between 09:30-10:00:

    LOD <= 10:00 + HOD > 10:00 & <= 11:00


    Manual check: N = 33.


    I can see a very cumbersome way to do this by creating helper columns for all these different counts. But I can't seem to set up a formula that counts if A = X & B = Y for the entire set of data.

    Any ideas?

    Thanks in advance for any pointers.

    PS: Not sure why my count didn't add up (809 vs 800).
    Attached Files Attached Files
    Last edited by Elijah; 01-12-2018 at 06:08 PM.

  2. #2
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: Requesting help with some what-if analysis and counting. :)

    A clarification unless it wasn't clear:

    I want to know when what I asked above is true for A5 & B5, A6 & B6, A7 & B7, etc.

    Also, I'm of course not expecting anyone to fill out the sheet. If I could get help with one formula I will do the rest of my self. Can't just wrap myself around how to do this right now. Maybe it's easier than I think.

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Requesting help with some what-if analysis and counting. :)

    Hi,

    Personaly I'd use a DCOUNT function with criteria ranges. See attached.
    The general formula in C6 cioied across is

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I've just shown three resultas so obviously you'll need to add the other time slots and criteria cells in rows 1 & 2
    Attached Files Attached Files
    Last edited by Richard Buttrey; 01-12-2018 at 08:13 PM.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: Requesting help with some what-if analysis and counting. :)

    Hi, Richard,

    Many thanks. I have to say it went a little bit over my head, but I'm having a second look at it now and trying to wrap my head around it.

    Elijah

  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Requesting help with some what-if analysis and counting. :)

    If you struggle with it and can't get it in your head then let me know.

  6. #6
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: Requesting help with some what-if analysis and counting. :)

    Appreciate it, Richard, but I think doing a count on my other spreadsheet will serve the same purpose. If not, I'll let you know. : )

+ 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] Requesting help regarding IF function
    By venu619 in forum Excel General
    Replies: 2
    Last Post: 12-25-2015, 12:11 PM
  2. Replies: 1
    Last Post: 03-06-2014, 11:00 AM
  3. Data Analysis- advanced pivot functions for employee analysis
    By Dsankie in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 05-10-2013, 12:30 AM
  4. Help In Requesting Data
    By pasteis in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-03-2012, 01:14 PM
  5. Requesting Help with Macro
    By Endlin3 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-08-2010, 06:39 PM
  6. Requesting a simple counting function, pretty urgent (VBA)
    By ssdt in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-18-2008, 08:47 PM
  7. [SOLVED] Analysis ToolPak installed but no Data Analysis option
    By Eric Stephens in forum Excel General
    Replies: 3
    Last Post: 02-02-2005, 06: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