MAX in this formula has nothing to do with the max number, the formula works, however if a zero is a text zero (0 aligned left with no alignment chosen) then it will return the zero since all text values are greater than any number. Believe me the formula works -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "James Fullmer" <JamesFullmer@discussions.microsoft.com> wrote in message news:A54F0ECA-BB1F-40AD-A3D2-2C906D25EF25@microsoft.com... >I did use the Ctrl+Shift+Enter, and it at least returned zero, but that is > where it ended. And the last number in the column isn't always the > largest, > so I don't think the max formula will work. > > "Bob Phillips" wrote: > >> Note the bit about an array formula. >> >> -- >> HTH >> >> Bob Phillips >> >> (replace somewhere in email address with gmail if mailing direct) >> >> "James Fullmer" <JamesFullmer@discussions.microsoft.com> wrote in message >> news:F54B8434-76ED-4280-A523-D17DBDE1FA58@microsoft.com... >> > That returned a zero. >> > >> > "Bob Phillips" wrote: >> > >> > > =INDEX(A1:A1000,MAX(IF(A1:A1000<>0,ROW(A1:A1000)))) >> > > >> > > which is an array formula, it should be committed with >> > > Ctrl-Shift-Enter, >> not >> > > just Enter. >> > > >> > > -- >> > > HTH >> > > >> > > Bob Phillips >> > > >> > > (replace somewhere in email address with gmail if mailing direct) >> > > >> > > "James Fullmer" <JamesFullmer@discussions.microsoft.com> wrote in >> message >> > > news:77C2E128-1C80-4D49-AFD8-1E907EB2331E@microsoft.com... >> > > > I have an inventory worksheet that I am having trouble with. The >> total >> > > daily >> > > > inventory column updates daily. At the end of the column, I want a >> formula >> > > > that gives the most recent inventory number. For instance >> > > > >> > > > 1 15,358 >> > > > 2 15,358 >> > > > 3 19,520 >> > > > 4 19,520 >> > > > 5 19,693 >> > > > 6 0 >> > > > 7 0 >> > > > 8 0 >> > > > 9 0 >> > > > 10 0 >> > > > 11 0 >> > > > >> > > > The final field in the inventory column would read 19693. >> > > > There will always be a whole number for inventory, so the zeros >> > > > will >> be >> > > > replace with the number once the daily inventory is completed. >> > > >> > > >> > > >> >> >>

0 |

6/5/2006 10:22:45 PM

Yes, you need to change it in all 3 places in the formula to H3:H33 -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "James Fullmer" <JamesFullmer@discussions.microsoft.com> wrote in message news:E686EF4B-696A-488B-A3FB-D5FA7AE31BB6@microsoft.com... >I would like to believe you both, but my eyes are telling me something >else. > Maybe I am using the formula incorrectly, I need to change the A1:A1000 to > the vector that I use, in this case H3:H33 for all three instances of it > in > the formula correct? > They are not text zero's, but they are the result of a formula, would that > make any difference? > > "Peo Sjoblom" wrote: > >> MAX in this formula has nothing to do with the max number, the formula >> works, however if a zero is a text zero (0 aligned left with no alignment >> chosen) then it will return the zero since all text values are greater >> than >> any number. Believe me the formula works >> >> -- >> >> Regards, >> >> Peo Sjoblom >> >> Excel 95 - Excel 2007 >> Northwest Excel Solutions >> www.nwexcelsolutions.com >> "It is a good thing to follow the first law of holes; >> if you are in one stop digging." Lord Healey >> >> >> "James Fullmer" <JamesFullmer@discussions.microsoft.com> wrote in message >> news:A54F0ECA-BB1F-40AD-A3D2-2C906D25EF25@microsoft.com... >> >I did use the Ctrl+Shift+Enter, and it at least returned zero, but that >> >is >> > where it ended. And the last number in the column isn't always the >> > largest, >> > so I don't think the max formula will work. >> > >> > "Bob Phillips" wrote: >> > >> >> Note the bit about an array formula. >> >> >> >> -- >> >> HTH >> >> >> >> Bob Phillips >> >> >> >> (replace somewhere in email address with gmail if mailing direct) >> >> >> >> "James Fullmer" <JamesFullmer@discussions.microsoft.com> wrote in >> >> message >> >> news:F54B8434-76ED-4280-A523-D17DBDE1FA58@microsoft.com... >> >> > That returned a zero. >> >> > >> >> > "Bob Phillips" wrote: >> >> > >> >> > > =INDEX(A1:A1000,MAX(IF(A1:A1000<>0,ROW(A1:A1000)))) >> >> > > >> >> > > which is an array formula, it should be committed with >> >> > > Ctrl-Shift-Enter, >> >> not >> >> > > just Enter. >> >> > > >> >> > > -- >> >> > > HTH >> >> > > >> >> > > Bob Phillips >> >> > > >> >> > > (replace somewhere in email address with gmail if mailing direct) >> >> > > >> >> > > "James Fullmer" <JamesFullmer@discussions.microsoft.com> wrote in >> >> message >> >> > > news:77C2E128-1C80-4D49-AFD8-1E907EB2331E@microsoft.com... >> >> > > > I have an inventory worksheet that I am having trouble with. >> >> > > > The >> >> total >> >> > > daily >> >> > > > inventory column updates daily. At the end of the column, I want >> >> > > > a >> >> formula >> >> > > > that gives the most recent inventory number. For instance >> >> > > > >> >> > > > 1 15,358 >> >> > > > 2 15,358 >> >> > > > 3 19,520 >> >> > > > 4 19,520 >> >> > > > 5 19,693 >> >> > > > 6 0 >> >> > > > 7 0 >> >> > > > 8 0 >> >> > > > 9 0 >> >> > > > 10 0 >> >> > > > 11 0 >> >> > > > >> >> > > > The final field in the inventory column would read 19693. >> >> > > > There will always be a whole number for inventory, so the zeros >> >> > > > will >> >> be >> >> > > > replace with the number once the daily inventory is completed. >> >> > > >> >> > > >> >> > > >> >> >> >> >> >> >> >> >>

0 |

6/5/2006 10:42:39 PM

I would like to copy data from 1 column in sheet1 starting at row 12 to the first empty column starting at row 8 in sheet2. sub trythis()'SAS untested sc=2 slr=sheets("sheet1").cells(rows.count,sc).end(xlup).row with sheets("sheet2") dlc=.cells(1,columns.count).end(xltoleft).column+1 sheets("sheet1").cells(12,sc).resize(slr) copy .cells(8,dlc) end with end sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Ed Davis" <ed.davis1@verizon.net> wrote in message news:6F9BC9EC-E48E-42CC-84D8-01C007B0C6E8@micr...

hi the value 0.1 in the computer memory is not equal 0.1 so if u add it 10 times the result is diffrent then 1.0: double value=0.1, result; ressult=value+value+value+value+value+value+value+value+value+value; //result<1.0 BUT: result=value*10; //result==1.0 WHY??? another example: float value=0.1, result; ressult=value+value+value+value+value+value+value+value+value+value; //result==1.0 WHY??? can anyone explain it? thx On Thu, 28 Jun 2007 10:18:05 -0700, rsobies <rsobies@discussions.microsoft.com> wrote: >hi > >the value 0.1 in the computer memory is not equal 0.1...

I use Outlook XP for e-mail and also for contacts. I use my "Contacts" folder as my address book. Whenever I send an e-mail, Outlook always asks me to choose which address I want for the contact, and it brings up both the e-mail address and the fax number. So I have to go through an extra step almost every time of choosing which I want. Is there any way to stop it from bringing up the "Fax" number? See http://www.slipstick.com/contacts/nofax.htm Lee Daum wrote: > I use Outlook XP for e-mail and also for contacts. I use my > "Contacts" folder as my add...

Hello: You know how you cannot integrate employees into Employee Maintenance in Integration Manager if the employee's phone number has parentheses and dashes in it? Well, I found a great TechKnowledge article that walks you through how to replace the parentheses and dashes and successfully integrate. But, if you have one employee in your integration file that does not have a phone number, then Integration Manager throws out the employee record altogether and says "Invalid use of Null: 'Replace'". So, I tried the code below. I added some lines to the TechKnowledg...

The last few days I've experience a delay when inputting numbers into Excel 2000 worksheets. When I type in the numbers, they slowly enter themselves, one at a time pausing between each number. This is occurring both with the remote 10-key for the laptop and the number keys on the laptop. This is occuring in all files, no matter how big or small. I wouldn't say I have any overly complicated workbooks, mostly basic functions. I have had much bigger and more complicated before without a problem. When using the 10-key or the number keys anywhere else, Word, internet, e...

I was shown once, but I can't remember how it was done. I have a list of 5000 things and I need to number then 1-5000, but don't want to go through all 5000. I have seen it done where you type the first few like 1-7 and then use a shortcut and drag it down the rest of the list and the numbers will populate. Please help! You can try something like this: =IF(B1="","",COUNTA($B$1:B1) copy down....or if there are no gabs in your list simply double click on the lower right corner of the cell. HTH JG -- pinmaster -------------------------------------------------...

Hello, I needed to create some conditional number format - to say so... My goal: Cell A1 content is "m2" as I Enter in cell A2: "10" -> the output to cell A2 should be "10 m2". Cell A1: "Dollars" -> A2: 200 -> shown "200 Dollars"... and so on.... Is that possible in Excel? (don't know VBA - yet :DD) Any help is appreciated! BR, Daniel How about just use a third cell: =a2 & " " & a1 daniel_of_vienna wrote: > > Hello, > > I needed to create some conditional number format - to say so... > M...

I have 5,000 images I have to change to current name to a part # found on an excel spreadsheet. How do I change the actual name (not one at a time) but globally with these part #'s? On 2/1/10 4:01 PM, Elizabeth wrote: > I have 5,000 images I have to change to current name to a part # found on an > excel spreadsheet. How do I change the actual name (not one at a time) but > globally with these part #'s? This seems like something that you could do with VBA as long as there is a way to identify the new name from the old name (is the old name listed in the Excel ...

What is the formula to convert a whole column of date to text? If I am to do it cell by cell, I need to press F2 then put a --> ' in front of the date. Is there a way I can do it with formula? I have tried CONCATENATE to combine column a which contains ' with date 12/31/03. Column A Column B ' 12/31/03 =CONCATENATE(A1,B1) However, the end result I got is as follow. '37986 instead of '12/31/03 Please help! Hi Caine one way: =TEXT(A1,"mm/dd/yyyy") where A1 stores your date value. Copy this down ...

Hi. I want to implement a function in my spreadsheet, that gives me this: When I enter a number into a cell, another number shows in another cell, based upon the number first entered. A bit more spesific: The idea is to calculate the number of instructors needed for a week end seminar. 1 participant requires 1 instructor. Same for 2 participants, and 3. 4, 5 and 6 participants requires 2 instructors. 7, 8 and 9 participants requires 3 instructors. And so on. For every 3 participant, we apply 1 instructor. So, i.e. if I enter the number 5, i want the number 2 in the cell below. I trie...

How can I show only valid data to date and not future data not yet entered and considered by Excel as zeroes Hi, Future data cells should be empty. If they contain formula then the result for empty data should be NA() rather than "". Charts treat text values, which "" is, as zero. NA() will only stop the data marker being plotted. The line will be interpolated between valid data points. But as your future points will not contain any valid points the line will stop. Cheers Andy Bryan wrote: > How can I show only valid data to date and not future data not yet enter...

I'm using Publisher 2000 and here's something I've never been able to figure out; how do I set up a multiple page document, each page with 2 columns and copy text so that Publisher will automatically flow into the columns but link the columns together for multiple pages? Or is that even possible? Thanks in advance! MV >-----Original Message----- >I'm using Publisher 2000 and here's something I've never been able to >figure out; how do I set up a multiple page document, each page with 2 >columns and copy text so that Publisher will automatically flow int...

In excel how to change numberical data into english words, many times currency figures are required to be stated into english words as well so what is the formula for this problem, for example for US$ 5400 : $ Five thousand four hundred is the conversion into words. See http://www.xldynamic.com/source/xld.xlFAQ0004.html -- HTH RP (remove nothere from the email address if mailing direct) "Mukesh Dhoot" <Mukesh Dhoot @discussions.microsoft.com> wrote in message news:B59710A8-2377-4CF5-98B0-229933A3DE33@microsoft.com... > In excel how to change numberical data into e...

How do i Prefix 0 to all the data in a perticular data. Eg. I have different No. like 1234, 3432, 3453 etc in a column and want to see that it shows up as 01234,03432,03453 If it is just for display just use a custom format like 00000, if you need 5 characters in the cell you can use a help column and then use =TEXT(A2,"00000") copy down, edit>paste special as values in place, delete original column -- Regards, Peo Sjoblom "Manoj Nair" <Nair,Manoj@kuwait.army.mil> wrote in message news:90A3390A-3C10-4967-A639-80C0680C7E45@microsoft.com... > How do i Pre...

I am trying to do a automated line chart that updates as users input over the next 12 weeks... the chart is consisting of 12 weeks on the x axis and % on the y. The problem im having is the values in the table that the chart is picking up from are formulas... but if the formula result is zero or ""(blank) I want the line chart to ignore it... rather than plot zero values making the line drop when a nil value or blank... for rest of the whole 12 weeks...? eg week 1 = 10% week 2 = 11% week 3 = I want the line to stop at week 2...? but because to work out the % is a formula...

I see you can display the SENT column optionally in Outlook 2003. But strangely, the SENT column shows a time newer than the RECEIVED column. Like a message will say SENT 9:59 PM & RECEIVED 9:31 PM. What's up with that? Thanks, BTJustice one of the mail servers or computers has the wrong time. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions...

I have some calulations in my worksheet. They are done twice, if my number is positive or negative. I need to fill the cell next to my number with the calulation that correspondes with the + or - of my number. if A1 + if A1 - A1 B1 C1 D1 -10 7 12 A2 B2 C2 D2 14 4 6 I need the 12 to appear in B1. and the 4 to appear in B2. Thanks In B1 enter =IF(A1<0,D1,C1) Copy down. Gord Dibben M...

I need a formula that sums column C for each "Loc" by "Item"? A B C LOC Item WA Txn Value 1241 R3331 595.20 1241 R3334 595.20 1241 R3334 1,488.00 1242 R1400 908.46 1242 R1400 908.46 1242 R3334 1,488.00 1242 R3334 1,190.40 1243 R1400 908.46 1243 R3334 297.60 -- swestberry Hi, Try this =SUMPRODUCT((A2:A20=1241)*(B2:B20="R3334")*(C2:C20)) You could use cell ref's for the lookup values instead of having them embedded in the formula =SUMPRODUCT((A2:A20=D1)*(B2:B20=D2)*(C2:C20)) -- Mike ...

Hello everyone Every month I work out the max and min for a range of income types over the last 24, 18, 12 and 6 months. As well as displaying the max and min result, is there a way to also select which of the months was the max and which was the min so it can be displayed alongside the value? The month is the column header. You didn't post your formula. Have a look in the help index for INDEX -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Tabbi" <Tabbicat22@gmail.com> wrote in message news:bbc50392-0d13-4020-97df-98c238352b4d@e25g200...

Is there a way to stop Excel from changing the last-modified date of every file that is opened (even when no changes are made and save is never pressed)? This is really screwing us up in the office because we are looking through a ton of old files for one that was last-modified on a certain date. When we open a suspect file it CHANGES THE DATE!!!! Make it stop. Thanks. It is only temporary, if you don't save it it reverst back when you close it. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Jerry Baker" <jerry@nodomain.invalid> w...

Is there any way to display the notes from the column properties in my drawing? ...

I need to create a chart showing the number of students who received each numerical grade on test. The spacing between grades is not equidistant (ex: 65, 70, 90, etc.) and I need to show the true spacing on the x-axis. So I chose a scatter chart because the other charts make the points of equal distance on the x-axis. The trouble is I need to represent the data as columns so if two students scored a 65 and eight scored a 90, the 90 column would much higher. Is there a way to add vertical columns to a scatter chart? Put your grades in a column, and include the grades which had none. ...

Hello, Sample data: 3-764 Neighbourhood Cir 39 Frostbite Lane 3938 Stardust Drive 4 Jones Lane I have a spreadsheet that contains addresses in a column such as the ones above. What I need to do is separate the numbers in the addresses in one of two ways. Either would be ok. The first is (and better for me) would be so that the number portion of the address would have spaces between every number, and then the rest of the addresses in the same cell. For example, "3-764 Neighbourhood Cir", would become "3 - 7 6 4 Neighbourhood Cir". The second would be to split the num...

I have a number field (double) where it would be nice if Access didn't show a leading zero. Decimal Places is set to "Auto", and that is good, except for the leading zero. Format is currently blank. Access 2002. Thanks, croy On Tue, 23 Mar 2010 06:36:01 -0700, croy <croy@invalid.net> wrote: >I have a number field (double) where it would be nice if >Access didn't show a leading zero. Decimal Places is set to >"Auto", and that is good, except for the leading zero. >Format is currently blank. Access 2002. > >Thanks, >cro...

When I look at my Budget, I see an amount in the "Budgeted" column in the "Special :Debt" catagory that I don't recognize. It is a different amount each month. Can anyone tell me where Money gets this amount from? "Jimbob" <Jimbob@discussions.microsoft.com> wrote in message news:C3506374-D004-47E3-B010-61F1EA695A88@microsoft.com... > When I look at my Budget, I see an amount in the "Budgeted" column in the > "Special :Debt" catagory that I don't recognize. It is a different amount > each month. Can anyone tell me wher...