Re: This should be easy...

"Ed" <nosoup4yew@xxxxxxxxx> wrote in message
news:1133551202.177555.22810@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> 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


.

Relevant Pages

  • RE: macro needed for non-blank cells
    ... OK...back to the LOOKUP. ... The array only returns 1's and errors. ... This formula tests if the last non-blank cell in A1:A100 equals zero ...
    (microsoft.public.excel.worksheet.functions)
  • Re: This should be easy...
    ... saying if the range B2:D2 is blank, then column equals zero? ... "Total" cell. ...
    (microsoft.public.excel.worksheet.functions)
  • RE: macro needed for non-blank cells
    ... Open a new workbook and put this formula in cell B1 ... OK...back to the LOOKUP. ... The array only returns 1's and errors. ... This formula tests if the last non-blank cell in A1:A100 equals zero ...
    (microsoft.public.excel.worksheet.functions)
  • Re: IF only.....IF only....
    ... it represents the 'column index'. ... >>> figure in cell D, and, using the variables below, automatically ... >>> If cell D equals 7-12 then cell E should automatically read 15 ... >> The array constant in the above can also be set up as a range on ...
    (microsoft.public.excel.worksheet.functions)
  • RE: User List Box - List from Hidden range - VBA worng
    ... In your for loop, ... range is only 1 column wide this will be a single cell). ... Basic Editor type the word on it's own in lower case on a line and hit enter. ... in the array reference - and an array starts with element 0. ...
    (microsoft.public.excel.programming)