Convert list of addresses

[JG_Berson]

This is a 2 steps request.

First, I want to select and input a list of addresses (of values, not text) into a cell like this  a3,b5,d55,k20 and have it display exactly like that. Not as a value or a formula.

Next in another cell, I need to calculate the MAX value in that list.

Do any of you see a simple way to do that?

jgb

[#2]

@JG_Berson This is pretty simple but does require Excel 365 (and TBH I don't recommend this and would recommend re-evaluating what you are doing and see if there is a 'better' way):

=LET(in,TEXTSPLIT(A1,","),REDUCE(0,in,LAMBDA(p,I,MAX(p,INDIRECT(TRIM(I))))))

A1 is the text based list of cells

this assumes min of 0 but if all the numbers might be negative then change the '0' after REDUCE to something like -9.9E99

[#3]

@mtarlerThanks and YIKES!!!

I've done some pretty complex formulas in the past, but this is a new one for me.

No doubt it would work, but I can't test or use it as I only have EXCEL 2013.

I will heed your advice to find a different way to display that list.

thanks again

jgb

[#4]

There may be other options available for you. Why list "a3,b5,d55,k20" in a cell and determine the max from those addresses? If you're able to share a sample workbook and your end goal, we may be able to give you some options.

[#5]

@Patrick2788 I agree and suggest as much also.

@JG_Berson as for more simple formula it turns out INDIRECT might work on an array (it originally failed for me but I think that was because I had to add the TRIM).  So here is a more simple formula but still won't work for you:

=MAX(INDIRECT(TRIM(TEXTSPLIT(G1,","))))

but using the old trick to go from text to array may work for you but you will probably have to enter the formula using CTRL-SHIFT-ENTER:

=MAX(INDIRECT(TRIM(MID(SUBSTITUTE(G1,",",REPT(" ",LEN(G1))),(ROW($A$1:INDEX($A:$A,LEN(G1)-LEN(SUBSTITUTE(G1,",",""))+1))-1)*LEN(G1)+1,LEN(G1)))))

I'll cross my fingers for you.

[#6]

Thanks, please uncross fingers.
While that should work, it is far too complex to use. I would need hundreds of instances in my spreadsheet.
Please refer to my reply to @Patrick2788 as to what I am trying to do.

[#7]

@Patrick2788 

This is what I want to do. I only started this sheet yesterday and I am just testing functions at this point.

I am creating a "Critical Path" spreadsheet. I do have a lot of experience with CPM and a lot of, but dated, experience with EXCEL. Nothing out there does what I want, or is corporate based, extensive and way too expensive for what I need to do.

In CPM, or more accurately in this case, "Precedence Method" all work on a project is divided into definable "activities" that are linked in a logical chain and network fashion. No activity can start until all preceding linked activities have completed.

So, with a list of preceding activities and the date they each finish on, the start date of any following activity can be determined, and that start date is based on the completion date any of the preceding linked activities that have completed. (That's the basic theory, and it gets a bit more complex than that.)

That date is called the "Early Start". Add the duration the activity is expected to take, and you get the completion or "Early Finish" date, and that date feeds the next linked activity's early start date, which is the day after. If there is more than one preceding activity, the latest early finish date determines that early start date.

So, in my spreadsheet, I have a cluster of cells for each activity. They contain, among other things, the activities ID, which in my case is a cell reference, the text description, early start date, duration, early finish and a list of preceding activities.

It is the list of preceding activities and the early start that is the focus of this problem.

To keep it simple, the Activity ID is its Early Finish Cell address. I just want to simply type that list in without framing it as a displayed formula.

Like this, in cell, say D5, type in   A3,M7,V66,R3

Then in the Early Start cell, the basic formula would be  =MAX(0,D5)+1  which gives me my Early Start date.
I will probably need another cell to convert the list of Activity ID's to cell addresses.

Show me how to include here the spreadsheet, and I will post it.

jgb

[#8]

This sounds a lot like MS Project...

You may have to host your sample workbook on OneDrive or another host, if the upload button is not available.

[#9]

The old MS Project I knew only did Ghantt charts (bar charts), not CPM. Great for presentations, very poor at planning.
CPM or PM is great for planning, resource allocation, contingency planning and display all that with Ghantt charts, which mine will all do when I complete it. (IF I complete it)

And no "upload" button here.

[#10]

so I first agree that you might want to re-look at MS Project or other project management solutions.
That said, in excel I could see you doing a solution by having a list of all the tasks/sprints and what the next task ID would be. Then you might be able to use LARGE() to find the largest something like [range of end dates]*ISNUMBER(SEARCH([this task ID], [range of next tasks]))

here is a snap shot where I tested it:

[#11]

Interesting. Don't quite see that as a solution, as the list of precedents is quite variable and subject to change. I would have to set up an array like that for each activity. Unless I see that wrong, it is simply not really practical for what I am doing.
However your advice to seek out other PM's is what I'm doing now, as I suspect I won't find a homegrown solution that could work the way I want.
In fact I just found an EXCEL template that looks very similar to what I'm doing, but has a serious rookie error in the early start calculation, and is entirely manual in entering the links, which I wanted to automate.
I'll keep looking.
Thanks.

[#12]

again it would help if we had a template sheet to work with. maybe that template that is similar could be a starting point.
as for my solution, I imagine each row having a task with various information and in column D is the NEXT task (i.e. tasks that this item is dependent on) and then column E would be the date (I just have a number there right now). Then the formula in G6 looks at F6 (the task id for this row and finds all tasks that have this task id listed as a pre-req and then finds the max date for those tasks.

[#13]

@mtarler 

Thanks.  Good news!!!  I found a solution.

Kept me up most of the night and after 2 hours testing this morning, got it to work.

It is not exactly the way I originally wanted, but it gives the exact same result I wanted in one cell.

Originally I wanted to simply type a list of cell refs as text like this    A3,T5,G44   then in another cell calculate the Early Start as a  max(list)+0  value of that string.  The problem was converting that text string into actual cell refs for the MAX() function. Wasn't going to happen.

My eureka moment (4am) was to put the cell refs directly into the Early Start cell and calculate the truncated text string in the list cell. In other words vice-versa to the original plan. I had to enter that list anyway, so which cell it went into didn't matter.

I ended up with the Early Start cell being the input cell as a MAX() formula.

That cell would be =MAX(A3,T5,G44)+1 in cell ES

Then the list was a nested text formula of FORMULATEXT(ES) then MID(that,6,100) to strip off the "=MAX(" (100 is arbitrary) then finally =SUBSTITUTE(all that),")+1","",1) to strip off the trailing ")+1".

=SUBSTITUTE(MID(FORMULATEXT(ES),6,100),")+1","")

and that resulted in converting formula =MAX(A3,T5,G44)+1  into text   A3,T5,G44

Now I have to do something similar to calculate the Late Finish, and that's gonna be tougher.

Thanks again for the help.

jgb

[#14]

I love it. I love seeing outside the box or inside out thinking :) i still think my concept could work for you but I'm happy you have it up and working, because that is what is most important.