Replies

Attached is a spreadsheet that only takes into account a change to the # of months over which the sales are counted. This looks like it should work but I only did limited testing. You should definitely do a bit more testing with your data.

Note I put in the # months in B1 (and put a label in A1). This is used in the calculations.

Also note that I did not account for any potential rounding effects. For example, if Jan sales = 100 and that is to be spread over 3 months, then Jan will be counted as 33.3333 for the next 3 months. This could be rounded but then each of the 3 months would be 33.33 or 33.34. Rounding so that the first 2 months are 33.33 and the last month is 33.34 would be harder.

I left the rows for your original explanation but moved these rows down just to be out of the way. However, they are no longer correct (or needed?) if the # months is not 4.

Being able to change the % allocation per month to NOT be the same each month (as opposed to keeping it equal based on the # months, which is what I did) would be harder – perhaps not even doable without VBA. Perhaps a modification to the code that Maud posted? I looked at his code and it doesn’t look too difficult to modify for both features (# months, unequal %). However, I won’t be able to get to either approach (unequal % with or without VBA) today.

Is there some # of MAXIMUM months over which the sales would be spread? I’m thinking of a non-VBA solution where I’d have “helper” rows (rows where intermediate calculations are done rather than trying to capture everything in 1 formulas) for each of those months. If the maximum number of months is NOT used, then those rows would be 0. Then there’s be 1 more row to sum all the helper rows, which is what you want. If necessary, the helper rows can be hidden.

Fred

Hi,

I’m attaching a workbook with a suggestion. Take a look.

Look at the columns that have row 1 shaded in yellow – that’s what I added.
– col L just calculates the number of days for the palettes based on Movement Date and Date In (plus 1 based on your Col K). It seemed like your column K was based on this but why not let Excel do the calculations? This is not critical to the 2nd set of cols added – they use col L but the formulas could have used col K

– cols P-R are equivalent to your M-O. P-R use 1 formula that is filled across the cols and down the rows. I wasn’t sure if you wanted C/F throughout the cols if the col was, in effect, not applicable but that’s the way I approached it for now. The formula could be changed if you want to leave a blank like kweaver’s formulas but I interpreted your original post to show C/F everywhere except for where there is an actual charge.

The formula in P2 changes automatically as you fill across and down to change col and row references (unless there’s a $ in front of the number or letter telling Excel not to change cols and rows), so let me explain just P2
=IF(ROUNDUP($L2/30,0)=RIGHT(P$1,2)/30, 10*$H2,”C/F”)

The ROUNDUP($L2/30,0) takes the # days (either the original col K could be used or the calculated # days from col L), divides by 30 since your intervals are 30 days long, and rounds up to 0 decimal digits (giving a whole # of 30-day intervals). So this calculates the # of 30-day intervals for the row that the palettes were stored.

The RIGHT(P$1,2)/30 looks at the last 2 characters in Row 1 of the column and divides by 30 to also get the # of 30-day intervals relevant for that column. There are other ways to do this if the column labels in row 1 had been done differently but this is a small point.

The formula compares the two 30-day intervals and gives back C/F or the charge.

You might also want to consider putting the charge somewhere in a cell so you can change it in one place and the formulas in cols P-R would just refer to that cell rather than having to update these formulas if the charge changes.

Let me know if you have any questions.

Fred

I’m looking for the post I just made in response so I could edit it but can’t find it. So I’ll try again.

NOTE: I just found my previous post and deleted it. This new post is more direct.

In your original spreadsheet, you had sales for each month. One might assume that the total sales figures are the result of several sales.

So when you ask about changing the rule of “spread each month’s sales over 4 months evenly”, would it be safe to assume that you want to change the rule to the total sales? Doing otherwise (changing the rule on a per-sale basis) doesn’t seem feasible since your original spreadsheet did not go to that level of detail.

Also, when you ask about changing the rule, I’d assume that the change would apply to all months. Is that true? Or would you want the sales of some months to be treated differently than for other months. In other words, could you have the sales in Jan spread over 4 months but the sales of Feb spread over 5 months? Same question applies to the percentages (sales for Jan treated evenly but for Feb treated as 30-30-20-20).

Lastly, do you want to be able to compare, in one spreadsheet, the cash flow for different rules? Or just apply a rule, look at the result, maybe print it out, and then just do another rule? It would seem that some place is needed to store the different rules regardless of your answer. It would also seem that a VBA solution might be needed.

Depending on your answers, I’m thinking this is getting beyond my skills (and it’s still early here so my brain is not awake). But I’ll bet that Maud, RG, Zeddy and others would be able to help.

Fred

Still early in the morning – for me. So remember that the brain may not be fully awake yet.

Before trying a solution, I’d have to ask some questions.

Are you changing the rule of “spread cash flow EVENLY over 4 MONTHS” for every job or on a job-by-job basis? It sounds like the former but I’d want to ask.

In other words, could some jobs be over 3 months while others are over 5? Could some jobs for 4 months have cash flows evenly while others over 4 months be 30-30-20-20?

Or are you trying to see, if for ALL jobs, you change the current rule to compare how that affects cash flow?

If each job can have a different rule, where will that information come from? Would you create a sheet of “rules” where each rule has a #months and % of price due each month? Then for each job, you’d just need to refer to the proper rule.

If you change the rule for all jobs, would you want to have in your one spreadsheet a comparison of the cash flow for all rules?

I’m not sure I’d be able to help depending on the answer (and almost definitely not today as I have a full plate of things to do). But I’d bet on Maud, RG, Zeddy, or others being able to move this along depending on the answers.

Also note that my questions above are just what I think needs answering before beginning a solution. Others might need clarification along related lines.

Fred

Zeddy – I see you came to a fork in the road and took it!

My favorite Yogiism is about him going into a pizza shop and ordering a whole pie. The waiter asks should the pie be cut into 6 slices or 8 slices. Yogi asks that the pie be cut into 8 slices because he’s really hungry. Something to remember the next time you’re with friends ordering a pie.

Another approach, although a little bit more dangerous, is to change your system clock to be sometime in 2015. Doesn’t matter when. I’ve done that on a few occasions. BUT YOU HAVE TO REMEMBER TO CHANGE IT BACK. On some computers, you may also need admin rights to do that (when a dialog pops up, just put in the admin password).

Fred

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

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

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

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

here’s a link to new features in Excel 2016:
http://new.office-watch.com/2016/excel-2016-jan-2016-update-new-chart-and-functions/

I looked at the article. I don’t do much charting, so those features were not of much interest to me.

However, the new built-in functions were mildly interesting.

I would be interested in knowing about differences between 2010 and 2013. I have 2010 loaded and got 2013 at a cheap price but haven’t loaded it yet. And now 2016 is here.

Fred

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

Yay for learning!

about the only thing I can take credit for on the R1C1 (or called just RC?) addressing is not forgetting something that I just did a few days earlier!

I had replied to another post and used that as part of the solution.

Had it gone much longer to this thread, the thought would have been gone.

You know, at this age, memory is the 2nd thing to go.

Fred

Another way to deal with the changing columns, instead of using CHOOSE where the column letters are specified explicitly, is to use INDIRECT with R1C1 type of addressing.

For example,
INDIRECT(“Sheet1!R” & B1 & “C” & COLUMN()-2, FALSE)

This way, as you fill from col C to the right, the col will keep changing. You don’t have to worry about how many columns there are.

Fred

that makes life even simpler. Thanks.

Fred