Forum Discussion
Help With A Formula
Thank you very much for your help. However, when I copy and paste the formula I get #VALUE! and can't figure out why.
Hi Jordan Miller,
One possibility could be that you have entered the Array Formula using Enter, instead of Control + Shift + Enter.
Other possibilities could include incorrect reference range or incorrect data.
Try entering the Formula with Control + Shift + Enter or else attach a sample file, with test data in place of the actual data.
I have attached the sample file for your reference. Formulas are in P2 and S2.
Do let me know once the problem is solved or you still face some difficulties.
Vijaykumar Shetye,
Panaji, Goa, India
Thank you so much! One last question. Looking at the picture I attached, is there a formula to make L:2 say the last "milestone" that has an "x" by it? Basically, I want L:2 to say "One Year" until I reach the next milestone and once I have reached that milestone it will automatically say Two Years. It currently doesn't do this because I have spaces in between that the formula is reading and saying "0".
Dear Jordan Miller,
Enter the below Array Formula in cell L2.
=INDEX(L$4:L$24,-3+MAX(IF($O$4:$O$24="x",ROW($O$4:$O$24)*IF(LEN($L$4:$L$24)>=1,1,0),0)),1)
Your milestones are to be entered in column L.
Refer attached file for reference.
Change cell references as required.
As before, enter the Array formulas using Control + Shift + Enter instead of Enter.
Since there are no blank cells between entries of x, in column O, I have designed the formula for consecutive entries of x.
Do let me know if this is what you wanted to do.
Regards,
Vijaykumar Shetye,
Panaji, Goa, India
That was exactly what I needed. Thank you so much for your help on both situations.
