Finding the relative position of the date that closet matches a target date

Hi All,

I am trying to figure out a formula that will help me find the relative position of the largest date that is equal to or less than a provided selected date. In the attached you will see a list of projects that have column headers of stages 1 - 5 and a current stage. I am trying to get column H (the Current Stage column) to indicate which stage the project is in based on a selected date.

So far I tried the below formulas (Using Project 4 as example), but neither work for all the possible scenarios for dates.

=INDEX($C$2:$G$2,MATCH($J$3,C6:G6,1))

and

=INDEX($C$2:$G$2,MATCH(MAXIFS(C6:G6,C6:G6,"<="&$J$3),C6:G6,0))


The two issues I am running into is that:

1) dates may not be chronological with stage dates. So stage 1 may have a later date than stage 5 (like Project 3).
2) the same date might be used for multiple stages (Like Project 2 and Project 4).

The two issues make the formulas I came up with produce incorrect results. Does anybody have a suggestion on a formula that might work for all the projects listed?

Hello PetPartner. Welcome to the forum.

Your workbook is apparently protected and is not visible upon download.

Can we try again?

Still not working. All I get is blue screen as if hidden. It is not on the 'Unhide' list.

I've never seen this before.

Calling for help.

Very unlikely.

Since other files are uploading I suspect there is some obscure setting at my end that needs attention ... or there is a "hiccup" in the forum system.

I've called for help. It's near day's end and later so this may take a while.

Hey PetPartner welcome to the forum,

See if this answer helps you. It is a CSE (Control+Shift+Enter) or Array formula. To find the date you want (closest to given in J) you might use this.

=MIN(ABS(C3:G6-$J$3)) (must use CSE to enter)

Then the answer is either plus or minus that minimum. See the attached.

CSE Minimum date.xlsx

@Dave.

I am able to open and view the file without issue. Will save and upload here for you.

Alan

Marvin, I am having the same problem downloading your attachment as well.

Nothing wrong with the workbook as far as I can see. I can see the data and formulae and can edit them.

Can't offer a solution to the problem off the top of my head.

@FR: maybe reload Excel and/or reboot the computer?

@ Alan

Thank you for that. It's still not downloading/opening.

It displays as my PERSONAL.XLSB file does when hidden.

Thank you Trevor. Should have thought of that myself. It did the trick.

Hey Dave,

https://support.office.com/en-us/art...e-a6b40c5bb88b
Office repair tool...

Or
https://www.thewindowsclub.com/excel...g-blank-window

@FR: You're welcome. Thanks for the rep.

Second pair of eyes

Try

Formula:

Please Login or Register  to view this content.

I will do my best.

I rely heavily upon the F9 function key and Evaluate formula feature (Fx).

The following focuses on the case of cell H6 where there are duplicate MAX values in C6:G6.

The "heart" of this formula is the (MAXIFS(C6:G6,C6:G6,"<="&$J$3)=C6:G6) part. MAXIFS of course returns the max value in C6:G6 that is less than or equal to $J$3. That part just mentioned compares that maximum value to all the values in C6:G6 to check for equality. Select just that part above in the formula bar and hit the F9 function key.

You will see an array of TRUE/FALSE {TRUE,TRUE,FALSE,FALSE,FALSE}. If you are not aware of it when TRUE/FALSE is subjected to math operations it coerces T/F into their underlying values of 1/0. Reciprocation does that. 1/(MAXIFS(C6:G6,C6:G6,"<="&$J$3)=C6:G6). In this case it also creates an array of 1s and #DIV/0! errors. Select just the reciprocation 1/(MAXIFS(C6:G6,C6:G6,"<="&$J$3)=C6:G6) in the formula bar and hit the F9 again. You will see ...

{1,1,#DIV/0!,#DIV/0!,#DIV/0!}

LOOKUP will match the right most value in that array that is less than or equal to the lookup value ... 2 in this case. Since LOOKUP is one of the few functions that ignore errors it will match the right most 1. It then returns the corresponding value from the range in the last LOOKUP argument $C$2:$G$2 where the "Stage " headers are. Although there are two qualifying maximum values LOOKUP matches the right most one.

You can also follow the calculation steps using the Evaluate formula feature ... FORMULAS > Evaluate formula. If you are not already familiar with them F9/Fx are helpful for analyzing, trouble shooting and self instruction. I recommend them both whenever I can.

I hope this helps.

You are welcome. Glad to help, and thank you for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Please try at H3

Please Login or Register  to view this content.

Press Ctrl-Shift-Enter for ARRAY formula.


Regards.