This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C5D328.5674E0A0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I am using the following array formula to try and determine the minimum = value of a range of values verifying a certain criteria : {=3DMIN(('D=E9tail = OF'!$F$2:$F$2040=3DOverview!$A3&Overview!$B3)*('D=E9tail = OF'!$J$2:$J$2040))} This however only returns 0 as results while I have no 0 values in the = range J2:J2040???? Using the exact same formula to determine the maximum va...

i get the above error message when i try to change a source link. what does it mean and how do i correct it? thanks A formula can be 1024 characters long (when measured in R1C1 reference style). Try moving your workbook to a folder that has an overall smaller length: c:\my documents\excel\for work\January\2005\important_do_not_delay to C:\a lbaskin wrote: > > i get the above error message when i try to change a source link. what does > it mean and how do i correct it? > thanks -- Dave Peterson ...

How do I copy cells with relative references into other cells kepping the same references? e.g. cell A3 has the formula A1+A2. A6 has the formula A4+A5. I want to copy A3 and A4 into A15 and A16 in order to have A15 with the formula A1+A2 and A16 with the same formula as A6 (A4+A5). Hi Change the formula to =$A$1+$A$2 Look also in the Excel help -- Regards Ron de Bruin http://www.rondebruin.nl "avjunior" <avjunior@discussions.microsoft.com> wrote in message news:38ACCFCC-04D7-4921-B45C-B49BAB29D474@microsoft.com... > How do I copy cells with relative references i...

Hi I want to make a list of international phone numbers starting with the + sign. I can not work out how to stop the cell being treated as a formula, i have the cell formated as text. thanks tim You can put a single quote mark in front - eg '+613xxxxxx Rgds, ScottO "Tim" <adslk4at@tpg.com.au> wrote in message news:42d5f72b@dnews.tpgi.com.au... | Hi I want to make a list of international phone numbers starting with the + | sign. | | I can not work out how to stop the cell being treated as a formula, i have | the cell formated as text. | | thanks tim | | Another w...

If an "average" formula includes multiple columns, and those columns change each month by hiding old months (at end) and adding new months (at beginning) what can be done, by macro or in formula) to ignore the hidden columns? Try this if you can use 101 in your Excel version =SUBTOTAL(101,A1:C1) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "DotK" <DotK@discussions.microsoft.com> wrote in message news:D6F36A22-BDE4-4836-98AE-028A616A9300@microsoft.com... > If an "average" formula includes multiple columns, and thos...

Hi, I am trying to replace ,B with ,C in the following formula and get an incorrect result. =SUMPRODUCT(BB$2:BB$377,BL$2:BL$377)/(SUMPRODUCT(BB$2:BB$377,BO$2:BO$377)+SUMPRODUCT(BB$2:BB$377,BG$2:BG$377)+SUMPRODUCT(BB$2:BB$377,BH$2:BH$377)+SUMPRODUCT(BB$2:BB$377,BI$2:BI$377)+SUMPRODUCT(BB$2:BB$377,BJ$2:BJ$377)+SUMPRODUCT(BB$2:BB$377,BK$2:BK$377)+SUMPRODUCT(BB$2:BB$377,BL$2:BL$377)+SUMPRODUCT(BB$2:BB$377,BM$2:BM$377)+SUMPRODUCT(BB$2:BB$377,BN$2:BN$377)) When I do a find and replace of ,B with ,C Excel replaces the :B with :C instead of replacing ,B with ,C . See the result below after the fin...

Hi, I am trying to find the value of AF at the 25% percentile of AW in the following array formula. =IF(PERCENTILE(Exit_Date!$AW$184:$AW$228,0.25),Exit_Date!$AF$184:$AF$228) I am getting a result but not the answer I am expecting. Any ideas? Bruce You might try =INDEX(AF$184:$AF$228,MATCH(PERCENTILE($AW$184:$AW$228,0.25),AW$184:$AW$228)) if your data values in AW are in ascending order, but in that situation you could also use =INDEX(AF184:AF228,1+0.25*ROWS(AW184:AW228)) -- David Biddulph "Bruce" <Bruce@discussions.microsoft.com> wrote in message ...

I have names in col A that go from row 1 to row 1000 I then place data(numbers) in col B that corresponds with the name in col A. Once done I show ONLY the rows that have new data(filter)and use the SPEAK function to read the data back to me. Here is the tough part. On subsequent weeks I enter data in col B again and I want to be able to apply the SPEAK function BUT ONLY to the LAST set of inputted data. aDoes anyone have a good way to implement htis Thanks Hi, It sounds like you need to add a date column to your data so you can filter on that. -- If this helps, please click the Yes...

Hi, How can I remave the gap/space after hide a field? I am using the codes to hide field crmForm.all.fieldname_c.style.display = 'none'; crmForm.all.fieldname_d.style.display = 'none'; thanks -- MS CRM consultants at Melbourne Visit my CRM blog at http://melbournecrm.spaces.live.com/ ...

I select columns and try to hide them. Excel says it cannot shift objects off the sheet. I try goto objects and it says there are no objects on the sheet. I cannot hide or resize the column widths of certain columns. I cannot figure out why I can't get it to work and hide or resize the columns. Does anyone have any idea how to fix the columns so I can hide/resize the columns? It only does it on some of the columns, not all of them. I can't find any objects to delete. ...

Example : E/no salary Bonus total 001 1000 #N/A 002 200 100 003 2000 #N/A How to set a formula for each worker in total column (Salary & Bonus)when the value are set as #N/A. How to convert #N/A to 0 for sum formula. Please advise. Thanks. D2=IF(C2<>"#N/A",C2+B2,B2) Assuming the Bonus values are in Column C and Salary amounts are in Column B. Copy/drag formula down the column D. Corey... "lee-sc" <leesc@discussions.microsoft.com> wrote in message news:1ADC71CA-4C31-4C50-A201-4...

If I have a table with 'zero' values and don't want them to appear in my graphic, but do want the rest of the values to show, how do I hide them? Hiding them on the table doesn't work, they still show up in the graphic... Replace the zeros by =NA() or #N/A -- David Biddulph "FLF" <FLF@discussions.microsoft.com> wrote in message news:BE6BC77B-9DBB-4BEB-A361-F5C1C0D2074D@microsoft.com... > If I have a table with 'zero' values and don't want them to appear in my > graphic, but do want the rest of the values to show, how do I hide them? > H...

hi Im new to excel I seem to lose my formula's everytime I go back to enter some data that I may enter wrong for example I have put the formula in and go back and put a number in the cell and find it's the wrong # so I backspace or hit delete and I lose my formula and clear the cell what am I doing wrong??:) -- genevieveg ------------------------------------------------------------------------ genevieveg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30333 View this thread: http://www.excelforum.com/showthread.php?threadid=499995 A cell can contain ...

F4 i have 0 H4 i have 6 J4 i have 1.12 The answer in N4 is 6 (good enough but would like to round to nearest No which should be 7) Formula used is =H4-F4*J4 F7 i have 0 H7 i have 6 J7 i have 2.24 The answer in N7 is still 6 ( it should be 12, double the one above) Formula used is =H7-F7*J7 just can`t work this out. help please thanks Scudo wrote: > F4 i have 0 > H4 i have 6 > J4 i have 1.12 > The answer in N4 is 6 (good enough but would like to round to nearest No > which should be 7) > Formula used is =H4-F4*J4 > > F7 i have 0 > H7 i have 6 ...

Is there any way to use the value shown in a drop down box in a sum formula. I am trying to work out the total of a column including whatever number is shown in the drop down box. The formul at the moment is =SUM(C16:C28) with the drop down box positioned on cell C28. Is there any way of linking the drop down box to the cell so the number shown is added as well? Thx --------- www.coffeecozy.com Use your Bodum and give up cold coffee for good! Hi what drop down boy are you suing ('Data - validation')? -- Regards Frank Kabel Frankfurt, Germany "44203 - ExcelForums.com" <...

Hi guys, Firstly my thanks go out to those who sort out the problems of us minions. I've learnt so much from these posts that I never thought possible - keep up the good work guys... I have a pretty good knowledge of Excel - years of experience with different releases, but have never come across this situation before. I have a a John Walkenbach termed 'megaformula' that contains ISERR, IF, SEARCH, AND, OR, ISNA, VLOOKUP, LEFT & MID functions. Having got it to work I opted to use copy and paste to automate it's use in a report, rather than mess around with converting...

Hi Can someone explain when and why you would use an array formula. Just trying to get my head around Thanks A Hi Alex Check this link from CPearson, http://www.cpearson.com/excel/ArrayFormulas.aspx Do a search in Google for "Array Formula in Excel" you'll find all the information you need. HTH John "Alex Hammerstein" <aph@misnet.co.uk> wrote in message news:C698A2ED.EC2B%aph@misnet.co.uk... > Hi > > Can someone explain when and why you would use an array formula. Just > trying to get my head around > > Thanks > > A > You can...

Is there a formula that can return the last non-zero number in a column of numbers? I have a column of numbers that I add to daily, and I need to know the value of the last number in that column for use in another formula. Thanks! -- Bill The following formula will return the last non-zero number in cells A1:A20 -- =INDEX(A:A,MAX((A1:A20<>0)*ROW(INDIRECT("1:20")))) It's an array formula -- type the formula in the cell, then hold the Ctrl + Shift keys, and press the Enter key. Bill H. wrote: > Is there a formula that can return the last non-zero number in ...

Hi all, I am trying to chart a series of data points from a table which looks up values dependant on the contents of a seperate cell with a drop down list. The table / chart is designed to be used until the end of the year, so obviously values after today show as blank, (there are formulas already in the table which will allow the graph to populate over time). Is there a way of not charting cells with a blank value in Excel 2007 to prevent the graph displaying these data points as zero? Thanks in advance Hi, You need to output #N/A , using the NA() formula, instead of zero or text...

I have a spreadsheet with several columns of numbers. These numbers are totaled in one column. I would like the column next to it to show the amount less than 6 and the column next to that to show the amount greater than 6. How do I format the cell to do that? -- dcchica ------------------------------------------------------------------------ dcchica's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30601 View this thread: http://www.excelforum.com/showthread.php?threadid=502532 In the first column type the formula:- =If(C1<6,C1,0) and in the next col...

NOTE: To clarify what I have described below I have attached screenshot. I am trying to fill a table (18 rows by 50 columns) on a seperate shee (called 'Data') from another sheet (called 'Sheet1') containing column of 900 cells of data. The thing is each of the 900 cells is actually 4 merged rows (so ther are actually 3600 rows). So the cells containing the data are labele as $J7, $J11, $J15, $J19...... etc. The table must be filled sequentially (i.e. from left to right the onto the next line) from the column of 900 cells containing the data. How can I do this ? Is i...

Hi, I am trying to develop an anesthesia record chart for the vet clinic I work at. I have A8 through A15 labeled for time, temperature, heart rate, etc. The other columns (B8-B15, C8-C15, etc) I have set aside for later entries in the above categories as we take values every 5 min. or so. What I would like to do is have a formula that would show numerical values in red if they are above or below the normal value range (in black). For instance, a dog's normal temperature can range from 99.5 degrees F to 102.5 degrees F. If it is 98 degrees or 103 degrees I want these values to s...

Hi: I have a collumn of #s such as 80 x 120 and 65 x 100, etc. I need the actual product of them. I have replaced the "x" with "*" but cannot get the "=" inserted. Can you suggest somethiong? Select the cells you want to convert and run this small macro: Sub formulator() For Each r In Selection r.Formula = "=" & Replace(r.Value, "x", "*") Next End Sub Macros are very easy to install and use: 1. ALT-F11 brings up the VBE window 2. ALT-I ALT-M opens a fresh module 3. paste the stuff in and close the...

I am trying to create a formula in a spreadsheet that will calculate the numeric value of "X" in the following formula; X*4+55-X*.2=55*.79 Is this possible? =55*(0.79-1)/(4-0.2) ???? -- HTH Bob Phillips "paperguy" <paperguy@discussions.microsoft.com> wrote in message news:2A34DC95-B80B-4DF2-9E94-D01D18027B67@microsoft.com... > I am trying to create a formula in a spreadsheet that will calculate the > numeric value of "X" in the following formula; > X*4+55-X*.2=55*.79 > > Is this possible? Well, you could start out by doing some bas...

Hi - Thanks beforehand! I'm stuck on a formula for a condition and hoping someone can help. =AND($H23="stalled",$G23<>TODAY()) Is currently what I have and it works... I need to add an additional part to this formula that looks for if $G23 is blank and/or if it's past today(). So if it's "stalled" has no date or the date has not occurred then true, else if "stalled" and has date that has occurred then false. Try: =AND($H23="stalled",OR($G23="",$G23>TODAY())) Regards, Fred "Awrex" <Aw...