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

1. ## 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).

2. ## 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. ## 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:

I've just shown three resultas so obviously you'll need to add the other time slots and criteria cells in rows 1 & 2

4. ## 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. ## 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. ## 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. : )

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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