if statement with dates and applying % increase based on specific months

78 views including at least 6 downloads of the workbook - you may need to clarify what you are looking for.

You could do with a simplified sample workbook and you SHOULD manually enter the results you are looking for in at least a few cells. I presume it's those highlighted in yellow?

Hi there

I have provided an example in the attached workbook.

If you go to cell AU17 (I have highlighted the row in red), that what the new formula should do. However, it is also contingent on "column K".

Annually = 1 = each month
Semi-annually = 2 = every 6 months
Quarterly = 4 = every 4 months

hope this example can help?

thxs so much!

As before, I think we are having trouble understanding exactly what you need help with.

What I see in the formulas in AU17:BF17 is a conditional sum of the rates, then add 1, then multiply that by the original pricing to get the projected pricing. Am I understanding correctly so far?

Assuming that is correct, my first thought was if the effective dates and wave rates can be arranged differently? My thinking here would be to use the SUMIFS() function for the conditional sum, and it would be easier if the dates and rates were arranged so that the dates were all together and the rates were all together. Instead of dates in columns M, O, Q, S, (rates immediately to the right of the date), would you be allowed to arrange the dates in M:P, and the rates in Q:T? Then the conditional sum becomes SUMIFS($Q17:$T17,$M17:$S17,"<="&AU$10) [in words -- "sum the rates where the effective dates are less than or equal to the date in AU10"]. The complete formula in U17 is then =AH5*(1+SUMIFS(...)) [pay attention to relative and absolute referencing] then copy/paste/fill across and down.

I'm not sure that helps any.

If your business model permits, I would concentrate on the numbers of days after the start date that a particular rate applies. If necessary, work backwards from the number of days to a specific date.

It would make your arithmetic and programming much easier.

MrShorty

Based on your recommendation, i have aligned the dates next to the each other, and the % increases together. Just not sure how to logically make the formula flow.

in columna AU18 (red highlight), i have manually done the calculation to show as an example.

any help on this would be great!

Did you try the formula I proposed =AH5*(1+SUMIFS($Q17:$T17,$M17:$P17,"<="&AU$10))? It seems to work for me in your file. (note prior errata, I had mistakenly had the criteria range extend through column S).

*** READ PREVIOUS MESSAGE FROM MrShorty FIRST ***

if that isnt gonna work... then need a bit more info to understand the intent from your perspective...

Can we first focus on what your formula is CURRENTLY doing

From your initial post

Please Login or Register  to view this content.

IF M15... what? this does nothing...rendering the nested IF statement completely useless and really just fluff.

Which comes from the new sheet AU Column...so to update the above it would be this out of the latest file you shared...

Please Login or Register  to view this content.

Removing the random lost IF we would be left with

Please Login or Register  to view this content.

But ... before I break it down... can you take the time to break down what this formula is doing... and WHY you were doing it...


Once you do that, explain the NEW ELEMENT and how it would interact with the formula or what you feel you are missing and what the INTENT is.

Originally Posted by MrShorty

Did you try the formula I proposed =AH5*(1+SUMIFS($Q17:$T17,$M17:$P17,"<="&AU$10))? It seems to work for me in your file. (note prior errata, I had mistakenly had the criteria range extend through column S).

Hi I tried your formula, with what you just wrote.

Please Login or Register  to view this content.

If i paste your formula in cell AU17, it calculates accurately FOR ONLY May-June months (as its a cumulatives). Otherwise for cell AU17, it should only do AH17*Q17 (as that is the only month that has the increase until 1-Dec-22, when it should add Q17+R17 (9.7%+10.50%).

hope that makes sense?

@ELeGault

Hi there

thanks for your time and your reply.

the initial formula was provided via the forum. however, it basically acts to apply the "4 waves % increase" based on when they are made effective, and should act cumulative.

i tried your formula, with extending the effective date range and the % waves, however getting a "SPILL!" error in cell AU17:

Please Login or Register  to view this content.

i dont know how else to edit this formula. in my last attachment in red highlight, i have provided what the formula should do / answer it should provide.

thxs again!

Maybe try at AU17

=IFERROR(IF($M17,AH17*(1+SUM($Q17:INDEX($Q17:$T17,IF($K17=1,MATCH(AU$10,$M17:$P17),YEARFRAC($M17,AU$10)*$K17+1)))*(AU$10>=$M17)),AH17),AH17)

Otherwise for cell AU17, it should only do AH17*Q17 (as that is the only month that has the increase until 1-Dec-22, when it should add Q17+R17 (9.7%+10.50%).

That's what should be happening with my formula. I notice that I have forgotten to mention that I believe your date in O17 is incorrect. You currently 1 May 2022, and I believe this should be 1 May 2023. This typo does create a problem for my formula, because 1 May 2022 is less than 1 Jul 2022, so it includes both corresponding rates in the sum.

Of course, if I have assumed incorrectly and O17 should indeed be 1 May 2022, then there is something about the overall logic that I am not understanding.

At this point, we are starting to get a few different solutions. Try the different solutions and see what is going to work for you.