Very complex formula dealing with many variables

First, thanks very much for even reading this at all because it is very long and I apologize for that. It’s a pretty big problem.

I have a very complex formula to figure out that deals with budgetary costs being populated correctly for each month and I’m not having much luck. In fact, it’s a bit overwhelming and I feel that I’m in way over my head on this one. Can you help?

See the attached workbook. Be aware that this workbook is in a transitional mode with many things (mostly VBA) not working at the moment due to lots of recent additions of columns, one of which is what this post is about.

For ease of working with the specific variables, I’ve kept the sheet unlocked and have hidden all columns on the worksheet that don’t pertain to the problem, as there are many on this sheet. I also included 12 rows with pre-entered test data, all ready for the months to test formulas with.

I will try to be as descriptive as possible.

For each cell in the month columns (AP:BA) on the Budget_Data sheet, the formula needs to use variables located in columns Q, X and AG:AO to derive the correct value to be applied to any given month or months for the next budget year.

The variables are:

• Q14 = “Recurring Fee” – Must be selected from Q14 for any of the rest of this to apply at all. All other selections from Q 14 would be assumed to only apply to the month select in the Start Month in AK14.

• X14 = The action for the Recurring Fee (Add, Remove, Renew – anything chosen outside of these is assumed to only apply to the Start Month chosen)
  

• AG14 = The quantity of an item to be purchased – This would always be “1” with a Recurring Fee and the amount in AH14 will be applied to specific months based on other criteria, below.

• AH14 = The total cost per item per Payment Cycle in AO14.

• AK14 = The month that the recurring fee will begin

• AL14 = The month number, a helper column for AK14

• AM14 = The month that a recurring fee will end

• AN14 = The month number, a helper column for AM14

• AO14 = The payment cycle for the recurring fee (One-Time, Monthly, Quarterly, Semi-Annual, Annual)

Rule: There can’t be both a Start and End month for any record (row). It must be one or the other and the formula for each month needs to automatically adjust for this. For any Start Month, the monthly payment should begin in the selected month and continue through December, and for any End month, the payment should be applied to this month and all months prior to the End Month but both based on the Payment Cycle selected in AO14.

For instance, there are 3 actions in X14 and 5 in AO14 that the formula needs to account for:

• The recurring fee will be Added and will start in a specific month (AK14) based on the Payment Cycle.

• The recurring fee will be Renewed, in which case, it will be applied to all months, based on the Payment Cycle.

• The recurring fee will be Removed and will be ending on a certain month but needs to be applied to this month and all months prior to the ending month based on the Payment Cycle.

Each of the above must apply to months based on the Payment Cycle chosen from AO14, One-Time, Monthly, Quarterly, Semi-Annual or Annual.

• For “One-Time” fee, the amount in AH14 should be applied to the Start Month chosen in AK14.

• For “Monthly”, the amount in AH14 should be applied to each month, beginning with the Start Month that was selected in AK14 and should continue through December.

• For “Quarterly”, the amount should be applied to the Start Month in AK14 and then every 3 months through December.

• For “Semi-Annual” the amount should be applied to the Start Month and then to the 6th month after, unless it goes beyond December, since we’re only working with one year at a time.

• For “Annual”, the amount should only be applied to the Start Month just like a “One Time” fee.

Should any of the month cells see that the data in X14 = “Remove”, then they should look at the End Month in AM14 instead of the Start Month in AK14 and include only the End Month and months before the End Month for the amount in AH14 to be applied, basically the opposite of what they do using the Start Month explained above.

If you have any questions about anything I might have left out, please let me know.

And again, thanks very much for any help you can provide.

Hi BeachRock,

Have you written a Formula based on your rules yet?

If so, which Column/Row is it in?

Hi noboffinme, thanks for your reply.

I've been working on this since my original post. I replaced the attachment in post 1 so it reflects everything I've done. I added a helper column for the Payment Cycle, which I'm thinking can be used somehow as a multiplier; 1 for "One-Time", "Monthly" and "Annual", 2 for "Semi-Annual" and 4 for "Quarterly".

Right now, all month column cells AQ14:BB25 each contain a formula that works to account for the Start and End Month in either AL or AN, the necessity for Q to be "Recurring Fees", and the necessity for Qty in AG to be 1.

A formula like this one located in AQ14 is also in all of the month cells AQ14:BB25 and they all work.

Formula:

Please Login or Register  to view this content.

However, I have not yet been able to also account for the value in column X to be either "Add", "Renew" (when there is a Start Month in column AL) or "Remove" (when there is an End Month in column AN), and I have also not yet been able to account for the Payment Cycle in a way that causes each month in a cell on the same row to know when to or when not to apply the cost if the value in the Payment Cycle helper column (in column AP) is 2 for Semi-Annual or 4 for Quarterly and that cell is 6 months after the Start month on a Semi-Annual Payment Cycle or 3 months after the Start month on a Quarterly Payment Cycle OR the reverse (6 months before the End Month for Semi-Annual or 3 months before for each Quarterly payment) of both of these if we're dealing with a "Removal" from column X and an End Month in column AN.

It's starting to make some sense but the last two items are not going to be easy.... If you have any ideas, please let me know.

Thanks!

Well, I really learned something on this problem. If you've got lots of variables to deal with in one very long formula, separate each variable from all other variables and get each one working by itself. Then, add them all together into the one long formula. By doing this, you also will understand what variables you thought you needed to account for don't actually need to be. For me it was the ones in column X that I found I didn't need to include. You'll find all of these in the yellow background fields.

Apparently, I was up to the task since I was able to finally get this all figured out. Take a look if you're interested. I attached a copy. The month cells Change the items in Q, S, T, AL, AN and AP to see how the months pull in the correct cost to be applied. Cells with a orangy-pink color were the four helper columns I needed in order to make this all work.

Here is an example of the formulas I ended up putting together. All were slightly different from each other. This one is from January (column AR) on row 14.

Formula:

Please Login or Register  to view this content.

If you have any suggestions for how I could have done this differently (in an easier way) and still accomplished the same result, please let me know.

Now I'm on to fixing everything I broke while trying to get all of this to work.

Your worksheet is protected

A quick look suggests you could simplify the Month Columns AM and AO or perhaps the user can simply selct the month number rather than name??

Hi Ace_XL,

The sheet is protected but without password if you want to look at anything else on it. I left the locked cells visible and selectable so you can see the formulas used in each.

Yes, I could have used month numbers in the dropdowns instead of month names, but I don't see how that would have made the formulas any easier. All of the helper columns will end up being hidden when this is a production version. I left them visible just so people could see what was going on without having to unhide them.