Replies

Arrg. Swear I answered this ten minutes ago but cannot find it!
Thanks again Fred. I’ve added the change to my model and it works. I’ll take a lot at the VBA tonight and see what I can make of it. Haven’t done programming stuff for over a decade though!
For the short term I’d say the maximum spread will be 9 months. Ultimately I’d like to change it based on what accounting shows really happens post forecast but I haven’t done enough research to know what that is! So if I could spread 4-9 months that’d be enough for 2016!

Maud, RG, Zeddy, fburg?? Any ideas appreciated. I’m just not getting anywhere on my own.

Right. I’m not trying to make a big leap. So not project by project and same rules for entire forecast. Just total sales for the month like last time. In line answers below in CAPS

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. TRUE

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. CORRECT. MAYBE BY TYPE OF PROJECT NEXT YEAR

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). SAME NUMBER MONTHS FOR ENTIRE FORECAST

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. I’M GOOD WITH ONE SHEET, ONE RULE, ONE FORECAST.

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

Hi Fred or anyone!
So this solution has worked very well and now it’s time to improve. I need a kick start! The current formula looks at the past 4 months and uses 25% from each month to create the total. Perfect. But what if I want to have this more dynamic. Say spread the income 30%/30%/20%/20% instead. Or spread it out across five months instead of four. Can the formula be modified to do a lookup for number of months to use and % for each month?

I don’t know if you saw my second post.

There is no need to move the data to start in col E if you use the revised formula of my second post.

You mentioned in post #3 that you wanted a single formula, so this will do it.

But with your data beginning in col C but you wanting the previous FOUR months, that creates a problem (referring back 4 cols) until you get to col E.

For example, if your data began in col C but you only wanted to go back 2 cols (sum up cols A and B), there would be no problem. If your data began in col E but you wanted to sum up 5 months, there would be a problem.

The problem, generally, is that you can NOT go back past (to the left of) col A.

From a purist point of view, the formula of post #5 is a bit messy. Compare the formula in post #4 vs #5 (when I was awake). The only difference is that -4 in the first one is replaced by that
-IF(COLUMN()<5,COLUMN()-1,4)

Note what the -IF does: if the column where you want to start the sum is less than 5 (columns, although usually identified by a letter, can also be referenced by the corresponding number – col A is 1, col B is 2, etc) meaning to the left of E, then we do NOT subtract 4 since that would take us back beyond the left of col A (which is NOT allowed, equivalently a col with a number of 0 or less). Instead we start the sum from col A (or the first col).

If the column where you want to start the sum is 5 or more (col E and beyond to the right), then the test
COLUMN()<5
is false, and the result of -IF will always give 4.

So if you look at the 100th or 952nd or 2500th col, the -IF is always FALSE and you're always starting the summing 4 cols to the left of the present column. At this point, you might wonder why the -IF is there, rather than just a 4.

The present column where you're putting the formula has a col given by COLUMN(). We don't have to actually know which column we're in to reference the current column.

So, I think your options are
1) move the labels to col D and start the data in col E; then you can use the formula in post #4 EVERYWHERE.
At this point, it could also be simplified to something like

As you fill to the right, Excel will adjust the formula to SUM(B4:E4)/4 for col F, then SUM(C4:F4)/4 for col G, etc.

2) if you want to keep the labels in col B, then
2A) use the formula in post #5 everywhere or
2B) use the formula in post #5 for just col D (and col C if you want) but then use the formula in post #4 from col E forever to the right.

As to the rest of the formula, the 2 INDIRECTs are putting together a starting point and an ending point for summing. Since these change with every column, you can't have a fixed column reference. That's what the COLUMN()-4 and COLUMN()-1 do. When added to the end of (concatenated with) the fixed string "R4C", you get something like R4C2 for a start and R4C5 for an end. (This is an alternative form of referencing cells called "RC" referencing, where you give the Row # after the "R" and the Col # after the "C" all as one string.)

Note that the 4 is because your sums are in Row 4. If you change your mind for this, you'd have to change the 4 to the proper row # (there are other alternatives than using a constant row # but those really don't seem to be needed here based on what you originally posted).

Hope this helps.

Fred

Thanks Maudibe. That’s a cool approach I’ll review tonight.
Thanks Fred so much for all that detail. I’ve learned a lot today!

ok. very cool. thanks. now I get to try and figure out how this works!
it’s a bit hard to move the data as it gets swept into our forecast tool, but I’ll try moving everything to E and see if I can remap the import

Was hoping to figure out how to do it all in one formula, one row, handling how many ever months there are. Something that could count the number of times the sales amt had been used then drop it.

Actually I’d think that every professional services firm would need this.

I don’t care about other people having it, although I want them to be able to see the last data I updated with. I would like to not have to open the other file though. Is there a way?

Thanks,

Bryan

Hi Rory,
Here’s the first part of the formula with the filename
=GETPIVOTDATA(“Period 13 Actuals “,’C:[2-Key Strategic Indicators QB Source Data.xlsx]P&L Pivot’!$A$1,

The first page is part of the display sheet. The second sheet is data entry. The timeline grey formatting is what I want to show up on the first sheet.

The format of the source will change week to week so we can’t manually format the display as it will change. The formatting is manual and up to the person doing the data entry so I can’t cond format. There is also no way to tell what will be in the data so I can’t see how to figure it out. We have to just be able to copy the formatting over as part of the automation. Can you point me in the direction of the VBA idea?

Thanks. that did solve it.

I had tried that as it looked incorrect to me, but then it returned an error.
Tried again this morning and it helped. Also Zeddy’s comment helped, although AG and AF are correct I somehow had split the letters. NOW, everything comes up green, there are no reds and some should be! But I’m closer. A little more detail might help.

I have a formula in the cell =IF($B12=””,””,IF($AG12>$AF12,Red,IF($AG12$AF12 is true. In the conditional formatting I have =$AG$4$AF$4 in the second rule which colors the cell red. The cell is being colored green. The second rule matches the cell formula so I figured it’d color it red. I do not have stop if true checked either.

The value in AG12 = 13000, AF12 = 6000

Should the cond format not have the $??

thanks. that worked, except for me the conditional formatting is not ‘taking’. seems silly, but I’m getting NO conditional formatting on those cells. I’ve attached a couple of screen shots in case they’ll help.