Add a fixed value until threshold is reached. Output is first value after threshold.

[kentongh]

Hi Everyone, first post. I'm generally "pretty good" with excel but I have no VBA experience. Hoping to find a solution to this problem without use of VBA.

What I'm trying to do deals with dates, but for the sake of this I'm going to simplify it and I should be able to translate a solution to my application.

You have a starting value that is fixed. A threshold value that is fixed. and a step value that is fixed. I need to have my output cell count in increments of the step until it passes the threshold and show that first value after threshold.

~~~~~~
For example.
Starting value is 2. Threshold is 12 and step is 3.
It will go as follows; 2, 5, 8, 11, 14 (stop). The output should be 14, the first step beyond my threshold of 12.

Another example
Starting value is 4. Threshold is 20, step is 7.
Calculates 4, 11, 18, 25 (stop). 25 is the output.
~~~~~~
I do not need any of the incremental value in the steps up to the output, only the last value after the threshold is passed.

In my actual application, these are dates. I have a starting date that is fixed, say 1/1/2016. I'm stepping forward in increments with EDATE (4 months at a time) and I need the output to be the first date that is after today using the today() function. Today is of course, changing every time I open the sheet, so my output cell will step to the next 4 month increment when today's date surpasses the output cell.

I'm also new enough to this forum that I can't seem to figure out how to upload a non-URL based image. Sorry.

Appreciate any and all help. Thanks!
Ken

 

[Peter_SSs]

Welcome to the MrExcel board!

Does this do what you want?

Excel Workbook
	A	B	C	D
1	Start	Threshold	Increment	Result
2	2	12	3	14
3	4	20	7	25
4	5	30	5	35
Value above Threshold

If I have mis-interpreted and that last result should be 30, remove the +0.5 from the formula

I'm also new enough to this forum that I can't seem to figure out how to upload a non-URL based image.

Have a look in my signature block below.

 

[kentongh]

Thanks for your help Peter. Your solution does work perfectly for my example. I'll put in a bit of work to adapt it to dates. Appreciate the help!

Is there a way for me to mark this thread "solved"?

Ken

 

[Peter_SSs]

Thanks for your help Peter. Your solution does work perfectly for my example. I'll put in a bit of work to adapt it to dates. Appreciate the help!

Is there a way for me to mark this thread "solved"?

Ken

No, we don't have a "solved" marker as often after an OP has an answer they are satisfied with, an even better suggestion comes along later and we don't want to discourage that. What you have posted is fine.

If you have any trouble adapting to dates, post back with some realistic sample data and any further explanation.