Formula Help Please

[Ken]

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?

 

[FSt1]

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

 

[Carim]

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

 

[Tyro]

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:L2,"<>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)

 

[T. Valko]

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:L1>0),A1:L1),"")

 

[Ken]

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

 

[T. Valko]

"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:L1>0),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:L1>0),A1:L1),0)

I've slightly changed the formula to:

=IF(COUNTIF(A1:L1,">0"),LOOKUP(2,1/(A1:L1>0),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.

 

[Tyro]

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

 

[Tyro]

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

 

[Ragdyer]

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

 

[Tyro]

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

 

[Ragdyer]

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

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

 

[T. Valko]

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.

 

[Tyro]

Apparently there are blanks.

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

 

[T. Valko]

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:L1>0)/(ISNUMBER(A1:L1)),A1:L1),"")

That takes care of *EVERYTHING* :

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

<vbg>

 

[Ragdyer]

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