Formula for variable date ranges in IF(LOGICAL TEST)

Hi,

I am trying to make a formula that works something like;

=IF([LOGICAL TEST]Date x<=Date y BUT Date x>Date z, [IF TRUE]Cell A, [IF FALSE]Cell A +1 row down AND +1 row down from Date x in the LOGICAL TEST part)

Essentially I am trying to make a model in which a specific value corresponds to any time between 2 dates. The value that corresponds to that range of dates is used until the date in the model (which changes) exceeds that given date range. Once that happens I want to next cell below Cell A to be used AND the date range in the logical test to change as well.

Any help to work out this formula or suggestions for a different approach much appreciated

Cheers,
Dan

If you can show us what you are working with, we might be able to make some suggetions. To upload a file (actual or mock up) click 'Go Advanced' at the bottom of the reply box, then click 'Manage Attachments' and follow the menu to upload the file.

This makes no sense to me at all - sorry!

Referring to F3: I want the value of this cell to equal the value in col C according to the date in col F which corresponds to the appropriate date range in col B

Excel 2016 (Windows) 32 bit

	B	C	D	E	F
3	22/05/2008	0.049978			15/04/2008
4	01/09/2008	0.049978			27/04/2008
5	08/12/2008	0.053652			09/05/2008
6	24/02/2009	0.053652			22/05/2008
7	01/09/2009	0.049978			03/06/2008
8	18/03/2010	0.053652			16/06/2008
9	16/07/2010	0.049978			28/06/2008
10	01/09/2010	0.049978			11/07/2008
11	03/11/2010	0.053652			23/07/2008
12	26/01/2011	0.053652			05/08/2008
13	01/03/2011	0.053652			17/08/2008
14	04/07/2011	0.049978			30/08/2008
15	22/08/2011	0.049978			11/09/2008
16	24/11/2011	0.053652			24/09/2008
17	13/02/2012	0.053652			06/10/2008
18	01/03/2012	0.053652			19/10/2008

Sheet: Sheet1

If you were using B4 and B7 as you date range, the the formula would be

Please Login or Register  to view this content.

This would be If the value in col F is <= col B(earlier date) And (not But) >= col B )later date) Then col G = col C corresponding value to col F, Else col G = col C Offset 1 row value.

I agree with AliGW, The objective does not really follow a logical pattern. and the formula would need to be tailored to each specific entry because of the date range variations. Maybe you can put some thought into it and give us a clearer picture of what you want to do.

Originally Posted by dan_apple

(Re post by JLGWhiz)

What you have explained here is exactly what I am looking for.
You don't think it possible to have a single formula (which applies for all col F entries) like what you have suggested but the logical test earlier and later date (i.e.: the date range) changes when no value is found to complete the calculation?

I am not good enough with formulas to write one that would do what you want. The varying date ranges are not in a consistent pattern, and would need some sort of criteria established to determine when to change the range and what to change it to. As I look at the example, I cannot see that logical pattern and the narrative in he OP does not gve any clues to the reasoning used to select the specific ranges. I won't say it can't be done, I will just say that I can't do it with the current information.

Dan - thank you for your PM. No, you may not open a new thread on this issue, however you can:

1. Change the thread title to make it clearer.
2. Add more detail to the thread by posting to it again.

That example in the second file is much clearer as to what you are trying to do. Unfurtunately, as I said before, I am not good enough with formulas to offer a solution, but I am sure someone on this site can. It will likely have to be an array formula using a VLookup. But I will leave that to the formula gurus. Thanks for the clarification.

YOu didn't change the thread title to give clearer details of what you are trying to do - I advised this back in post #9.

Administrative Note:

To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.