finding last value in rows

Here's what I have. In column B5 I have a list of 35 people and i
columns C5 through AL5 I have weekly values, all with 0 values excep
the first week which has already been entered. I need excel to find th
last value that is not 0 for each of the 35 people.
I tried this formula but it only seems to work with the first week o
values.

=INDEX('Totals sheet'!C5:AL5,COUNTA('Totals sheet'!C5:AL5)>0)

Can anyone help?...thanks

--
Message posted from http://www.ExcelForum.com

0
2/18/2004 5:01:11 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
369 Views

Similar Articles

[PageSpeed] 21

Hi
one way: enter the following array formula (CTRL+SHIFT+ENTER):
=INDEX('Totals sheet'!C5:AL5,0,MAX(('Totals
sheet'!C5:AL5>0)*ISNUMBER('Totals sheet'!C5:AL5)*COLUMN('Totals
sheet'!C5:AL5)))


--
Regards
Frank Kabel
Frankfurt, Germany

> Here's what I have. In column B5 I have a list of 35 people and in
> columns C5 through AL5 I have weekly values, all with 0 values except
> the first week which has already been entered. I need excel to find
> the last value that is not 0 for each of the 35 people.
> I tried this formula but it only seems to work with the first week of
> values.
>
> =INDEX('Totals sheet'!C5:AL5,COUNTA('Totals sheet'!C5:AL5)>0)
>
> Can anyone help?...thanks!
>
>
> ---
> Message posted from http://www.ExcelForum.com/

0
frank.kabel (11126)
2/18/2004 5:27:08 PM
Hi Frank, tried it and getting #REF

--
Message posted from http://www.ExcelForum.com

0
2/18/2004 5:55:01 PM
Pinmaster

Try this formula, entered as an array (ctrl-shift-enter)

=OFFSET($A$1,ROW(A5)-1,MAX((C5:AL5>0)*COLUMN(C5:AL5))-1,1,1
    
I assume you will have this formula in the same row as the data

Offset from this cell: $A$
By this many rows: ROW(A5)-
By this many columns: MAX((C5:AL5>0)*COLUMN(C5:AL5))-
range is this high: 
and this wide: 

Good Luck
Mark Graesse
mark_graesser@yahoo.co

     ----- pinmaster > wrote: ----
    
     Here's what I have. In column B5 I have a list of 35 people and i
     columns C5 through AL5 I have weekly values, all with 0 values excep
     the first week which has already been entered. I need excel to find th
     last value that is not 0 for each of the 35 people
     I tried this formula but it only seems to work with the first week o
     values
    
     =INDEX('Totals sheet'!C5:AL5,COUNTA('Totals sheet'!C5:AL5)>0
    
     Can anyone help?...thanks
    
    
     --
     Message posted from http://www.ExcelForum.com
    
     
0
anonymous (74722)
2/18/2004 6:31:08 PM
Hi Mark, no it's not on the same row and not on the same sheet, m
starting cell is D10 in the "standings" sheet, but I can work with wha
you gave me......thanks again!

Jean-Guy Collette
Canad

--
Message posted from http://www.ExcelForum.com

0
2/18/2004 8:24:22 PM
maybe:

=INDEX('totals sheet'!C5:AL5,MAX(('totals sheet'!C5:AL5>0)
       *ISNUMBER('totals sheet'!C5:AL5)
       *COLUMN('totals sheet'!C5:AL5)-COLUMN('totals sheet'!$C5)+1))

This is an array formula.  Hit ctrl-shift-enter instead of enter.  If you do it
correctly, excel will wrap curly brackets {} around your formula.  (don't type
them yourself.)



Frank Kabel wrote:
> 
> Hi
> one way: enter the following array formula (CTRL+SHIFT+ENTER):
> =INDEX('Totals sheet'!C5:AL5,0,MAX(('Totals
> sheet'!C5:AL5>0)*ISNUMBER('Totals sheet'!C5:AL5)*COLUMN('Totals
> sheet'!C5:AL5)))
> 
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
> 
> > Here's what I have. In column B5 I have a list of 35 people and in
> > columns C5 through AL5 I have weekly values, all with 0 values except
> > the first week which has already been entered. I need excel to find
> > the last value that is not 0 for each of the 35 people.
> > I tried this formula but it only seems to work with the first week of
> > values.
> >
> > =INDEX('Totals sheet'!C5:AL5,COUNTA('Totals sheet'!C5:AL5)>0)
> >
> > Can anyone help?...thanks!
> >
> >
> > ---
> > Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
2/19/2004 2:47:19 AM
Hi Dave.....works great!.....thanks a lot, much apreciated!

Jean-Guy!


---
Message posted from http://www.ExcelForum.com/

0
2/19/2004 12:28:59 PM
Reply:

Similar Artilces:

copy a drop down menu so it appears in each row of a column?
I'm trying to create a spread sheet to enter data into and i've figured out how to make a dropdown list to simplify data entry. But how can i make the drop down list appear in evey row of a column? I'm an excel newbie! thanks for your help! Make your list somewhere and then use Data-Validation-and where it says "any value" choose "list" and Then choose the source of the list. When you have done this for one cell, copy and then paste special and choose validation. Hope this helps "Vanmessa" wrote: > I'm trying to create a spread sheet...

** 1free add-on for RMS - $95 value
NEW WEST TECHNOLOGIES IS LOOKING FOR YOUR INPUT. HOW MANY TIMES HAVE YOU HAD TO SAY, "CURRENTLY RMS DOES NOT OFFER THAT FEATURE"? WE WANT TO HELP OU EMPOWER YOUR RMS EXPERIENCE BY PROVIDING ADD-ONS. LET US KNOW WHAT ADD-ON WOULD BE THE BEST VALUE FOR YOU, AND WE WILL GIVE YOU THE CASHIER AUTO LOG OUT FOR FREE (CASHIER AUTO LOG OUT ) AFTER A SET PERIOD OF INACTIVITY, THE CASHIER IS LOGGED OFF AND THEN RMS LOCKS, AND RETURNS TO THE LOGIN SCREEN. --- ONLY ONE PER PARTNER OR CUSTOMER; DONGLE SERIAL # REQUIRED Is it a bad thing to give out my Serial #? Thank You, Vince >----...

How to arrange mailing labels alphabetically by last name?
Word 2007, arrange mailing labels alphabetically by last name? Use the instructions in http://www.word.mvps.org/FAQs/MailMerge/ConvertAdrsToDatafile.htm to convert the labels to a data source, sort it, and then re-merge the labels. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "mazziossharon" <mazziossharon@discussions.microsoft.com> wrote in message news:D941ABB9-7BC4-49AC-8FF2-CB32F62A2FD9@microsoft.com... > Word 2007, arrange mailing labels alphabetically by last name? > Before you run ...

Switch First and Last Names
What is the excel formula to change John Brown to Brown, John? Thanks! Karen, Try this =MID(A1&" "&A1, FIND(" ",A1)+1,LEN(A1)) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Karen" wrote: > What is the excel formula to change John Brown to Brown, John? Thanks! Hi, try =TRIM(RIGHT(A1,FIND(" ",A1)-1))&" "&TRIM(LEFT(A1,FIND(" ",A1)-1)) "Karen" wrote: > ...

Re; Date Values
Thanks All You've been most helpful ...

Asking Excel Solver to use binaries, but selects other values
I am trying to get a linear program solved using solver. The values that Solver needs to enter are contrained as binary, but excel still try to find values that are neither 0 or 1. Stephane, I assume that when you say it is selecting values that are neither 0 or 1, they are values that are numerically close to 0 or 1 (such as 0.99998). If this is the case, you may want to consider the following approach: 1. Have the Solver constrain your decision variables as binary. 2. Have another set of cells which uses the decision variable to produce a result. When the underlying decision variabl...

OWA "the system cannot find the path specified"
Hi - I looked thru Google, Google Groups and this website, I came up with a KB article that suggested that I reset a metabase setting. I did that, still not working. Here is what I've done, we moved a server from one office to another. New external (routable) IP but the internal scheme stayed the same. I'm getting this message under status of exchange, exadmin, and public in the IIS Manager: "the system cannot find the path specified" I've restarted the services and that didn't help. I'm not seeing anything in the event log that is helpful. I'm not ...

combining duplicate row values?
Excel 2007 XP Pro SP3 I have several rows of data as: 1 gl 24 36 cw1 north 1st 1 g2 24 36 cw2 north 1st 1 gl 24 36 cw1 north 1st 1 g2 24 36 cw2 north 1st 1 gl 24 36 cw1 north 1st that I would like to combine (preferably into a separate worksheet) as: 3 gl 24 36 cw1 north 1st 2 g2 24 36 cw2 north 1st based on rows 1, 3, and 5 and rows 2 and 4 having duplicate data. Is there a way to do this? Dave DDP 1) Use the options in advanced filter to copy unique records to a location in the same worksheet. (Advanced filter does not copy to alternate sheet). 2) Move the records to the required sh...

How to make comments move with cells when cutting pasting, or inserting rows
I have to insert / delete rows or move cells often. When I do this, my "comments" boxes don't move with the cells (they end up all over the place), and they often grow tremendously. Is there some way to make the comment boxes autosize and follow the cells??? Thanks! Debra Dalgleish has some code that does lots of things to comments: http://www.contextures.com/xlcomments03.html Including resizing them: http://www.contextures.com/xlcomments03.html#Resize (and repositioning them to their original spot (just scroll up on that last link).) "anonymous@discussions.micr...

how to write value
Dear Sir/Madam i want to write entire row value while typing value in general sheet. in the general sheet i have three column name, id, and location. and i have three other sheet London, Newyork, Seattle. while i am entering value in general sheet like name id and location, if the location cell value is same as in the other sheet name london or newyork or seatlle, it should write the value in specific sheet if London it should write the value in London sheet Ryan 1811 is it possible in excel help me thanks and regards vincent yes, select all of the relevant sheet...

#value #4
My spreadsheet has hourly entries and Im using SUMPRODUCT for a hourly total. Somehow my users are changing something that is giving me #value in the total cell. There is no entry and I can go through the cells and delete the blank cells and find the offending cell. However, if I just copy the format from a non-offending cell it does not clear the #value. Both the offending and non-ofending cells are fromatted as GENERAL and if I change the format to NUMBER, it does not clear the error. How can I determine what is causing the #value? Thanks in advance "allan" <anonymous@dis...

if a cell has a value greater than 0, then atomaticaly goto a seperate page for addit
Dear Help forum, I'm trying to set up a formula that will automatically move me t another sheet if a value greater than 0 is entered in certin cells. Thank you Fran -- Franks ----------------------------------------------------------------------- Frankso's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3011 View this thread: http://www.excelforum.com/showthread.php?threadid=49797 A formula returns a value to the cell that holds it. It can't move you. But maybe you could use a =hyperlink() formula that would allow you to click the link and go to the ...

Finding data files
I just installed Windows XP but my Money 2001 now doesn't show any of my data. I saved everthing on my hard drive before I installed XP, but I did not back up my Money data. Isn't there a file that contains my current information? What is the name of the file Money saves its information to? Thank You!! See http://umpmfaq.info/faqdb.php?q=109. "Dale" <dalevan@iserv.net> wrote in message news:2a57301c466e7$63e929c0$a501280a@phx.gbl... > I just installed Windows XP but my Money 2001 now doesn't > show any of my data. I saved everthing on my hard d...

Inserting columns into a row
Just wondering if anyone knows how to insert columns into a row. If it's not possible that's fine too..any tips would be helpful Hi Park Copy the cells in the column Right click on the first cell in the destination row and choose Paste Special>Transpose -- Regards Ron de Bruin http://www.rondebruin.nl "park" <park@discussions.microsoft.com> wrote in message news:195421AA-806A-4081-871B-83B99B30A43A@microsoft.com... > Just wondering if anyone knows how to insert columns into a row. If it's not > possible that's fine too..any tips would be helpf...

"parameter values are not vaild" error message when trying to se.
I'm trying to send test emails, and I get a "could not complete operation" box with the error message, "One or more parameter values are not valid." How do I fix this? I would double check your e-mail account settings (e.g. account name, password, POP3/IMAP servername, SMTP server, e-mail address) in Microsoft Outlook to make sure they are filled out correctly. "Sarah" <Sarah@discussions.microsoft.com> wrote in message news:CF4C5632-C855-42E4-AD47-09CF15158E3D@microsoft.com... > I'm trying to send test emails, and I get a "could not c...

How can we refer to "Date last saved" field's values(windows7)
There is "Date last saved" field in windows7 explorer. How can we refer to the value? Is it equal to LastAccessTime? or LastWriteTime? or ChangeTime? Why not try it yourself? Write a program or find a utility that can set all three to different values and see which one pops up as "Dave last saved". The second sounds most likely. "Benjamin" <crazytazo@gmail.com> wrote in message news:2c1790a9-298c-499d-9727-356923338574@m27g2000prl.googlegroups.com... > There is "Date last saved" field in windows7 explorer. > > Ho...

Shading rows
Is it possible to shade every other row on an existing spreadsheet without manually shading? This would make it easier to read a list across the row. Thanks, Bob M format>conditional format>formula =mod(row(),2=0) format as desired and copy where desired -- Don Guillett SalesAid Software donaldb@281.com "Bob M" <bobmnh33NOSPAM@yahoo.com> wrote in message news:OYOdnZG4g5sxWSPfRVn-vA@comcast.com... > Is it possible to shade every other row on an existing spreadsheet without > manually shading? This would make it easier to read a list across the row. > > Th...

Go to certain sheets based on cell value
Hello, I have created a drop down list with the numbers 1,2,3. If the user chooses 1 and presses enter, he/she is taken to 'AOA Only Analysis' sheet, and so on for the other two. Any and all help on this is greatly appreciated. Tushar Tushar, Instead of a drop-down, why not put your three entries in three cells? They can have anything in them, such as "AOA Only Analysis." Now set up hyperlinks (right-click the cell, and select "hyperlink") and set up the hyperlink with "Place in this document." -- Earl Kiosterud mvpearl omitthisword at verizo...

How can I prevent a Chart from showing zero values?
I am creating a chart, and sometimes the data will exist such that there are points that I would like ignored. These points are determined by an If statement, so I can place any string in there when I want them ignored, but I have not been able to find a string that will result in the points not being shown on the chart. Simply put, I am looking for a value that the chart will ignore or another way to accomplish the same ends. Hi, Try using NA() instead of "" Cheers Andy Quinn Ramsey wrote: > I am creating a chart, and sometimes the data will exist such that there are ...

How do I reference last cell having an entry in a column?
Have a problem: trying to create formula for a cell to display the last entry in a column. Example: column has 100 cells, 48 of which have data. The 48th entry in the column was "556". How do I get 556 displayed in a cell by the top of the column (used as a summary)? Hi see: http://www.xldynamic.com/source/xld.LastValue.html -- Regards Frank Kabel Frankfurt, Germany "USNavy Sailor" <USNavy Sailor@discussions.microsoft.com> schrieb im Newsbeitrag news:CBE88CFD-AA3F-487D-8C44-F3F3CE418D5B@microsoft.com... > Have a problem: trying to create formula for a ce...

Runtime changing values in Registry
I am changing the values in the registry runtime... But the dll is not getting invoked even if the values are changed successfully. Vicky... :-) What registry values are you changing? When are you changing them? How are you changing them? what is your expected result? Glenn Adams Tiber Creek Consulting http://www.tibercreek.com glenn@tibercreek.com ---------------------------------------------- Please DO NOT respond to me directly but post all responses here in the newsgroup so that all can share the information. Vicky wrote: > I am changing the values in the registry runtime... B...

Summing the last 200 days
Hi, I was wondering if anyone could help me - I need to add up the 200 most recent days for a spreadsheet and was wondering rather than messing with cut and paste if there was a formula that could do it all for me. I will have more than 200 days worth of info. Also the sheet I am adding up on has gaps between the weeks so its not just a matter of copying the formula over. Thanks! Louja, If you want the last 200 days of data - assuming one data point per day: =SUMIF(A1:A1000, ">=" & LARGE(A1:A1000,200), B1:B1000) Or within the last 200 days: =SUMIF...

Counting rows of blanks across certain columns
I have a survey whose answers were recorded in Excel. The answers for a particular question extend from Q6 to Z505. I need to count the people who did not answer the question (that is, the people, entered in rows 6 to 505 that left columns Q through Z blank). I'm not sure how to do this. Can someone offer a suggestion? Hi You can use a formula like this in Column AA =IF(COUNTA(Q6:Z6)=0,1,"") copy down till AA505 You can use a simple Sum formula now to count the empty ones =SUM(AA6:AA505) -- Regards Ron de Bruin http://www.rondebruin.nl "crossingboston" <...

Get External Data
I have a 2 column table which is likely to change length whenever the Refresh Data menu option is selected. The table links to another software package (SAGE). I don't have formulae in adjacent columns so my problem can not be solved by checking the "Fill Down formulas in columns adjacent to data" box. I do, however, have text in adjacent columns (which the user selects from an in-cell drop down list) which I need to remain aligned with the relevant row. The "insert entire rows for new data ..." option does not appear to do what it says! It only inserts rows in the...

how do automatically change entire row color when cell changes
Hi, is it possible to change the color of the row cell1 is in when cell1 changes to a specified text? See Help under Conditional Formatting -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "IOE" <IOE@discussions.microsoft.com> wrote in message news:91738C3F-3BEB-4EAE-8551-99E60D8E8A21@microsoft.com... > Hi, > is it possible to change the color of the row cell1 is in when cell1 > changes > to a specified text? Hi - select the row (e.g. row 1) - goto 'Format - Conditional Format' - enter the following formula =$A1="T...