I’m trying to set up a spreadsheet to keep track of my tips by shift, day week, month and year.

Topic

New Reply

Along with an average by day of the week at a minimum. If I could sort it by the parameters in the Title it would be great. It would be nice to have an overview of what the cash to credit card ratio is. I’ve figured out how to sum the data by day using SUMIF but I’m having trouble with the rest. Nothing is sacred on the sheet I’m open to anything. I’ve attached a copy.

Thanks!

Reply | Quote

Replies

Hi Check Marc,

I know someone here will be able to help with the solution but I would suggest using Excel’s Pivot Table (PT) feature. It will take care of all/most of what you asked about.

A PT is similar to what you did on Sheet1. Except Excel figures out what formulas to use based on the layout of your PT (which is part of the dialog for setting up a PT) and what stats you want in the body of the table. You decide what your rows and cols are during the PT setup also. And while you can’t truly do a 3-deimension PT (for example, AM vs PM, day of week, month), you can tell Excel to have sub rows. For example, you can have a PT row be a month and within that row have each month show 7 sub-rows for the day of the week within the month. The cols could be AM and PM. The body of the table could show the number of times (“counts”) you worked an AM shift on a Tuesday in February and all such combinations. Excel will also add an extra col/row for row/col totals and sub-totals. It can get a bit messy. But the nice thing is that Excel figures out all the formulas it needs to give you want you define in the PT setup dialog.

To do the things I mentioned above (day of week, month), you do need to break apart your dates in col A as you did in cols J and L of your Daily Tips sheet. But see below.

That said, I’ll say that I hardly use PT, which is actually a bad thing. I would approach the problem similar to you. Then I think – couldn’t I have done this with a PT. I give it a try and it’s done.

For one thing, I don’t know how to get a PT to do much other than give me counts of how many times you worked on a Monday, a Tuesday, etc. So if you want to know the average tip on a Monday vs a Tuesday, that is something I would struggle with. Similar for the credit to cash ratio.

It may also be the case that a PT is over-kill for your problem because all you may really want is the last col/row of the PT where Excel gives you row/col totals.

For another thing, I also have trouble refreshing the PT w/o lots of formatting work if I’ve added some of my own formatting to Excel’s formatting while it’s creating the PT. I understand this can be done. The reason you’d want to refresh the PT is that you’ve added more rows to your tips.

So with that said, a couple of tips (pun intended) for your Daily Tips sheet where you have your data:
– in I2 (and down the col), you have =sum(a2). Not sure you need the sum part; =a2 should suffice.
– same for col K
– in cols J and L, you have CHOOSE formulas. I understand the purpose in terms of getting stats so you can look at your tips by day and month. But since you’re referring to col A, I’m not sure why you have col I.
– For getting the day and month, another approach would be to just use =a2 (and down the column). Then use Excel’s Custom Format (the last category under the Number tab in Format Cells). I see you used Custom Format for the year, so it looks like you know about this. If you want to see the day of week, enter either “ddd” or “dddd” w/o quotes in the Type box (near the top) in the Format Cells-Number dialog. The former will give you a 3-letter version of the week day while the latter will give you the full week day. Similarly, using mmm or mmmm for month.

So I’ll be watching this thread to see how others approach the solution.

Fred

Reply | Quote

Thanks most of the additional columns were how do I’s. The one glaring problem is using sum to return a m/d/y works as long as there’s something in the first column. If not it returns Saturday, January 00, 1900 as the date for all the blank cells in A which makes a mess of the Saturday numbers

Reply | Quote

Hi Check Marc

I waited to see if there were other responses before posting this example. All I really did was to follow Fred’s suggestion and create a couple pivot tables as examples. The first one has a calculated field to give the cash to credit ratio. The second pivot table is just another example of how you can take the same data and rearrange the fields in the pivot table. The second pivot table has two filters in the upper left-hand corner. The DOW (day of week) allows you to select/eliminate specific days. The “choose” formula for naming the day that Fred asked about works well for the pivot table, because you now have distinct text that displays properly in the pivot table. There are endless permutations of the data available. Use the Pivot Table toolbar settings to change the data range and to update the computations. I also put the year in the filter in anticipation of wanting to break the table up. Notice, that as I have arranged the tables, you automatically get the sums for all of the days with the same name in each month. I

I might be on the wrong track for what you want, so if you can just draw a picture of what your ideal report would look like, it’s likely that there’s a pivot table that will get you there.

Best regards,
Norm

Reply | Quote

Well done!

That’s why I didn’t even take a shot at this – no way I could do this. But I sure learned a lot.

Question: I noticed that the months go from 2 to 10 (presumably Feb to Oct) in the Pivot1 Example. That matches the data in the PivotData sheet. But in the Daily Tips sheet, there was data for other months (Jan was at the end). But the question is not why are months missing – that sounds like the data was just not copied to the PivotData sheet. The question is if the original data did NOT have Jan data but it was added later, how can you refresh the PT without losing any formatting you would have applied to the original PT? That’s one of my problems.

2nd Question: When clicking on the Data filter, I might select one of the columns. But it seems the Data filter choices are narrowed down once I do that. If I select only 1 filter, the Data filter disappears entirely. How do I get back to the original selection of all the filters?

3rd Question: How did you create the field for the Cash/Credit ratio? I know how to get the sums of all the combinations of AM, PM, etc.

Fred

Reply | Quote

Hi Fred,

1. Yes – the 1,2 3 are the months. I was too lazy to convert them to named months as was done with the days of the week. I’m enclosing a copy of the sheet with the data for 2016 added. You can add as much data, and in any order, as you like without upsetting any formatting. Insert the raw data, cols A-G, and copy down the formulas for the other columns. Position the cursor in the output table (you always want to that when editing pivot tables) and select the Pivot Table Tab. On the tab locate the Data Source button and use it to enter the new data range. Then click the neighboring button to update the results. You don’t have to sort the raw data, the pivot table does that.

In the example I attached I have extended the date column and the computed columns through to the end of 2016 and set the data range accordingly. The default condition when creating pivot tables is to not display empty cells. You can see that in the second example which has months where there is currently no data. However, where the data that is computed the sheet shows $0 rather than blank because a computed value of zero is still a value. The zero values can be made to “disappear” with a change in formatting.

43755-Pivot-Tab.

2. For fields that define where data appears in the table (e.g. row, column or heading), right-hand click on the little tab to the right of the title. You’ll get all of the choices for hiding/selecting the data. You also get the choice for how to sort the data which is often useful.

3. The ratio is a custom computed field. Click the button on the Pivot Tab marked Fields, Items & Sets. If you select List Formulas, Excel will insert a new page and show the formula I used. The Insert Calculated Field choice allows you create new fields and formulas.

Regards,
Norm

Reply | Quote

Hi Norm,

As I said in my original response, I don’t use PT’s much but I know they’re a powerful tool. So I’m having a little trouble with your responses in your previous email.

First let me mention that I open the file in Excel 2003, especially since it has an xls extension. I did open it in 2010 and was able to do most of what you said. But let me focus on 2003 in terms of my 3 questions and your answers.

Q&A #1: I’m ok with this (but you mention putting raw data is in A-G. I think that’s A-E since col F starts the sum formulas).

Q&A #2: This seems to work as you described when I open the file in 2010. But it’s different in 2003 – maybe a bug. See the attached screen shots:
– file named “1 Data field…” is what I see in 2003 when I click on the down arrow (is this what you meant by the “tab”) next to “Data” – all data fields, including the calculated one, are visible. So I unchecked 2 fields (AM CC and PM CC) and click ok
– file named “2 Data field…” is what is see when I click the down arrow again. Both fields that I unchecked in the above step are gone; it’s not that the field names are visible but the boxes are unchecked. They’re just gone. (In 2010, it works as you described in terms of field names are present but with boxes unchecked.)

Q&A #3: I suspect that this new page is a 2010 (or 2007) feature. I looked at the 2010 version briefly; not sure I saw how to create a calculated field on this page. But 2003 had a way of defining a calculated field but I have no clue where the feature is hidden or how to do it.

Thanks.

Fred

Reply | Quote

Hi Fred,

I’ve got Excel 2003 running now inside of an Windows XP virtual environment. That lets me run both 2010 and 2003 on a single computer.

1. Yes – I got the range of input data wrong. Good catch.

2. I see the same disappearance of the data choices in 2003 whenever I try filtering the “Data” field in either example. This appears to be by “design”, which was corrected in later versions of Excel. As you observed, this works fine in Excel 2010. So in example 1 you can filter Month, Day, Year without problems. If you choose to filter the other items, there is no path back (at least that I can find).

3. Yes, the calculated field is there in 2003. You have to remember that the pull-down menus in 2003, by default, only display the most common commands. At the bottom of the of the pull-down menu there will be a set of double downward pointing arrows. Click on that set of arrows to display the full menu. I’ve inserted a picture of the fully expanded menu. I know you can change that default behavior, but I’ve forgotten how.

As with 2010, you can create a formula or display the formulas already created. Look at the one I created for the ratio. It consists of typical Excel computation commands such as If(condition, do this is if true, do this if false). The equation editor allows you to pick the names of existing fields to use in the computation. The formula reads “=IF(‘CC Total’ 0,’Cash Total’ /’CC Total’,0)”.

So, my formula first checks to see that the denominator is NOT zero using the field CC Total. If the denominator is not zero, the formula computes the ratio of Cash Total to CC Total. If the denominator is zero, the formula returns a zero. I could use something more sophisticated than zero, such as reporting whether or not there were only Cash transactions but, as often is the case, I took the lazy way out.

The pivot tables were a great addition to Excel, but the implementation in 2003 was incomplete, at best, and needlessly complicated. The interface in 2010 is so much simpler that using the wizard is a waste of time. Just drag stuff around to put them either in columns, rows with the stuff you want added up in the data field.

Meanwhile, we’ve not heard from the original poster, so I don’t know if the problem he posed is solved or not.

Hope that helps,
Norm

Reply | Quote

Hi Norm

I’ve got Excel 2003 running now inside of an Windows XP virtual environment. That lets me run both 2010 and 2003 on a single computer.

I have Excel2003, 2007, 2010, 2013 and 2016 all running on a single computer, but I don’t use ‘virtual environment’.

I’ve inserted a picture of the fully expanded menu. I know you can change that default behavior, but I’ve forgotten how.

If you right-click anywhere in the top-panel Excel2003 menu, and then select Customize… from the bottom of the displayed flyout menu, you can then select the [Options] tab to show full menus always:
43784-zzz1

zeddy

Reply | Quote

Fred,
I need to install 2003 to answer your questions. I’ll setup a virtual machine to do that later tonight. I’m sorry I forget to specify that I was working with Office 2010.
Best regards,
Norm

Reply | Quote

Hi Marc

I had a look at your posted file and used some array formulas to generate some numbers for you.
I note from your sample data that on some days you do both am and pm shifts.
It seems that Sunday isn’t worked too much, but generates the best daily average amount.
Maybe work more Sundays – get more tips?
I stuck in a couple of charts as well.
You can also create pivot charts if you prefer to use pivot tables.

zeddy

Reply | Quote

Hi Norm,

Thanks for the info and learning opportunities.

As you observed, this works fine in Excel 2010. So in example 1 you can filter Month, Day, Year without problems. If you choose to filter the other items, there is no path back (at least that I can find).

Then this “feature” is even stranger. I just happened to choose the Data drop down first and didn’t even bother with Month or others once I saw what was happening here. One would think it would work the same for all cases.

I also said

Q&A #3: I suspect that this new page is a 2010 (or 2007) feature.

I just saw that it’s not. In your screenshot of the drop-down menu, the last item is List Formulas. Clicking this gives the same page (on quick inspection) as what I saw in 2010.

*** But that raises another question which I’ve always wondered about: What is the difference between a “Calculated Field” and a “Calculated Item”???

Just to make sure I understand what’s going on with these Calculated Fields:
– I see on the extra sheet that you defined a “Calculated Field” (not “Calculated Item”) called “Sales Ratio” with the IF statement as you mentioned. Small question on this:
— I understand why you have the check on ‘CC Total” to not be 0 (avoid the #DIV0 error). But if it is, you return 0. What happens, perhaps as unlikely as it is, that ‘Cash Total’ = 0 but ‘CC Total’ is >0. That would also give a result of 0. Perhaps this is something for the OP to worry about (or your “lazy way out” but did you mean only CC transactions?).

– When you did that, did that then become almost like another field in the PivotData field that can be dragged into the body of the PT? It looks like that’s the case from the PivotTable field list.

– Somehow Excel knew that “Sales Ratio” not only depended on the formula but also depended on its position in the PT in terms of what rows of data to use the formula on when providing the PT. For example, when showing the “Sales Ratio” for Sunday’s in Jan, it only used those rows from PivotData that pertained to Sunday’s in Jan. Is that something built into the PT feature (actually this would seem to be true for any field, even those already defined like ‘Sum of AM Cash’)? Almost like writing an array formula as Zeddy did.

I also saw in the Field List that some items are bold and some are not. It seems that the bold items correspond to those that are used in the PT. Is this correct? If not, what is the meaning of a bolded item?

Thks again.

Fred

Reply | Quote

Fred,
I’m getting ready to travel and won’t be back until late next week. Let me address some of your questions now and, unless I get my hands on computer while traveling, defer some of the answers that require more detailed explanations.

1. A calculated item is like one of the cells in the data set which computes a value locally – i.e. in this discussion the sum of the cash tips coming from the morning plus the tips coming from the evening. There is a single cell associated with every day in the spreadsheet that computes this value. A “field” can be thought of as a named collection of cells, for example all of the tips that were received in cash in the morning no matter what the date. So the pivot table data can be thought of as a collection of fields, where the row headings and the column headings define one large collection of fields. Some of the fields perform calculations – in the 2010 version they are the ones collected in the lower right-had box. The most common calculation is to add things up in accordance with the row and column headings.

2. The formula computes the ratio (%) of cash tips to charged tips. When the cash tips are zero, the ratio is zero. The cash tips could be hundreds of dollars, but percentage coming from cash is truly zero, When the charged tips are zero, there are two possible results. The cash tips could be zero – in which case the percentage is meaningless since no income was received and zero is the correct answer. The cash tips could be a positive number, in which case the true value should be %100 – all of the tips are from cash. As I said, I was too lazy to write out the rest of the logic which would result in formula something like: “=IF(‘CC Total’ 0,’Cash Total’ /’CC Total’,IF (‘Cash Total’ 0, 1,0))”. As it happens, that case did not occur with the data from Marc. And yes, since the computation represents a ‘field’ it can be used like any other field.

3. Bolded items are those used by the pivot table.

4. Your observation that the fields acting like the the SUMIF formulas used by zeddy is a really sharp observation. The advantage of the PT is that it writes the formula for you. The PT is not restricted to computing sums – it can do Max, Min, Average, and a host of things more and most of them with a click or two of your mouse. When I’m back online I’ll provide more details.

Best regards,
Norm

Reply | Quote

Hi Norm,

Thanks for the detailed explanations.

As I said at the very beginning of my posts on this thread (the one replying to the OP), I often do things the hard way with array formulas and then think about a PT. I did one like that last year. I do use something like

{=MAX(IF(G1:G10=”Tuesday”,H1:H10,-100))}

to get the maximum value of cells in col H only if the corresponding cell in G is, for example, Tuesday (with the assumption that all the values in H are positive). I’ve also done array formulas to compute Min and Average. I’d love to see something with Max or other than just counts in a PT.

I probably should use PT’s more; I probably would if I could use the computed values in the PT. I didn’t quite get your distinction on calculated field vs item in the PT (yes, I did understand the calculated item of adding up the AM cash tips and PM cash tips – but that’s outside the PT).

Duh – my bad on the CC Total vs Cash Total. Obviously the formula you wrote in your last response would capture the situation clearly.

But that got me to thinking about something that only the OP can answer: since it’s not clear what kind of “eatery” we’re talking about in terms of hours, it may be somewhat meaningless to compare AM to PM in terms of how much in tips was made. If the place is open 10am to midnight, one would expect the PM tips to be greater – there’s more hours! For a more useful result in this case, one would need to account for hours, so maybe something like avg tips per hour (and somehow, I’d bet, this could be built into the PT with some formula for a calculated field – or is it item?). Similarly, depending on the place, there may be greater traffic at a particular meal of the day.

Anyway, have a good trip and don’t worry about getting back to me on this. I’ve already learned a lot.

Fred

Reply | Quote