CODEWITHSUNDEEP
excel
Sinkerhawk
Sinkerhawk

Reputation: 3

Excel Name Manager Formulas

I'm trying to duplicate the example in the MS Gantt chart for my own project,...

(https://templates.office.com/en-us/Gantt-project-planner-TM02887601?ui=en-US&rs=en-US&ad=US)

but I'm really have a tough time understanding the formulas they're using. In one, they have it equaling back on itself and that cell is the header row.

=('Project Planner'!B$4=MEDIAN('Project Planner'!B$4,'Project Planner'!$E1,'Project Planner'!$E1+'Project Planner'!$F1).....

Also, Project Planner B$4 is the first cell in the header row and the column contains non-numerical values so I'm confused why it's part of a MEDIAN function. My assumption is that has something to do with an array, but it's not very intuitive. Also, in the same code they use a greater than size in the calculation which is confusing to me:

*('Project Planner'!$G1>0)

I'm not expecting anyone to break this down for me. What I really need is a good website online to help try to explain what's actually going on here. I've tried searching myself, but all I'm getting is examples of named references that's very basic. What I need is more info on how to build these advanced references in the "Refers to:" field. Thanks!

Upvotes: 0

Views: 185

Answers (2)

Zeeker
Zeeker

Reputation: 11

Assuming this is taken directly from Excel's example Gantt Chart, I've been looking at this exact same formula myself and it's been boggling my mind until just now. So for anyone else stumped by this, here's exactly what it means:

THIS:

='Project Planner'!Z$6=MEDIAN('Project Planner'!Z$6,'Project Planner'!$D8,'Project Planner'!$D8+'Project Planner'!$G8-1)

EQUALS THIS:

COLUMN HEADER = MEDIAN(COLUMN HEADER,LENGTH OF ACTIVITY, ACTIVITY START TIME + ACTIVITY END TIME -1)

Example of math breakdown within the formula itself

Median means middle, so since all the data going into the formula is numerical, what the formula is doing within the parenthesis is gathering all the numbers and putting them in order by smallest to largest. (I will explain the "-1" in a moment.) From there, the formula is determining if the column number falls inside the set of numbers - not at the beginning or the end, but within them.

So in the example screenshot where the activity starts at 15 with a length of 5.5, the formula is rotating through every column (1, 2, 3, 4, 5, etc.) to check if it should highlight the cell within that column.

In the example link, the formula is asking and doing the following:

Does your activity include 14? Formula is False, No highlight.

Does your activity include 15? Formula is True, Highlight!

Does your activity include 16? Formula is True, Highlight!

Does your activity include 17? Formula is True, Highlight!

Does your activity include 18? Formula is True, Highlight!

Does your activity include 19? Formula is True, Highlight!

Does your activity include 20? Formula is False, No highlight.

The "-1" in the formula subtracts one unit much like you would with time. So in this example, even though the activity ends at 20.5, you'd want to highlight up until that cell, but not highlight the cell itself.

Sorry so wordy and awkward, but I hope this is enough to help others!

Upvotes: 1

Sinkerhawk
Sinkerhawk

Reputation: 3

I think I've resolved my own questions here. I found this deskbright.com/excel/true-false-excel

Upvotes: 0

Related Questions