This should be easy...

But for some reason I can't get it to work. Here's the spreadsheet
setup:
I've got three columns of data that each describe a single transaction
during separate stages. I've got another column that I'd like to use
for determining the most up-to-date stage total. So, I came up with
the following formula:

IF((AND(A1<>0,B1<>0,C1<>0)),C1,(IF(AND(A1<>0,B1<>0),B1,A1)))

The idea was that if there's a value in columns, A,B, and C, then the
'total' would read from column C (the most up-to-date), but if no value
in C, then it would read from one of the other two based on which had
the more current data. However, I noticed that sometimes my data skips
a stage, so I may not have info in columns A or B; resulting in no
value for the total. I've tried the above equation with "OR" instead
of "AND" as well, but to no avail.

Any ideas?

How about =INDEX(A1:C1,1,MAX(IF(ISBLANK(A1:C1),0,COLUMN(A1:C1))))

as an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jerry" <nosoup4yew@yahoo.com> wrote in message
news:1133298843.999594.231560@g43g2000cwa.googlegroups.com...
> But for some reason I can't get it to work. Here's the spreadsheet
> setup:
> I've got three columns of data that each describe a single transaction
> during separate stages. I've got another column that I'd like to use
> for determining the most up-to-date stage total. So, I came up with
> the following formula:
>
> IF((AND(A1<>0,B1<>0,C1<>0)),C1,(IF(AND(A1<>0,B1<>0),B1,A1)))
>
> The idea was that if there's a value in columns, A,B, and C, then the
> 'total' would read from column C (the most up-to-date), but if no value
> in C, then it would read from one of the other two based on which had
> the more current data. However, I noticed that sometimes my data skips
> a stage, so I may not have info in columns A or B; resulting in no
> value for the total. I've tried the above equation with "OR" instead
> of "AND" as well, but to no avail.
>
> Any ideas?
>

Thanks for your reply. I could not get your solution to work for me
though. Here's a little more info on what I'm looking to do:

First, the equation I'm using with very limited success:
=IF((AND(C3<>0,D3<>0,E3<>0)),E3,(IF(AND(C3<>0,D3<>0),D3,C3)))

The spreadsheet setup:
A B C D E
1 Est1 Est2 Act Total
2 Alpha 5.00 10.00 12.00 12.00
3 Beta 5.00 11.00 5.00
4 Gamma 10.00 11.00 -
5 Delta 10.00 -




As you can see, the above equation only yields the correct Total in the
Alpha row because all the fields have a value. In my actual
spreadsheet there are 3 additional columns between the "Act" and "Rptd"
that should not be counted in my equation, but if you use an array
formula, would proabably have to be included. I'm not really familiar
with arrays, so if you have any advice, I'd really appreciate it.

Jerry,

Here is a variation that caters for the -

=INDEX(B2:D2,1,MAX(IF(ISBLANK(B2:D2),0,IF(B2:D2="-",0,COLUMN(B2:D2)-1))))

I am not sure what you mean by the extra columns.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jerry" <nosoup4yew@yahoo.com> wrote in message
news:1133455753.501219.289960@g49g2000cwa.googlegroups.com...
> Thanks for your reply. I could not get your solution to work for me
> though. Here's a little more info on what I'm looking to do:
>
> First, the equation I'm using with very limited success:
> =IF((AND(C3<>0,D3<>0,E3<>0)),E3,(IF(AND(C3<>0,D3<>0),D3,C3)))
>
> The spreadsheet setup:
> A B C D E
> 1 Est1 Est2 Act Total
> 2 Alpha 5.00 10.00 12.00 12.00
> 3 Beta 5.00 11.00 5.00
> 4 Gamma 10.00 11.00 -
> 5 Delta 10.00 -
>
>
>
>
> As you can see, the above equation only yields the correct Total in the
> Alpha row because all the fields have a value. In my actual
> spreadsheet there are 3 additional columns between the "Act" and "Rptd"
> that should not be counted in my equation, but if you use an array
> formula, would proabably have to be included. I'm not really familiar
> with arrays, so if you have any advice, I'd really appreciate it.
>

Why not use the "Large" function
the following example checks for the "largest" value in a selection:
=LARGE(B4 D4,1)
Put a "colon" between B4 and D4 - no spaces. I get a "smiley face" if I
use a colon.
This would be for column B thru D in row 4. The "1" is the for the
largest if you wanted the
second largest use a 2. Excel has a good help info on this function


--
wjohnson
------------------------------------------------------------------------
wjohnson's Profile: http://www.hightechtalks.com/m390
View this thread: http://www.hightechtalks.com/t2297300

Why not use the "Large" function
the following example checks for the "largest" value in a selection:
=LARGE(B4:D4,1)
This would be for column B thru D in row 4. The "1" is the for the
largest if you wanted the
second largest use a 2. Excel has a good help info on this function


--
wjohnson
------------------------------------------------------------------------
wjohnson's Profile: http://www.hightechtalks.com/m390
View this thread: http://www.hightechtalks.com/t2297300

Because he wants the last one in the series, not the biggest

--

HTH

RP
(remove nothere from the email address if mailing direct)


"wjohnson" <wjohnson.1zeca1@no-mx.forums.yourdomain.com.au> wrote in message
news:wjohnson.1zeca1@no-mx.forums.yourdomain.com.au...
>
> Why not use the "Large" function
> the following example checks for the "largest" value in a selection:
> =LARGE(B4:D4,1)
> This would be for column B thru D in row 4. The "1" is the for the
> largest if you wanted the
> second largest use a 2. Excel has a good help info on this function
>
>
> --
> wjohnson
> ------------------------------------------------------------------------
> wjohnson's Profile: http://www.hightechtalks.com/m390
> View this thread: http://www.hightechtalks.com/t2297300
>

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

"Ed" <nosoup4yew@yahoo.com> wrote in message
news:1133551202.177555.22810@o13g2000cwo.googlegroups.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

Thanks again. I think I'm understanding the logic now.

The only other problem I'm having though is the "extra columns." Is
there a way to exclude them from the range? There are times when they
might have values that would result in an erroneous Total since
expanding the range of your equation would include them in the Total.
The purpose of these columns is more for an internal "check," so they
are necessary.

-Ed