EXCEL FUNCTION OR FORMULA
 

I have week1 week2 week3 week4 total
I want a formula and/or function to pick up the last week populated with a
higher amount than null/zero into the total field

EXCEL FUNCTION OR FORMULA
 

Well, first of all, no caps, please. It is considered yelling. Secondly give
us an example of your data and what you want to see as a result. We are not
deaf mind readers.

Tyro

"sue" wrote in message
...
I have week1 week2 week3 week4 total
I want a formula and/or function to pick up the last week populated with a
higher amount than null/zero into the total field

EXCEL FUNCTION OR FORMULA
 

week1 week2 week3 week4 total
1500 3000 3000
the total column picks up the last column other than zero

"Tyro" wrote:

Well, first of all, no caps, please. It is considered yelling. Secondly give
us an example of your data and what you want to see as a result. We are not
deaf mind readers.

Tyro

"sue" wrote in message
...
I have week1 week2 week3 week4 total
I want a formula and/or function to pick up the last week populated with a
higher amount than null/zero into the total field

EXCEL FUNCTION OR FORMULA
 

Try this:

=LOOKUP(2,1/(A2:J2<0),A2:J2)

Adjust range references to suit your situation.

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"sue" wrote in message
...
week1 week2 week3 week4 total
1500 3000 3000
the total column picks up the last column other than zero

"Tyro" wrote:

Well, first of all, no caps, please. It is considered yelling. Secondly
give
us an example of your data and what you want to see as a result. We are
not
deaf mind readers.

Tyro

"sue" wrote in message
...
I have week1 week2 week3 week4 total
I want a formula and/or function to pick up the last week populated
with a
higher amount than null/zero into the total field

EXCEL FUNCTION OR FORMULA
 

One way which might suffice

Assuming the cols you posted are cols B to F, data from row2 down, col F =
Total

Place this in F2's formula bar, array-enter the formula by pressing
CTRL+SHIFT+ENTER (instead of just pressing ENTER):
=INDEX(B2:E2,MAX((B2:E2<"")*(COLUMN(B2:E2)))-1)
Copy F2 down as far as required

If you need an error trap to return neat blanks: "" instead of #VALUE! for
blank data rows , put instead in F2, array-entered:
=IF(MAX((B2:E2<"")*(COLUMN(B2:E2)))=0,"",INDEX(B2 :E2,MAX((B2:E2<"")*(COLUMN(B2:E2)))-1))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"sue" wrote in message
...
I have week1 week2 week3 week4 total
I want a formula and/or function to pick up the last week populated with a
higher amount than null/zero into the total field

excel function or formula
 

Thanks for the critique. My example was below
week 1 week2 week3 week4 total
1500 3000 3000
I would like for the total column to have the last total from the weekX.

"Tyro" wrote:

Well, first of all, no caps, please. It is considered yelling. Secondly give
us an example of your data and what you want to see as a result. We are not
deaf mind readers.

Tyro

"sue" wrote in message
...
I have week1 week2 week3 week4 total
I want a formula and/or function to pick up the last week populated with a
higher amount than null/zero into the total field

EXCEL FUNCTION OR FORMULA
 

If headings are in row 1 and data starts in A2, put this in E2:

=LOOKUP(10^100,A2:D2)

Will return the last non-zero entry in the range.

Hope this helps.

Pete

On Feb 13, 1:39*am, sue wrote:
week1 * *week2 * week3 * week4 * *total
1500 * * *3000 * * * * * * * * * * * * * * * 3000
the total column picks up the last column other than zero



"Tyro" wrote:
Well, first of all, no caps, please. It is considered yelling. Secondly give
us an example of your data and what you want to see as a result. We are not
deaf mind readers.

Tyro

"sue" wrote in message
...
I have *week1 *week2 *week3 week4 total
I want a formula and/or function to pick up the last week populated with a
higher amount than null/zero into the total field- Hide quoted text -

- Show quoted text -

excel function or formula
 

thx for prch
week1 week2 week3 week4 week5 total
1500 3000 0 0 0 3000
i would like the total column to have the latest week that does not have
null/zero. i like your cynicism to boot esp if you have a feasible answer

"Tyro" wrote:

Well, first of all, no caps, please. It is considered yelling. Secondly give
us an example of your data and what you want to see as a result. We are not
deaf mind readers.

Tyro

"sue" wrote in message
...
I have week1 week2 week3 week4 total
I want a formula and/or function to pick up the last week populated with a
higher amount than null/zero into the total field

EXCEL FUNCTION OR FORMULA
 

worked like a charm thanks

"Pete_UK" wrote:

If headings are in row 1 and data starts in A2, put this in E2:

=LOOKUP(10^100,A2:D2)

Will return the last non-zero entry in the range.

Hope this helps.

Pete

On Feb 13, 1:39 am, sue wrote:
week1 week2 week3 week4 total
1500 3000 3000
the total column picks up the last column other than zero



"Tyro" wrote:
Well, first of all, no caps, please. It is considered yelling. Secondly give
us an example of your data and what you want to see as a result. We are not
deaf mind readers.

Tyro

"sue" wrote in message
...
I have week1 week2 week3 week4 total
I want a formula and/or function to pick up the last week populated with a
higher amount than null/zero into the total field- Hide quoted text -

- Show quoted text -

EXCEL FUNCTION OR FORMULA
 

=LOOKUP(10^100,A2:D2)

Actually, that formula returns the
last numeric entry in the referenced range.
If that value is a zero...that's what's returned.

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)



"Pete_UK" wrote in message
...
If headings are in row 1 and data starts in A2, put this in E2:

=LOOKUP(10^100,A2:D2)

Will return the last non-zero entry in the range.

Hope this helps.

Pete

On Feb 13, 1:39 am, sue wrote:
week1 week2 week3 week4 total
1500 3000 3000
the total column picks up the last column other than zero



"Tyro" wrote:
Well, first of all, no caps, please. It is considered yelling. Secondly
give
us an example of your data and what you want to see as a result. We are
not
deaf mind readers.

Tyro

"sue" wrote in message
...
I have week1 week2 week3 week4 total
I want a formula and/or function to pick up the last week populated
with a
higher amount than null/zero into the total field- Hide quoted text -

- Show quoted text -

EXCEL FUNCTION OR FORMULA
 

You're welcome, Sue, but have a look at Ron's comments.

Pete

"sue" wrote in message
...
worked like a charm thanks

"Pete_UK" wrote:

If headings are in row 1 and data starts in A2, put this in E2:

=LOOKUP(10^100,A2:D2)

Will return the last non-zero entry in the range.

Hope this helps.

Pete

On Feb 13, 1:39 am, sue wrote:
week1 week2 week3 week4 total
1500 3000 3000
the total column picks up the last column other than zero



"Tyro" wrote:
Well, first of all, no caps, please. It is considered yelling.
Secondly give
us an example of your data and what you want to see as a result. We
are not
deaf mind readers.

Tyro

"sue" wrote in message
...
I have week1 week2 week3 week4 total
I want a formula and/or function to pick up the last week populated
with a
higher amount than null/zero into the total field- Hide quoted
text -

- Show quoted text -

EXCEL FUNCTION OR FORMULA
 

Thanks for pointing this out, Ron. In Sue's examples she shows blank
entries, so I thought it would be a suitable approach.

Pete

"Ron Coderre" wrote in message
...
=LOOKUP(10^100,A2:D2)

Actually, that formula returns the
last numeric entry in the referenced range.
If that value is a zero...that's what's returned.

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)



"Pete_UK" wrote in message
...
If headings are in row 1 and data starts in A2, put this in E2:

=LOOKUP(10^100,A2:D2)

Will return the last non-zero entry in the range.

Hope this helps.

Pete

On Feb 13, 1:39 am, sue wrote:
week1 week2 week3 week4 total
1500 3000 3000
the total column picks up the last column other than zero



"Tyro" wrote:
Well, first of all, no caps, please. It is considered yelling. Secondly
give
us an example of your data and what you want to see as a result. We are
not
deaf mind readers.

Tyro

"sue" wrote in message
...
I have week1 week2 week3 week4 total
I want a formula and/or function to pick up the last week populated
with a
higher amount than null/zero into the total field- Hide quoted text -

- Show quoted text -

EXCEL FUNCTION OR FORMULA
 

Missed out your condition "0" earlier

The 2 array formulas should have read as
(with the condition added in):
=INDEX(B2:E2,MAX((B2:E20)*(B2:E2<"")*(COLUMN(B2: E2)))-1)

=IF(MAX((B2:E20)*(B2:E2<"")*(COLUMN(B2:E2)))=0," ",INDEX(B2:E2,MAX((B2:E20)*(B2:E2<"")*(COLUMN(B2 :E2)))-1))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

EXCEL FUNCTION OR FORMULA
 

This was very helpful to me, but I also need it to look up the one to the
left of that particular column... I am trying to create a formula that will
take the last column with a non-zero entry and subtract from it the entry
from the previous column without specifying names so that it can work over a
52-column spread sheet. I am updating the sheet every week and want the
current charges column to equal the last entry (cumulative charge) minus what
the cumulative charge was the week before. I don't know if that makes sense
or not.

"Pete_UK" wrote:

If headings are in row 1 and data starts in A2, put this in E2:

=LOOKUP(10^100,A2:D2)

Will return the last non-zero entry in the range.

Hope this helps.

Pete

On Feb 13, 1:39 am, sue wrote:
week1 week2 week3 week4 total
1500 3000 3000
the total column picks up the last column other than zero



"Tyro" wrote:
Well, first of all, no caps, please. It is considered yelling. Secondly give
us an example of your data and what you want to see as a result. We are not
deaf mind readers.

Tyro

"sue" wrote in message
...
I have week1 week2 week3 week4 total
I want a formula and/or function to pick up the last week populated with a
higher amount than null/zero into the total field- Hide quoted text -

- Show quoted text -