EXCEL FUNCTION OR FORMULA

[sue]

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

 

[Tyro]

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]

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

 

[Ron Coderre]

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)

 

[Max]

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))

 

[sue]

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.

 

[Pete_UK]

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

=LOOKUP(10^100,A22)

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

Hope this helps.

Pete

 

[sue]

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

 

[sue]

worked like a charm thanks

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

=LOOKUP(10^100,A22)

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

Hope this helps.

Pete

 

[Ron Coderre]

Re: =LOOKUP(10^100,A22)

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)



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

=LOOKUP(10^100,A22)

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

Hope this helps.

Pete

 

[Pete_UK]

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

Pete

 

[Pete_UK]

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

Pete

 

[Max]

Missed out your condition ">0" earlier

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

=IF(MAX((B2:E2>0)*(B2:E2<>"")*(COLUMN(B2:E2)))=0,"",INDEX(B2:E2,MAX((B2:E2>0)*(B2:E2<>"")*(COLUMN(B2:E2)))-1))

---

 

[emily @ UU]

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.