Re: This should be easy...

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.

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.

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.

Thanks in advance for any illumination you can offer.

-Ed

.

Relevant Pages

  • Re: This should be easy...
    ... > saying if the range B2:D2 is blank, then column equals zero? ... and the results will be returned as an array which MAX works on. ... > "Total" cell. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Auto populate
    ... I doubled checked my cell range. ... "Oscar" wrote: ... I'm working on a calculator for mortgage payments. ... interest rate on column K that equals to 31.00% in column O instead of having ...
    (microsoft.public.excel.worksheet.functions)
  • Color a cell and a value is automatically assigned to that color.
    ... Color a cell and a value is automatically assigned to that color. ... Greens are worth 5 ... and in D it equals D$2. ... This post is a suggestion for Microsoft, ...
    (microsoft.public.excel.worksheet.functions)
  • Re: If statement with time
    ... "David Biddulph" wrote: ... What value do you see in C7 if you temporarily change the format from time ... when the calculated time cell equals 30. ...
    (microsoft.public.excel.misc)
  • Re: "=" in text file formatting messes with my code loop- more elegant (and faster) solution?
    ... Maybe you could force that cell to be text: ... I like this format ... I can see why Excel doesn't like a cell starting with an equals sign when ... I can't throw a single apostrophe in ...
    (microsoft.public.excel.programming)
Loading