Hi Try: =MAX(A1:A10)/MIN(A1:A10) -- Andy. "Stephen" <Stephen@discussions.microsoft.com> wrote in message news:CD6B1F82-B59F-405A-A4F7-D314B81EB887@microsoft.com... > Hi. I'm trying to do a very complicated formula. > I have a list of ten numbers in a row and I have one cell which is telling > me the max of those ten numbers. > This new cell I want to be able to find the number that was returned from > the above cell and then create a formula from that point. I want the > formula > to keep looking back (going down the excel spreadsheet) looking for the > lowest number in a row from that number, and divide the current number by > that number. > > Example: > > From say a1:a10 I have : > 2, 6, 4, 8, 4, 9, 3, 2, 1, 4 > > Cell one returns - 9 > Cell two would look what came before the 9 and find the lowest number in a > row and divide cell one's number by this lower number. So it would find 1 > is > the lowest number in a row and divide 9/1, bringing you to the answer of > 9. > Thanks for you help! > -- > Thanks! > > Stephen

0 |

4/15/2005 1:48:29 PM

Stephen, =MAX(A1:A10)/MIN(OFFSET(A1,MATCH(MAX(A1:A10),A1:A10,FALSE)-1,0,11-MATCH(MAX( A1:A10),A1:A10,FALSE))) HTH, Bernie MS Excel MVP "Stephen" <Stephen@discussions.microsoft.com> wrote in message news:CD6B1F82-B59F-405A-A4F7-D314B81EB887@microsoft.com... > Hi. I'm trying to do a very complicated formula. > I have a list of ten numbers in a row and I have one cell which is telling > me the max of those ten numbers. > This new cell I want to be able to find the number that was returned from > the above cell and then create a formula from that point. I want the formula > to keep looking back (going down the excel spreadsheet) looking for the > lowest number in a row from that number, and divide the current number by > that number. > > Example: > > From say a1:a10 I have : > 2, 6, 4, 8, 4, 9, 3, 2, 1, 4 > > Cell one returns - 9 > Cell two would look what came before the 9 and find the lowest number in a > row and divide cell one's number by this lower number. So it would find 1 is > the lowest number in a row and divide 9/1, bringing you to the answer of 9. > Thanks for you help! > -- > Thanks! > > Stephen

0 |

4/15/2005 1:53:57 PM

Stephen, I used my formula, and it worked, at least in the way that I thought you wanted, dividing the max by the lowest number below - in a higher row number, below the mxaimum on the screen, as your example showed. The only thing that I can think of is that we are at cross-terms on our usage of lower, and that you mixed up your example. I can send you a working example, that does it both ways, if you would like. To contact me privately take out the spaces and change the dot to . HTH, Bernie MS Excel MVP "Stephen" <Stephen@discussions.microsoft.com> wrote in message news:40717892-0481-4C29-A2AB-79725F6F184F@microsoft.com... > Hi. > I used your below formula and it ends up dividing by the lowest # in the > set. Not the lowest # in a row (below the highest) in the set. > Do you know how I could fix this? > > Thanks! > > "Bernie Deitrick" wrote: > > > Stephen, > > > > =MAX(A1:A10)/MIN(OFFSET(A1,MATCH(MAX(A1:A10),A1:A10,FALSE)-1,0,11-MATCH(MAX( > > A1:A10),A1:A10,FALSE))) > > > > HTH, > > Bernie > > MS Excel MVP > > > > > > "Stephen" <Stephen@discussions.microsoft.com> wrote in message > > news:CD6B1F82-B59F-405A-A4F7-D314B81EB887@microsoft.com... > > > Hi. I'm trying to do a very complicated formula. > > > I have a list of ten numbers in a row and I have one cell which is telling > > > me the max of those ten numbers. > > > This new cell I want to be able to find the number that was returned from > > > the above cell and then create a formula from that point. I want the > > formula > > > to keep looking back (going down the excel spreadsheet) looking for the > > > lowest number in a row from that number, and divide the current number by > > > that number. > > > > > > Example: > > > > > > From say a1:a10 I have : > > > 2, 6, 4, 8, 4, 9, 3, 2, 1, 4 > > > > > > Cell one returns - 9 > > > Cell two would look what came before the 9 and find the lowest number in a > > > row and divide cell one's number by this lower number. So it would find 1 > > is > > > the lowest number in a row and divide 9/1, bringing you to the answer of > > 9. > > > Thanks for you help! > > > -- > > > Thanks! > > > > > > Stephen > > > > > >

0 |

4/15/2005 5:11:14 PM

I have a worksheet that has formulas built within 6 columns. I want to drag the formula in each column down 100 rows, but as I am calculating a date that references only 1 cell (A1) when I drag the formula, it picks up A2, A3, A4 etc. as the formula progresses down the column. Is there a way to lock the cell reference to A1 so that when I drag the formula down the column, that cell reference doesn't change? Add a $ sign in front of the cell reference. Example $A1 Locks the column but allows the rows to change A$1 locks the row but allows the column to change $A$1 loc...

I have a complicated (for me, anyway) problem that I have solved in a way that I'm not happy with. If anybody can offer suggestions for improvement to my methods, I'd very much appreciate it. The problem is that I've ended up with a set of formulas which fall apart if I insert a row. This is because I'm using the offset() function, where I suspect I should be using something else. The goal is to have 2 worksheets, named D (for data) and R (for report). The data is organized such that each column is a distinct entity. That is, the data points are rows and there can be hund...

I have a question for you. I just received a new computer with Office 2007 and I cannot figure out how to do a screen print of a single screen with a dual monitor. In the prior version I would use Ctrl Alt Prt Sc - but it does not work with this new version. Do you by chance know how to do this? Have you tried the Snipping tool in Accessories? This is only available in Vista and Windows 7. -- Mary Sauer MVP http://msauer.mvps.org/ "Ron D" <Ron D@discussions.microsoft.com> wrote in message news:A29E99CD-59F8-46B6-BAEC-F42EB72F19FD@microsoft.com... >I h...

I have written a macro that allows a user to select a specific series on an embedded chart, then specify a change to the series limit ranges. It works very well, EXCEPT I have not figured out how to write a statement that will return the index number of the selected series. I mistakenly believed that the series index number is the last number on the right end of the series formula. It's not. That's the plotting order sequence number, and it can change. I need the series index number, which I think is unique within the workbook. I tried using SELECTION.NAME after selecting the se...

When defining a custom entity it is currently not possible to define 2 separate relationships from this entity to another entity. For example, consider a custom "Project" Entity - it is not possible to create two relationships to Contact - one for a "Lead Engineer" and another for "Inspector". To do this, you would currently need to set up a many-to-many relationship using a 3rd entity and this makes things very difficult for the end user data entry, search and for reporting purposes. ---------------- This post is a suggestion for Microsoft, and Microsoft ...

Can anyone please help? The code: Sub test() Dim x As Range Worksheets("sheet1").Activate Set x = Cells(Rows.Count, "N").End(xlUp) MsgBox x.Address End Sub I would like to find the last cell in column N and instead of the result being displayed in A msgbox, I would like to use the cell address in a formula after the code above e.g.: range("b2").formula = _ "sumproduct((N8:LASTCELLADDRESS>=0)*(N8:LASTCELLADDRESS<=1000)) Hi, Try this Dim LASTCELLADDRESS As Long LASTCELLADDRESS = Cells(Cells.Rows.Count, "...

I seem to have trouble w/ a particular formula. I am attempting to round a number to the nearest tenth. No big deal...simple stuff if it were to stop there. I have rounded the number which returns as a decimal. I don't have trouble with any other number except when dealing with a zero in the 10ths spot. When I add text to the formula the decimal is not shown. Is there a way to have Excel first determine whether I have a zero in the tenths column and if so, have it show the decimal? Here's an example of the formula... FORMULA RESULT ...

hi... earlier tonight i was using outlook express 6 to check my mail.. when i went to write mail and send one my user name and password came up on the screen. i have been using this for months and never had a problem.. the username and password came up and i pressed ok.. and it keeps popping up.. but it is only for outgoing mail..now i think i did something to the password... how can i change it or whatever? thanks Rich nevermind i fixed it...... >-----Original Message----- >hi... earlier tonight i was using outlook express 6 to >check my mail.. when i went to write mail an...

I need to find a formula that's on the sheet I'm working on - I don't know what it is or where it is and it's causing problems. How can I display all formulas on the sheet I'm working on? -- Lynne Mawson Manchester Touching CNTRL ~ will toggle the display to shows formulas as opposed to values in the cells. -- Gary's Student "Lynne Mawson" wrote: > I need to find a formula that's on the sheet I'm working on - I don't know > what it is or where it is and it's causing problems. How can I display all > formulas on the sheet I...

Has anynone encounter this problem in MS Excel in Office XP?? I use a spreadsheet that basically adds up numbers and gives me a tota on the bottom. Today it stopped doing that. I change numbers and th total stays unchanged unless I highlight the total's cell, put th cursor in the formula bar on top at the end of the formula and hi ENTER. It won't do it on its own anymore. This happens in ever spreadsheet I use so it's not just that one file. Can someone help? Thanks. Filipesk -- Message posted from http://www.ExcelForum.com Tools>Options>Calculation, check "Aut...

In a sheet named "Confirmed" I have a spreadsheet which contains the following:- Column F (a list of languages) Column G (a list of languages) Column H (a fee per 1000 words) Column I (a fee per 1000 words) Now, I have set up a data validation series in a new sheet, so that when a language is selected, say for example, Arabic which is in F1 of the sheet "Confirmed", the languages listed next to it in G1 appears. If Arabic appears in F1, F2 and F3, then the languages which are listed in G1,G2 and G3 appear. Now what I want to do, is for in a new sheet in cell A1, I want t...

Hello My worksheet is full of thousands of firstnames, middle names surnames, email addresses etc etc. How do I create a formula that wil result in having each persons *first name, initial of middle name surname and email address * ? I would really appreciate some help. Thank you. Ka -- Kat Hughe ----------------------------------------------------------------------- Kat Hughes's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1674 View this thread: http://www.excelforum.com/showthread.php?threadid=31952 You didn't bother to tell us your setup so try thi...

In GP7.5, is there any way to change inventory item types once they have been used? Can the item be deleted and then recreated at the end of a year? Are there any utility programs that can do this? In particular, I've got some "sales inventory" items that I want to convert to "kits" and others that need to be "services" We are on 8.0, but I don't think it is possible to change the item type - not even with Professional System Tools. We have changed the item name (via tools) to include the word Discontinued. Then we set up a new item to repl...

Hi, I was just wondering how i would tell Publisher that i want an A3 publication but i want it to print on 2 A4's. currently, instead of using two A4s which would be perfect size, its using 4 A4's and only printing in the middle orners of the pages. how would i change that so it only used 2 A4s Thanks. Elliot wrote: > I was just wondering how i would tell Publisher that i want an A3 > publication but i want it to print on 2 A4's. > > currently, instead of using two A4s which would be perfect size, its using 4 > A4's and only printing in the middle o...

Hi all, I have a MFC dll, in this dll I have a function. This is function's prototype: int GetCellAt(double left, double top, LPDISPATCH* lpCell). In this function I asign a valid pointer to lpCell. In the programe that uses this dll I call this function but after calling this function lpCell is always NULL. When I change the function declaration like this : LPDISPATCH GetCellAt(double,double) and in this function I return the pointer that I asigned to lpCell. After calling this function I get an valid pointer. Please tell me why? In your first function, you have declared a &q...

Can someone please explain me what is correct formula i excel.....I need to markup or increase A1 for 30% A1=16101.35 =A1*1.3 and that is 20931.75 (B1) when I go to check =1-(A1/B1) i get 23% -------------------------------------------------------- A1=16101.35 =A1/0.7 and that is 23001.93 (B1) when I go to check =1-(A1/B1) i get 30% So what is correct? Hi dmmatic, 16101.35*1.3=20931.75 and 16101.35/0.7=23001.93 so the problem is with your checking. If you use a formula like =(B1-A1)/A1, that will give you 0.3 (or 30%) for the first calculation, and 0.42857... (or 42.857%) for the secon...

I have a large sheet, so I need some automated method to subtotal it. There are a few different GFS CODES in one column and many different BRANCH CODES in another. At the bottom of the sheet is a table of various COUNTIF calculations on the GFS CODES. What I do right now is autofilter the BRANCH CODES and load each branch individually. I then update the named range to only the GFS CODES in that particular branch, and finally print out the page with the updated COUNTIF calculations. The SUBTOTAL tool can do one COUNTIF calculation, but is there a way I can have a "table of GFS CODE COUNTI...

I'm a MSP new user and I want a detailed explanation to the following questions: What is MS Project 'Level 1' schedule? What is MS Project 'Level 2' schedule? What is MS Project 'Level 3' schedule? and What is MS Project 'Level 4' schedule? AFAIK, I never heard of such nomenclature in the context of the product as provided by Microsoft. Are these words used on your project? Where did you see these words written, or who uses them? --rms www.rmschneider.com On 03/01/10 07:30, Alfred wrote: > I'm a MSP new user and I want ...

This is a multi-part message in MIME format. ------=_NextPart_000_000C_01C5B13F.18EB58A0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi all Looking for a formula to locate a score from a results table place it = next to the fixture list. (backward sounding I know but from fixture list I can ascertain current = form, but I cant from a table) Created a fixture list I.e. Arsenal vs. Aston Villa is the first = fixture. and I want to pull the result for each team from the table of = results.=20 Results Table Sheet a bc...

I just purchased office:mac 2004 and was wondering what the seal is supposed to look like. Mine looks like someone left off a small piece of the seal in the middle with a silver line through it and red writing on the silver line. Does that make any since? The guy at Staples said it is supposed to look like that. Help.....thanks! On 17/10/07 11:14 AM, in article 1192583675.602242.208900@z24g2000prh.googlegroups.com, "sstewart@mcleodusa.net" <sstewart@mcleodusa.net> wrote: > I just purchased office:mac 2004 and was wondering what the seal is > supposed to look like. ...

Hi all I need a formula that will link to the same cell in different worksheets. for example a1 will be c2 in worksheet 1. b2 will be c2 in worksheet 2 etc....... thanks for you help in advance. Hi try ='sheet2'!C2 -- Regards Frank Kabel Frankfurt, Germany "Tom" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:3aa701c4ab13$a8064cd0$a301280a@phx.gbl... > Hi all > > I need a formula that will link to the same cell in > different worksheets. for example a1 will be c2 in > worksheet 1. b2 will be c2 in worksheet 2 etc....... > >...

I am trying to give a user rights to send on behalf of the email folder. I went to Exchange Admin properties of the folder and under permission "directory rights" and "administrative rights" I've added the user and given all rights including "send as Replicated all CD and login again but still getting an error "you do not have permission to send on behalf any help will be greatly appreciated Thank you L ...

Is there a size limit for pst files? Depends. In versions of Outlook prior to 2003 the limit was 2GB. Outlook 2003 can create Unicode PST files, however, which can grow quite a lot larger if necessary. -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP http://home.hawaii.rr.com/schorr **I apologize but I am unable to respond to direct requests for assistance. Please post questions and replies here in the newsgroup. Mahalo! "John" <anonymous@discussions.microsoft.com> wrote in message news:043201c3a95e$c363e670$a301280a@phx.gbl... > Is there a size limit for pst files? ...

Hi there! Using E02 on XP. Have zillions of formulas to create and if I can get my template going, it will be much easier. Only problem is this: I used Replace to change all my ='1'!'s with '=1'1! so I can see my formulas and use copy/replace to create my boiler templates on multiple worksheets. I have done this in the past and then just replace in reverse and voila! I have formulas linked to the correct worksheet locations. This time I keep getting the Excel can't find anything to replace, check your formating, etc. What am I doing wrong? I really do not want...

I initially made an ASP.NET page and after I added AJAX code by entering the page is the initial declaration <% @ Register Assembly = "System.Web.Extensions, Version = 1.0.61025.0, Culture = neutral, PublicKeyToken = 31bf3856ad364e35 " Namespace = "System.Web.UI" TagPrefix = "asp"%> then I have write the ScriptManager in this page. In the web.config page i have write the tag <add assembly = "System.Web.Extensions, Version = 1.0.61025.0, Culture = neutral, PublicKeyToken = 31bf3856ad364e35 "/></ assemblies>. Now w...