Formula Help Please
 

A row of 12 cells representing 12 months, all of which have an initial
value of zero. Each month a value is inserted into one cell, with the value
remaining as each month passes. There is another cell in a separate
worksheet into which I want to place the most recently entered value from
the 12 cell row, i.e. it will overwrite the previous value.

I've tried using 'if' statements and various formula listed in the Help
files but without success. Can anyone offer a solution please?

Formula Help Please
 

hi,
not entirely sure what you are asking but if i understand....
if the worksheet is in the the same workbook then all you need to do is draw
the value of the first sheet into the second sheets(or some variation there
of...)
on sheet 2 enter(in any cell)
=sheet1!A1 'or the cell address desired.
sheet 2 will update as sheet1!A1.value changes.
you will need a formula for each of the 12 months.
edit to fit your data.
regards
FSt1


"Ken" wrote:

A row of 12 cells representing 12 months, all of which have an initial
value of zero. Each month a value is inserted into one cell, with the value
remaining as each month passes. There is another cell in a separate
worksheet into which I want to place the most recently entered value from
the 12 cell row, i.e. it will overwrite the previous value.

I've tried using 'if' statements and various formula listed in the Help
files but without success. Can anyone offer a solution please?

Formula Help Please
 

Hi Ken,

If I am not mistaken, you are looking for the last value <0 ...
Please adapt to your needs ...

=LOOKUP(2,1/(A1:L1<0),A1:L1)

HTH

Formula Help Please
 

Assuming your 12 monthly values are in A2:L2 on Sheet1, You can use this
formula in versions prior to Excel 2007:

=IF(ISERROR(OFFSET(Sheet1!A2,0,COUNTIF(Sheet1!A2:L 2,"<0")-1,1,1)),0,OFFSET(Sheet1!A2,0,COUNTIF(Sheet1!A2:L2, "<0")-1,1,1))

In Excel 2007, you can use

=IFERROR(OFFSET(Sheet1!A2,0,COUNTIF(Sheet1!A2:L2," <0")-1,1,1),0)


"Ken" wrote in message
...
A row of 12 cells representing 12 months, all of which have an initial
value of zero. Each month a value is inserted into one cell, with the
value
remaining as each month passes. There is another cell in a separate
worksheet into which I want to place the most recently entered value from
the 12 cell row, i.e. it will overwrite the previous value.

I've tried using 'if' statements and various formula listed in the Help
files but without success. Can anyone offer a solution please?

Formula Help Please
 

In other words you want the *last* value in the range that is not 0?

Are there any negative numbers in the range?
Are there any empty cells in the range?
Are there any TEXT entries in the range?

If the answer to all those questions is NO, try this:

=IF(COUNTIF(A1:L1,0)<12,LOOKUP(2,1/(A1:L10),A1:L1),"")

--
Biff
Microsoft Excel MVP


"Ken" wrote in message
...
A row of 12 cells representing 12 months, all of which have an initial
value of zero. Each month a value is inserted into one cell, with the
value
remaining as each month passes. There is another cell in a separate
worksheet into which I want to place the most recently entered value from
the 12 cell row, i.e. it will overwrite the previous value.

I've tried using 'if' statements and various formula listed in the Help
files but without success. Can anyone offer a solution please?

Formula Help Please
 

Thank you all for your responses.

FSt1 Entries in cells will not change

HTH Your formula returns the error "A value is not available to the
formula or function".

Tyro The formula works very well . Thanks. If you have the time, and
inclination, would you post a word description of the formula - I cannot
follow it entirely.

Biff The three answers are 'no', but your formula returns the same error
"A value is not available to the formula or function". I tried to follow it
through but stumbled on the double quotation marks inside the final
parentheses. What do they mean? Should they have something between them?

Regards, Ken

Formula Help Please
 

"A value is not available to the formula or function".

Hmmm...

I've never seen that error before so I really don't know what it means.

the double quotation marks inside the final parentheses. What do they mean?

They are used to return a blank *until* a number 0 is entered in the range.
If you want some other result just replace the "" with whatever you want.
Just remember that if you want some TEXT value returned to enclose the TEXT
string in quotes like this:

=IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10),A1:L1),"no data")

If you want a 0 returned then you don't need the quotes:

=IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10),A1:L1),0)

I've slightly changed the formula to:

=IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10),A1:L1),"")

Here's a very small sample file that demonstrates this:

lastnum.xls 14kb

http://cjoint.com/?bghmSfGDPm

Enter some numbers (0) replacing the 0s and you'll see that it works.


--
Biff
Microsoft Excel MVP


"Ken" wrote in message
...
Thank you all for your responses.

FSt1 Entries in cells will not change

HTH Your formula returns the error "A value is not available to the
formula or function".

Tyro The formula works very well . Thanks. If you have the time, and
inclination, would you post a word description of the formula - I cannot
follow it entirely.

Biff The three answers are 'no', but your formula returns the same
error
"A value is not available to the formula or function". I tried to follow
it
through but stumbled on the double quotation marks inside the final
parentheses. What do they mean? Should they have something between them?

Regards, Ken

Formula Help Please
 

You formula produces #N/A if all 12 entries are blank. He's referring to the
description of #N/A

"T. Valko" wrote in message
...
"A value is not available to the formula or function".

Hmmm...

I've never seen that error before so I really don't know what it means.

the double quotation marks inside the final parentheses. What do they
mean?

They are used to return a blank *until* a number 0 is entered in the
range. If you want some other result just replace the "" with whatever you
want. Just remember that if you want some TEXT value returned to enclose
the TEXT string in quotes like this:

=IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10),A1:L1),"no data")

If you want a 0 returned then you don't need the quotes:

=IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10),A1:L1),0)

I've slightly changed the formula to:

=IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10),A1:L1),"")

Here's a very small sample file that demonstrates this:

lastnum.xls 14kb

http://cjoint.com/?bghmSfGDPm

Enter some numbers (0) replacing the 0s and you'll see that it works.


--
Biff
Microsoft Excel MVP


"Ken" wrote in message
...
Thank you all for your responses.

FSt1 Entries in cells will not change

HTH Your formula returns the error "A value is not available to the
formula or function".

Tyro The formula works very well . Thanks. If you have the time, and
inclination, would you post a word description of the formula - I cannot
follow it entirely.

Biff The three answers are 'no', but your formula returns the same
error
"A value is not available to the formula or function". I tried to follow
it
through but stumbled on the double quotation marks inside the final
parentheses. What do they mean? Should they have something between them?

Regards, Ken

Formula Help Please
 

You're getting an #N/A error from 2 of the formulas. Once again, the
formulas below presume your data is in A2:L2 on sheet1 and your formula is
on some other sheet.
Here is HRH's formula, the best of the formulas, adjusted to take care of
that. For errors you can change the ,0 (comma 0) in the formulas to ,""
(comma empty string) to show a blank.

All Excel versions:
=IF(ISERROR(LOOKUP(2,1/(Sheet1!A2:L2<0),Sheet1!A2:L2)),0,LOOKUP(2,1/(Sheet1!A2:L2<0),Sheet1!A2:L2))

Excel 2007 only:
=IFERROR(LOOKUP(2,1/(Sheet1!A2:L2<0),Sheet1!A2:L2),0)

tyro

"Ken" wrote in message
...
A row of 12 cells representing 12 months, all of which have an initial
value of zero. Each month a value is inserted into one cell, with the
value
remaining as each month passes. There is another cell in a separate
worksheet into which I want to place the most recently entered value from
the 12 cell row, i.e. it will overwrite the previous value.

I've tried using 'if' statements and various formula listed in the Help
files but without success. Can anyone offer a solution please?

Formula Help Please
 

Just tripped over this thread and would like to comment.

The first sentence of the OP states all cells have an *initial* value of
zero.

It seems that you're reading into the meaning of that sentence certain
suppositions.
The other responders are doing the same, making suppositions.
Many threads here require guesses in order to make suggestions, where the
OPs fail to include all requirements.

Why are you acting as if *your* guesses are the only correct ones, and
everyone else's is wrong.

I read that the cells are *initially* filled with zeroes, which are returns
of existing formulas.
In which case *your best* formula FAILS!

Many of us here come up with varying suggestions, dependant on our own
understanding of the OPs request.
In such cases, we simply state what differences our suggestions will take
into consideration, compared to suggestions made by other responders.

We don't berate their interpretation, we just state the distinctions, and
await the OP's feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Tyro" wrote in message
. net...
You're getting an #N/A error from 2 of the formulas. Once again, the
formulas below presume your data is in A2:L2 on sheet1 and your formula is
on some other sheet.
Here is HRH's formula, the best of the formulas, adjusted to take care of
that. For errors you can change the ,0 (comma 0) in the formulas to ,""
(comma empty string) to show a blank.

All Excel versions:

=IF(ISERROR(LOOKUP(2,1/(Sheet1!A2:L2<0),Sheet1!A2:L2)),0,LOOKUP(2,1/(Sheet1
!A2:L2<0),Sheet1!A2:L2))

Excel 2007 only:
=IFERROR(LOOKUP(2,1/(Sheet1!A2:L2<0),Sheet1!A2:L2),0)

tyro

"Ken" wrote in message
...
A row of 12 cells representing 12 months, all of which have an initial
value of zero. Each month a value is inserted into one cell, with the
value
remaining as each month passes. There is another cell in a separate
worksheet into which I want to place the most recently entered value
from
the 12 cell row, i.e. it will overwrite the previous value.

I've tried using 'if' statements and various formula listed in the Help
files but without success. Can anyone offer a solution please?

Formula Help Please
 

I filled the cells with formulas that return 0 and the formula works fine.

"Ragdyer" wrote in message
...
Just tripped over this thread and would like to comment.

The first sentence of the OP states all cells have an *initial* value of
zero.

It seems that you're reading into the meaning of that sentence certain
suppositions.
The other responders are doing the same, making suppositions.
Many threads here require guesses in order to make suggestions, where the
OPs fail to include all requirements.

Why are you acting as if *your* guesses are the only correct ones, and
everyone else's is wrong.

I read that the cells are *initially* filled with zeroes, which are
returns
of existing formulas.
In which case *your best* formula FAILS!

Many of us here come up with varying suggestions, dependant on our own
understanding of the OPs request.
In such cases, we simply state what differences our suggestions will take
into consideration, compared to suggestions made by other responders.

We don't berate their interpretation, we just state the distinctions, and
await the OP's feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Tyro" wrote in message
. net...
You're getting an #N/A error from 2 of the formulas. Once again, the
formulas below presume your data is in A2:L2 on sheet1 and your formula
is
on some other sheet.
Here is HRH's formula, the best of the formulas, adjusted to take care of
that. For errors you can change the ,0 (comma 0) in the formulas to ,""
(comma empty string) to show a blank.

All Excel versions:

=IF(ISERROR(LOOKUP(2,1/(Sheet1!A2:L2<0),Sheet1!A2:L2)),0,LOOKUP(2,1/(Sheet1
!A2:L2<0),Sheet1!A2:L2))

Excel 2007 only:
=IFERROR(LOOKUP(2,1/(Sheet1!A2:L2<0),Sheet1!A2:L2),0)

tyro

"Ken" wrote in message
...
A row of 12 cells representing 12 months, all of which have an initial
value of zero. Each month a value is inserted into one cell, with the
value
remaining as each month passes. There is another cell in a separate
worksheet into which I want to place the most recently entered value
from
the 12 cell row, i.e. it will overwrite the previous value.

I've tried using 'if' statements and various formula listed in the Help
files but without success. Can anyone offer a solution please?

Formula Help Please
 

You're right, I didn't realize you were referencing Row2.

As to the rest of the post ... it's nice to be nice!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"Tyro" wrote in message
et...
I filled the cells with formulas that return 0 and the formula works fine.

"Ragdyer" wrote in message
...
Just tripped over this thread and would like to comment.

The first sentence of the OP states all cells have an *initial* value of
zero.

It seems that you're reading into the meaning of that sentence certain
suppositions.
The other responders are doing the same, making suppositions.
Many threads here require guesses in order to make suggestions, where
the
OPs fail to include all requirements.

Why are you acting as if *your* guesses are the only correct ones, and
everyone else's is wrong.

I read that the cells are *initially* filled with zeroes, which are
returns
of existing formulas.
In which case *your best* formula FAILS!

Many of us here come up with varying suggestions, dependant on our own
understanding of the OPs request.
In such cases, we simply state what differences our suggestions will
take
into consideration, compared to suggestions made by other responders.

We don't berate their interpretation, we just state the distinctions,
and
await the OP's feed-back.
--
Regards,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"Tyro" wrote in message
. net...
You're getting an #N/A error from 2 of the formulas. Once again, the
formulas below presume your data is in A2:L2 on sheet1 and your formula
is
on some other sheet.
Here is HRH's formula, the best of the formulas, adjusted to take care
of
that. For errors you can change the ,0 (comma 0) in the formulas to ,""
(comma empty string) to show a blank.

All Excel versions:


=IF(ISERROR(LOOKUP(2,1/(Sheet1!A2:L2<0),Sheet1!A2:L2)),0,LOOKUP(2,1/(Sheet1
!A2:L2<0),Sheet1!A2:L2))

Excel 2007 only:
=IFERROR(LOOKUP(2,1/(Sheet1!A2:L2<0),Sheet1!A2:L2),0)

tyro

"Ken" wrote in message
...
A row of 12 cells representing 12 months, all of which have an
initial
value of zero. Each month a value is inserted into one cell, with the
value
remaining as each month passes. There is another cell in a separate
worksheet into which I want to place the most recently entered value
from
the 12 cell row, i.e. it will overwrite the previous value.

I've tried using 'if' statements and various formula listed in the
Help
files but without success. Can anyone offer a solution please?

Formula Help Please
 

You formula produces #N/A if all 12 entries are blank.

The first formula would do that but the OP said there were no empty cells.

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
et...
You formula produces #N/A if all 12 entries are blank. He's referring to
the description of #N/A

"T. Valko" wrote in message
...
"A value is not available to the formula or function".

Hmmm...

I've never seen that error before so I really don't know what it means.

the double quotation marks inside the final parentheses. What do they
mean?

They are used to return a blank *until* a number 0 is entered in the
range. If you want some other result just replace the "" with whatever
you want. Just remember that if you want some TEXT value returned to
enclose the TEXT string in quotes like this:

=IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10),A1:L1),"no data")

If you want a 0 returned then you don't need the quotes:

=IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10),A1:L1),0)

I've slightly changed the formula to:

=IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10),A1:L1),"")

Here's a very small sample file that demonstrates this:

lastnum.xls 14kb

http://cjoint.com/?bghmSfGDPm

Enter some numbers (0) replacing the 0s and you'll see that it works.


--
Biff
Microsoft Excel MVP


"Ken" wrote in message
...
Thank you all for your responses.

FSt1 Entries in cells will not change

HTH Your formula returns the error "A value is not available to the
formula or function".

Tyro The formula works very well . Thanks. If you have the time, and
inclination, would you post a word description of the formula - I cannot
follow it entirely.

Biff The three answers are 'no', but your formula returns the same
error
"A value is not available to the formula or function". I tried to follow
it
through but stumbled on the double quotation marks inside the final
parentheses. What do they mean? Should they have something between them?

Regards, Ken

Formula Help Please
 

Apparently there are blanks. :)
"T. Valko" wrote in message
...
You formula produces #N/A if all 12 entries are blank.

The first formula would do that but the OP said there were no empty cells.

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
et...
You formula produces #N/A if all 12 entries are blank. He's referring to
the description of #N/A

"T. Valko" wrote in message
...
"A value is not available to the formula or function".

Hmmm...

I've never seen that error before so I really don't know what it means.

the double quotation marks inside the final parentheses. What do they
mean?

They are used to return a blank *until* a number 0 is entered in the
range. If you want some other result just replace the "" with whatever
you want. Just remember that if you want some TEXT value returned to
enclose the TEXT string in quotes like this:

=IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10),A1:L1),"no data")

If you want a 0 returned then you don't need the quotes:

=IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10),A1:L1),0)

I've slightly changed the formula to:

=IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10),A1:L1),"")

Here's a very small sample file that demonstrates this:

lastnum.xls 14kb

http://cjoint.com/?bghmSfGDPm

Enter some numbers (0) replacing the 0s and you'll see that it works.


--
Biff
Microsoft Excel MVP


"Ken" wrote in message
...
Thank you all for your responses.

FSt1 Entries in cells will not change

HTH Your formula returns the error "A value is not available to the
formula or function".

Tyro The formula works very well . Thanks. If you have the time, and
inclination, would you post a word description of the formula - I
cannot
follow it entirely.

Biff The three answers are 'no', but your formula returns the same
error
"A value is not available to the formula or function". I tried to
follow it
through but stumbled on the double quotation marks inside the final
parentheses. What do they mean? Should they have something between
them?

Regards, Ken

Formula Help Please
 

How can there be blanks (I'm assuming you mean formula blanks "") if:

A row of 12 cells representing 12 months, all of which have an initial
value of zero.

I'm pretty sure this is overkill for the task at-hand but I just went
through this same scenario with another person:

=IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10)/(ISNUMBER(A1:L1)),A1:L1),"")

That takes care of *EVERYTHING* :

empty cells
text
0s
formula blanks
errors
booleans
the kitchen sink!

<vbg

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
. net...
Apparently there are blanks. :)
"T. Valko" wrote in message
...
You formula produces #N/A if all 12 entries are blank.

The first formula would do that but the OP said there were no empty
cells.

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
et...
You formula produces #N/A if all 12 entries are blank. He's referring to
the description of #N/A

"T. Valko" wrote in message
...
"A value is not available to the formula or function".

Hmmm...

I've never seen that error before so I really don't know what it means.

the double quotation marks inside the final parentheses. What do they
mean?

They are used to return a blank *until* a number 0 is entered in the
range. If you want some other result just replace the "" with whatever
you want. Just remember that if you want some TEXT value returned to
enclose the TEXT string in quotes like this:

=IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10),A1:L1),"no data")

If you want a 0 returned then you don't need the quotes:

=IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10),A1:L1),0)

I've slightly changed the formula to:

=IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10),A1:L1),"")

Here's a very small sample file that demonstrates this:

lastnum.xls 14kb

http://cjoint.com/?bghmSfGDPm

Enter some numbers (0) replacing the 0s and you'll see that it works.


--
Biff
Microsoft Excel MVP


"Ken" wrote in message
...
Thank you all for your responses.

FSt1 Entries in cells will not change

HTH Your formula returns the error "A value is not available to the
formula or function".

Tyro The formula works very well . Thanks. If you have the time, and
inclination, would you post a word description of the formula - I
cannot
follow it entirely.

Biff The three answers are 'no', but your formula returns the same
error
"A value is not available to the formula or function". I tried to
follow it
through but stumbled on the double quotation marks inside the final
parentheses. What do they mean? Should they have something between
them?

Regards, Ken

Formula Help Please
 

Around here, I'm sure the OPs get as much entertainment as education!<bg
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"T. Valko" wrote in message
...
How can there be blanks (I'm assuming you mean formula blanks "") if:

A row of 12 cells representing 12 months, all of which have an initial
value of zero.

I'm pretty sure this is overkill for the task at-hand but I just went
through this same scenario with another person:

=IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10)/(ISNUMBER(A1:L1)),A1:L1),"")

That takes care of *EVERYTHING* :

empty cells
text
0s
formula blanks
errors
booleans
the kitchen sink!

<vbg

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
. net...
Apparently there are blanks. :)
"T. Valko" wrote in message
...
You formula produces #N/A if all 12 entries are blank.

The first formula would do that but the OP said there were no empty
cells.

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
et...
You formula produces #N/A if all 12 entries are blank. He's referring
to
the description of #N/A

"T. Valko" wrote in message
...
"A value is not available to the formula or function".

Hmmm...

I've never seen that error before so I really don't know what it
means.

the double quotation marks inside the final parentheses. What do they
mean?

They are used to return a blank *until* a number 0 is entered in the
range. If you want some other result just replace the "" with
whatever
you want. Just remember that if you want some TEXT value returned to
enclose the TEXT string in quotes like this:

=IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10),A1:L1),"no data")

If you want a 0 returned then you don't need the quotes:

=IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10),A1:L1),0)

I've slightly changed the formula to:

=IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10),A1:L1),"")

Here's a very small sample file that demonstrates this:

lastnum.xls 14kb

http://cjoint.com/?bghmSfGDPm

Enter some numbers (0) replacing the 0s and you'll see that it
works.


--
Biff
Microsoft Excel MVP


"Ken" wrote in message
...
Thank you all for your responses.

FSt1 Entries in cells will not change

HTH Your formula returns the error "A value is not available to
the
formula or function".

Tyro The formula works very well . Thanks. If you have the time,
and
inclination, would you post a word description of the formula - I
cannot
follow it entirely.

Biff The three answers are 'no', but your formula returns the
same
error
"A value is not available to the formula or function". I tried to
follow it
through but stumbled on the double quotation marks inside the final
parentheses. What do they mean? Should they have something between
them?

Regards, Ken