Hi all,
Long time fan of this forum, many times have found answers here without needing to post, but this one has truely stumped me and I cannot find the exact answer I need anywhere. I will try to be as brief and clear as possible. Any input is appreciated! Side-note: I read about a new function in Excel 2016 called "IFS". The system is telling me that we have Office 2016 but IFS doesn't work and is not recognised.
Relevant columns:
B: Dates (the dates of Mondays from 2nd July 2018 onwards)
C: Formula needed*
D: Formula needed*
R: Target A (numerical) - DEFINED NAME: WTEtar
S: Target B - DEFINED NAME: AgrTar
T: Dates (date targets A and B are effective from) - DEFINED NAME: EffectiveFrom
* C and Ds formula are going to be identical with different references so if we keep it simple and just focus on C that would be great.
The formula needs to: Work out the latest applicable date in EffectiveFrom (so the latest date that is before the date on that row in column B), and then use the figure from that date's row in WTEtar in the following calculation: WTEtar figure / 52 * 40
There is another complication as well, in that it also needs to add this to the previous result from the cell above (so it's cumulative).
The idea behind this is that in the side-section (I don't want to say table because I'm not using the actual table function!) (Columns R, S and T), the user can add a new line with the 2 new targets and the date the change is effective from, and the main-section would automatically alter the cumulative formula from that week onwards whilst still maintaining the same results prior to that week.
I have managed get various different results (obviously not complete results) a few different ways using LOOKUP, MAX and IF formulae (and combinations of them) but not all the way there.
I am afraid I have deleted most of the stuff I tried (I am terrible for doing this, I never think "I may need this snippet again" and just overwrite it with the next idea I have). Currently I've managed to reference the latest applicable date with (entered as an array formula): =MAX(IF(EffectiveFrom<$B3,EffectiveFrom)).
I will admit that I got this from google and slightly adapted it, I don't really understand it (or array formulae at all).
The main obstacle, at least in my head, when trying to use IF to do this (IF the last date in range is less than B, do this calculation, else IF the second-last date in range is less than B do this calculation using the second-last figure in WTEtar etc etc) is the 'else do' part. I read somewhere you can nest up to 7 IF statements which means using this method, even if I was to get it working, would mean the side-section could only have 7 different entries before the formula would have the potential to no longer function.
And I couldn't even get the 'else do' part to work anyway for some reason.
So that wasn't very brief (sorry), but if anyone has any bright ideas or even recommendations on a better way to approach creating the sheet, I would be extremely grateful!
Long time fan of this forum, many times have found answers here without needing to post, but this one has truely stumped me and I cannot find the exact answer I need anywhere. I will try to be as brief and clear as possible. Any input is appreciated! Side-note: I read about a new function in Excel 2016 called "IFS". The system is telling me that we have Office 2016 but IFS doesn't work and is not recognised.
Relevant columns:
B: Dates (the dates of Mondays from 2nd July 2018 onwards)
C: Formula needed*
D: Formula needed*
R: Target A (numerical) - DEFINED NAME: WTEtar
S: Target B - DEFINED NAME: AgrTar
T: Dates (date targets A and B are effective from) - DEFINED NAME: EffectiveFrom
* C and Ds formula are going to be identical with different references so if we keep it simple and just focus on C that would be great.
The formula needs to: Work out the latest applicable date in EffectiveFrom (so the latest date that is before the date on that row in column B), and then use the figure from that date's row in WTEtar in the following calculation: WTEtar figure / 52 * 40
There is another complication as well, in that it also needs to add this to the previous result from the cell above (so it's cumulative).
The idea behind this is that in the side-section (I don't want to say table because I'm not using the actual table function!) (Columns R, S and T), the user can add a new line with the 2 new targets and the date the change is effective from, and the main-section would automatically alter the cumulative formula from that week onwards whilst still maintaining the same results prior to that week.
I have managed get various different results (obviously not complete results) a few different ways using LOOKUP, MAX and IF formulae (and combinations of them) but not all the way there.
I am afraid I have deleted most of the stuff I tried (I am terrible for doing this, I never think "I may need this snippet again" and just overwrite it with the next idea I have). Currently I've managed to reference the latest applicable date with (entered as an array formula): =MAX(IF(EffectiveFrom<$B3,EffectiveFrom)).
I will admit that I got this from google and slightly adapted it, I don't really understand it (or array formulae at all).
The main obstacle, at least in my head, when trying to use IF to do this (IF the last date in range is less than B, do this calculation, else IF the second-last date in range is less than B do this calculation using the second-last figure in WTEtar etc etc) is the 'else do' part. I read somewhere you can nest up to 7 IF statements which means using this method, even if I was to get it working, would mean the side-section could only have 7 different entries before the formula would have the potential to no longer function.
And I couldn't even get the 'else do' part to work anyway for some reason.
So that wasn't very brief (sorry), but if anyone has any bright ideas or even recommendations on a better way to approach creating the sheet, I would be extremely grateful!
