Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 --- |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
i am in search of Formula / Function in EXCEL-2003 | Excel Discussion (Misc queries) | |||
Need Excel Formula/Function to color cell interior | Excel Worksheet Functions | |||
complex excel formula Array how do I convert it to a vba Function | Excel Worksheet Functions | |||
excel formula/function woes | Excel Worksheet Functions | |||
How do I create a multi formula IF function in Excel? | Excel Worksheet Functions |