At work, I created a single-page sp 2

[lefty78312]

At work, I created a single-page spreadsheet with a sumproduct formula to count data that meets certain criteria and how many sales result from the data that meets the criteria. I also notate the time of each data occurrence. Now I just need to break the relevant data into time periods, arbitrarily every 2 hours, with the ability to change it to other time periods; say, 4 hours, if need be.

In other words, I want to be able to track how many occurrences of each type of event occur within a certain time period, e.g., 8:00-10:00, 10:01-12:00, 12:01-2:00, etc., and how many sales occur during that same period. I can then easily track my closing percentage by time period. BTW, since I do the entire month on one spreadsheet, the times are not in chronological order.

I tried a Pivot Table, but I don't understand it well-enough to do it properly. I'm sure a modified sumproduct formula would do the trick; I just don't know how to formulate the time periods so Excel will know what I'm asking. All I end up with is error messages.

Thank you in advance.

 

[BlueHorizon]

Hi,
I understand you don't get Pivot Tables but they may be the answer in your case, especially since you can group times and dates together to accomplish your occurence tracking.
Wish I could help further,


Best,
Blue Horizon

 

[lefty78312]

Thank you, BlueHorizon. The problem I have with Pivot Tables is I don't know which field to drag where. I did try it a couple of times, and it just breaks my calls down by time; e.g., it will list each individual call by the time it was received, and if there's more than one that come in at the same time (on a different day, of course), it will list 2 (or more) for that exact time. That might be helpful for some things, but it's not what I'm trying to do.

I'd rather just modify the sumproduct formula I'm currently using, since 2/3 of it is already written, but I'd be happy to stay with the Pivot Table if there's a way to make it do what I need.

Thank you again.

 

[SkipVought]

Post a sample of your source data, AND the result you want to see from that sample.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[lefty78312]

Thank you, Skip. I don't know how to post an actual spreadsheet, so let me try this.

Current columns (truncated for brevity)

Criteria | Time | Name | Phone | No. of items ordered

There are 4 different criteria, which we can call criteria1, criteria2, etc. The relevant columns, of course, are 'Criteria', 'Time', and 'No. of items ordered'.

My current sumproduct formula to calculate the 'Criteria' and 'No. of items ordered' columns is:

=SUMPRODUCT((A3:A450="criteria1")*(G3:G450))
=SUMPRODUCT((A3:A450="criteria2")*(G3:G450))
etc.

Where 'No. of items ordered' is in column G. Ideally, I'd like to modify my current sumproduct formula to include time periods, as mentioned above, so I can get a self-updating formula that calculates:

a. How many calls I'm taking per time period, by criteria
b. How many items I'm selling from those calls during the
same time period, by criteria
c. My closing percentage per time period, by criteria

If I can get a and b, 3 will be no problem, of course. I have nothing against Pivot Tables, but when I tried them, I just ended up with hundreds of columns, which is useless to me.

Any help you can offer will be greatly appreciated.

Thank you.

 

[SkipVought]

a. How many calls I'm taking per time period, by criteria

In the PivotTable Wizard - LAYOUT

Drag Criteria to ROW
Drag Time to COLUMN
Drag Criteria to DATA as Count of Criteria

What is the Time period? In the PT, Right-click the Time heading, select Groug & show detail > GROUP and select the time interval you want.

b. How many items I'm selling from those calls during the
same time period, by criteria

In the PivotTable Wizard - LAYOUT

Drag Criteria to ROW
Drag Time to COLUMN
Drag [No of iterms ordered] to DATA

c. My closing percentage per time period, by criteria

I don't know what you mean by "closing percentage"

You ought to be able to do a PivotTable report like each of these in a matter of SECONDS.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[lefty78312]

Thank you again, Skip.

In the PT, Right-click the Time heading, select Groug & show detail > GROUP and select the time interval you want.

When I do that, Excel lists my individual times in several rows, which run out to column IV and stop at 5:13 PM. Over 15%+ of my calls are taken after that time. Additionally, when I right-click the Time heading, select Group & show detail, then GROUP, I get a message that says 'Cannot group that selection'. I think it's summing the number of items sold, but not counting the number of calls taken by criteria, which would be in column B. I.e., I want Excel to tell me how many calls taken per time period, by criteria, and how many items sold per same time period, by criteria.

Thank you again for your assistance. I may learn something.

 

[SkipVought]

"When I do that, Excel lists my individual times in several rows"

Not if you dragged the TIME field into the COLUMN area.

"Additionally, when I right-click the Time heading, select Group & show detail, then GROUP, I get a message that says 'Cannot group that selection'. "

That's because EITHER the values you have entered are not REALLY time values OR there is not a time value in EVERY ROW in your source table.

faq68-5827

"I think it's summing the number of items sold, but not counting the number of calls taken by criteria"

That is TOTALLY under your control, as you can select COUNT or SUM of whatever is in the DATA area.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[lefty78312]

Skip, thank you so much for hanging in there with me.

Not if you dragged the TIME field into the COLUMN area.

I did drag the time field to the column area.

That's because EITHER the values you have entered are not REALLY time values OR there is not a time value in EVERY ROW in your source table.

Now that could be the problem. Rather than have a date column, I separate the calls for each day by a blank space, so some rows are blank. And even though I specified the entire range in the pivot table, I know pivot tables don't like blank rows. That's why I was hoping to use sumproduct. I just don't know how to write the time ranges in a formula that Excel will recognize.

 

[SkipVought]

"I separate the calls for each day by a blank space, so some rows are blank. "

This is a HUGE mistake for ANY table functionality. HUGE!!!

Get rid of ALL you empty rows. A table is not a table if it has empty rows or columns. If you want to emphasize changes in certain data values, you can use any number of techniques that will not destroy the integrety of the table.

If a value LOOKS like Time, you can verify if it a real time value by temporarily changing the cell format to GENERAL. A Time Value be be a fractional NUMBER.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[lefty78312]

"I separate the calls for each day by a blank space, so some rows are blank. "

This is a HUGE mistake for ANY table functionality. HUGE!!!

Sumproduct doesn't care how many blank spaces I have, and that's why I use it, and why pivot tables don't work for me.

Thank you again for a valiant effort.

 

[IknowMe]

lefty78312, why limit yourself? The suggestion Skip's making opens a whole lotta doors that provide a whole lotta convienence.

[small]Sometimes you gotta leave your zone of safety. You have to manufacture Inspirado. You gotta get out of the apartment. You've got to run with the wolves. You've got to dive into the ocean and fight with the sharks. Or just treat yourself to a delicious hot fudge sundae........ with nuts. - Jack Black[/small]

 

[lefty78312]

If I could just do some sort of count formula (countif?) of the times in column B by time period, I'd have this thing licked. I just don't know how to ask Excel if a call took place within a certain time period; e.g., at or after 2:01 PM and at or before 4:00 PM. I can write the rest of the formula myself if I can figure out how to extract that info from the spreadsheet.

 

[lefty78312]

The suggestion Skip's making opens a whole lotta doors that provide a whole lotta convienence.

I've learned a lot since I first posted yesterday, and I'm very appreciative for the assistance offered. I probably will use pivot tables for other things in the future. They just won't work for this particular project the way I have it formatted.

 

[SkipVought]

Sumproduct can work in many cases, BUT...

it is VERY resource intensive.

In cases where I have NOT used a PivotTable (which, BTW, is not the end-all reporting tool, but just another powerful tool in My toolbox), and where SUMPRODUCT sucked resources, I have used DSUM or DCOUNT etc.

But in cases when your source data changes regularly, resulting in an expanding/shrinking report, a PivotTable is usually preferred.

In either case, empty rows/columns in tables is a novice spreadsheet user mistake that experienced users avoid like the plague, since it severely LIMITS the viable choices for data analysis and reporting.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[IknowMe]

If I could just do some sort of count formula (countif?) of the times in column B by time period, I'd have this thing licked. I just don't know how to ask Excel if a call took place within a certain time period; e.g., at or after 2:01 PM and at or before 4:00 PM. I can write the rest of the formula myself if I can figure out how to extract that info from the spreadsheet.

http://en.allexperts.com/q/Excel-1059/Count-times-1.htm

[small]Sometimes you gotta leave your zone of safety. You have to manufacture Inspirado. You gotta get out of the apartment. You've got to run with the wolves. You've got to dive into the ocean and fight with the sharks. Or just treat yourself to a delicious hot fudge sundae........ with nuts. - Jack Black[/small]

 

[lefty78312]

First of all, thank you very much to Skip for opening my eyes to pivot tables. I will get more familiar with them and incorporate them into what I do in the future.

Thank you also to Mr. Milson for showing me where to find that formula. I knew there had to be a way to do it; I just didn't know how.

Thank you both. I hope to continue reading your posts on other subjects and learning from both of you.

 

[yogia]

Hi lefty78312:

One way would be to consider using Excel's DataTable feature for this using DSUM and DCOUNT/DCOUNTA functions for the Sale_Amount, and Number_of_Sales between certain set of hours.

If you need to discuss this further, or if you would like to see an example, please post back and then let us take it from there.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[lefty78312]

One way would be to consider using Excel's DataTable feature for this using DSUM and DCOUNT/DCOUNTA functions for the Sale_Amount, and Number_of_Sales between certain set of hours.

If you need to discuss this further, or if you would like to see an example, please post back and then let us take it from there.

Thank you and Yes! Yes! Yes! I love these problems because they afford me the opportunity to learn more about Excel and grow. I'll stay on it until I get an answer that works because I really can use the solutions.

 

[yogia]

Hi lefty78312:

Let me see if I have understood you correctly in the following solution using EXCEL's DataTable feature ...

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com