Need help with a function in returning a value which is 0 and is last in the
series in non contigous cells of the same row
example

A D G J M P S V
1 0.5 2 1.5 3 1.6 0 0 =function which returns
value of E1

Tried a lot

"Satya" wrote:

Need help with a function in returning a value which is 0 and is last in the
series in non contigous cells of the same row
example

A D G J M P S V
1 0.5 2 1.5 3 1.6 0 0 =function which returns
value of E1 ( I am sorry I need the function in V1)

Tried a lot

Something is wron with me I had been having typos in this message, I need the
value of M1 to return in V1. Sorry guys.

"Satya" wrote:

Need help with a function in returning a value which is 0 and is last in the
series in non contigous cells of the same row
example

A D G J M P S V
1 0.5 2 1.5 3 1.6 0 0 =function which returns
value of E1

Tried a lot

last in the series in non contigous cells

Non contigous cells makes this tricky.

Let's assume the cells of interest are A1, B1, D1, F1, G1

=LOOKUP(2,1/N(INDIRECT({"A1","B1","D1","F1","G1"})),N(INDIRECT ({"A1","B1","D1","F1","G1"})))

--
Biff
Microsoft Excel MVP


"Satya" wrote in message
...
Need help with a function in returning a value which is 0 and is last in
the
series in non contigous cells of the same row
example

A D G J M P S V
1 0.5 2 1.5 3 1.6 0 0 =function which
returns
value of E1

Tried a lot

Hi Valko, that was great, I think it is working, I gotta try it with more
cells though. Will get back if I am stuck

"T. Valko" wrote:

last in the series in non contigous cells

Non contigous cells makes this tricky.

Let's assume the cells of interest are A1, B1, D1, F1, G1

=LOOKUP(2,1/N(INDIRECT({"A1","B1","D1","F1","G1"})),N(INDIRECT ({"A1","B1","D1","F1","G1"})))

--
Biff
Microsoft Excel MVP


"Satya" wrote in message
...
Need help with a function in returning a value which is 0 and is last in
the
series in non contigous cells of the same row
example

A D G J M P S V
1 0.5 2 1.5 3 1.6 0 0 =function which
returns
value of E1

Tried a lot

In your OP, the range of your example had a pattern, every 3rd column,
starting with Column A -
A - D - G - J - M - etc.
Since you just stated that you're going to need additional columns, try
something like this for A to AB:

=LOOKUP(2,1/((MOD(COLUMN(A1:AB1),3)=1)*(A1:AB1)),A1:AB1)



"Satya" wrote in message
...
Hi Valko, that was great, I think it is working, I gotta try it with more
cells though. Will get back if I am stuck

"T. Valko" wrote:

last in the series in non contigous cells

Non contigous cells makes this tricky.

Let's assume the cells of interest are A1, B1, D1, F1, G1


=LOOKUP(2,1/N(INDIRECT({"A1","B1","D1","F1","G1"})),N(INDIRECT ({"A1","B1","D
1","F1","G1"})))

--
Biff
Microsoft Excel MVP


"Satya" wrote in message
...
Need help with a function in returning a value which is 0 and is last
in
the
series in non contigous cells of the same row
example

A D G J M P S V
1 0.5 2 1.5 3 1.6 0 0 =function which
returns
value of E1

Tried a lot

Way to be observant! I didn't notice the pattern.

--
Biff
Microsoft Excel MVP


"Ragdyer" wrote in message
...
In your OP, the range of your example had a pattern, every 3rd column,
starting with Column A -
A - D - G - J - M - etc.
Since you just stated that you're going to need additional columns, try
something like this for A to AB:

=LOOKUP(2,1/((MOD(COLUMN(A1:AB1),3)=1)*(A1:AB1)),A1:AB1)



"Satya" wrote in message
...
Hi Valko, that was great, I think it is working, I gotta try it with more
cells though. Will get back if I am stuck

"T. Valko" wrote:

last in the series in non contigous cells

Non contigous cells makes this tricky.

Let's assume the cells of interest are A1, B1, D1, F1, G1


=LOOKUP(2,1/N(INDIRECT({"A1","B1","D1","F1","G1"})),N(INDIRECT ({"A1","B1","D
1","F1","G1"})))

--
Biff
Microsoft Excel MVP


"Satya" wrote in message
...
Need help with a function in returning a value which is 0 and is
last
in
the
series in non contigous cells of the same row
example

A D G J M P S V
1 0.5 2 1.5 3 1.6 0 0 =function which
returns
value of E1

Tried a lot