"Ed" wrote in message
ups.com...
Bob,

Thank you. That worked beautifully. I'm trying to understand the
equation, but am having trouble deciphering it (I've never used most of
these functions for anything before). Honestly, I'm not being
intellectually or google-search/excel-help lazy when I say this, but I
really don't understand what's going on in the parenthases once you use
the MAX function.

Here's my thought process:
By using INDEX, you're defining, within a range, what row and column
should be displayed in the cell. So, "Index(B2:D2,1" is say to choose
the current row in the range B2:D2. That's straightforward.


INDEX pulss back the value from a range for a specific row and column. As
you say, the row number is just 1 as there is only 1,l but we have to
calculate the column.


So, and this is where I get really cloudy, "Max(IF(Isblank(B2:D2),0" is
saying if the range B2:D2 is blank, then column equals zero?
Otherwise, "IF(B2:D2="-",0,COLUMN(B2:D2)-1)," is saying if the range is
literally a hyphen???, then it's equal to zero, otherwise it equals the
number of columns in the range minus one??? Also realizing that the
latter info is included in picking out the MAX value.


To find the latest, I am tryning to work out the last (validly) inhabited.
To find that, I test fro a hyphen, if there is one, I use that column
number, else I use 0 (0 will never be MAX). It is an array formula, so by
specifying a range, each cell in that range will be separately evaluated,
and the results will be returned as an array which MAX works on. So the
IF(B2:D2="-" tests B2, then C2, then D2 for a hyphen, and if not a hyphen,
it adds the columne number of B2 *or C2, or D2) to the array, else it adds 0
to the array.

Taking your example
Gamma 10.00 11.00 -
the formula
IF(B2:D2="-",0,COLUMN(B2:D2)-1)
would effectively evaluate as
IF({10.00,11.00,"-"="-",0,{1,2,3}))
which would return an array
{1,2,0}
which MAX evaluates to 2, which when passed to
INDEX(B2:D2,...
picks up the second column, i.e. C2


Besides really wanting to understand how this formula and its functions
work, I wanted to alter it such that I can account for the extra
columns I mentioned in the previous post. What I meant by extra
columns is that there are 3 columns that further describe the data
numerically, but I did not want included in them in the formula for the
"Total" cell.

Just extend the two ranges