Does anyone know of a book which lists and explains all the functions/formulas?

Say I have the following dynamic named ranges (12 per year, but here are three): TERR_2004 CAT_2004 MONTH_2004 TERR_2003 CAT_2003 MONTH_2003 and so on and so on back to 1999 Lets say in cell A2 is Data validation list with the years 1999-2004 Is it possible to put a formula in cell B2 that would be able to reference the appropriate dynamic range based on the year value in A2? I did a google search on the indirect() function, but I am unfamiliar with that function and was not able to get any of the examples to work. I am hoping this is possible as the only other options I see is trying to...

I need help creating a a formula to accrue vacation time and subtract when used. For example I need each month to be .83, but have a total of all the time for each month so I then can subtract what has been used. ....put the number of the month you are in in cell A1 (Jan=1, Feb=2, etc.). In cell A2, type "=A1*.83". In cell A3, across to cell L3, type month abreviations (Jan, Feb, etc.) In cells A4 through L4, type the time taken throughout the year. In cell B2 type "=sum(A4:L4)" In cell C2 type "=A2-B2" this will be time remaining at the current month t...

Hi all i'm building upload proccess from excel to access, i need to take some amount from the access and enter it into a formula inside of the excel for example min(amount-5,60) during the upload access programming will exchange the amount text in real amount and will upload the result is it posible to do the folowing: 1.exchange the amount text in the amount from access2 2.to calculate the formula in excel and the sum will be uploaded to the excel 3.still to leave the formula as is in the excel without revising(all changes will be made by the programming and values in access ...

Hello - I need to average a range of cells B2:Y31. Normally, I'd use the formula =AVERAGE(B2:Y31)... easy enough. The twist is that I need to average that range of cells, BUT any cell that has a value less than 1 or more than 900 cannot be used in the calculation. These values are considered invalid for my purposes. All cell values are referencing a cell value in another worksheet that is part of the spreadsheet; for instance =bam!B2 How can I use a formula to automatically calculate the average of the range without calculating the invalid values? TIA! --- Phil =AVERAGE(IF((...

When I try to save a spreadsheet, I only have the option to save it as a Workspace. The "Save" and "Save As" options aren't in the "File" menu at all. Can anyone help? Hi does this happen with all files or only with a specific one? -- Regards Frank Kabel Frankfurt, Germany EMSchon wrote: > When I try to save a spreadsheet, I only have the option to save it > as a Workspace. The "Save" and "Save As" options aren't in the > "File" menu at all. Can anyone help? It happened to all of them and is now resolv...

I've tried messing aroung with a couple of formulas in an attempt to sort this out, however nothing seems to be resulting in what I'd like to accomplish. I've done up an example of what I'd like to do... I would like to be able to have a persons name entered into column A if the number in column B matches the number that has been assigned to that person. I would like for the formula to be the same for each entry so that I can just copy it from cell to cell. What I have in mind is something like "If 25678 = Mark Jones, then enter Mark Jones in column A" The numb...

Ok gurus, Here's another one. I want to pull a value out of a certain column based on the row that the formula is in. For example: -The desired value is in A column (number value) -The row() function will return the row that the formula is in -Lets say row returns 13 -I would like to retreive the text value in A13 dynamically using row() within the formula.....something like ="S_"&"5133"&"_"&TEXT(C3,"dd") -The "5133" would be the text value of the number from A13 as determined by row Sorry if these are noob ques...

formula can be named. i click <ctrl+function key F3> give a name "yearbefore" and in the reference type<=year(today())-1> and in any cell i hit function F3 and select <yearbefore> enter twice I get 2002. But if I give the name as "lastyear" it does not work . it gives <#name?>. what mistake am I doing? if the formula contains a cell address it works but it seems to take the absolute addresses. e.g.name <lookup> and reference is <vlookup(a5,data,2,false)> it takes $A$5. any comments Excel 2000 thanks and regards AutoCorrect is cha...

KellyMcG03, Below is what I wrote and I am missing a () I think. =IF(j2>8500,"",(IF(F2<8500, (H2-J2),"",(IF(F2>8500,(8500-J2,"") What have I done wrong? Thanks, C -- cefp ----------------------------------------------------------------------- cefpe's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1357 View this thread: http://www.excelforum.com/showthread.php?threadid=26722 hi, =IF(J2>8500,"",IF(F2<8500,H2-J2,IF(F2>8500,8500-J2,0))) >-----Original Message----- > >KellyMcG03, >Below is what I...

We are looking for the international eqivalents for the Excel worksheet functions AND CHAR REPT For Danish these are respectively OG TEGN GENTAG We are looking for the following languages. Can anyone help or direct us in the right direction. ' Russian 7 (Russian Federation) ' Arabic 966 (Saudi Arabia) ' Czech 42 (Czech Republic) ' Farsi 982 (Iran) ' Greek 30 (Greece) ' Hebrew 972 (Israel) ' Hungarian 36 (Hungary)...

I have a spreadsheet that I'm using to track progress over the period of a year. One sheet is raw data that is manually entered daily and one sheet is monthly statistics based on that data. In the statistics worksheet are cell formulas calculating statistics relating to each month based on the raw data. If I change the data in March, I don't want January's or February's statistics to be changed. Is there a way to do this? Thanks, PaulH On Mar 7, 11:52=A0am, PaulH <paul.h...@gmail.com> wrote: > I have a spreadsheet that I'm using to track progress over the perio...

This is way above my basic spreadsheet knowledge so please if anyone can help. I am trying to automate a small section of a booking form, Example In C7 26, D7 5, E7 2008. This represents 26th May 2008 In G7 5, H7 6, I7 2008. This represents 5th June 2008 M7 to show �229, which is a weeks rental M8 to show �229, which is a weeks rental I am trying to get M7 and M8 to show the cost for a week or part of a full weeks rental in these cells based on the info (dates) where the dates are entered. I have some cells below this where I can work out a discount for part weeks etc.(I ...

I am trying use the logical function to find the MIN of more than 2 numbers and then have the column heading presented in an adjactent cell from which the MIN number was found Hi if your values are stored in A2:X2 (with the corresponding headings in A1:X1) use the following functions: 1. To get the lowest number: =MIN(A2:X2) 2. To get the corresponding column Heading from row 1 use =INDEX($A$1:$X$1,1,MATCH(MIN(A2:X2),A2:X2,0)) -- Regards Frank Kabel Frankfurt, Germany jklen wrote: > I am trying use the logical function to find the MIN of more than 2 > numbers and then have the colum...

Hi, I have 19 excel files in a folder and I have created another excel file (B) in which I am trying to set a formula that can look in Cell "A6" which has the path with the file name of the first file (1) then copy From the first File (1) cell "L25" an place it in Cell "Y6" of File (B). I want the file names or file names with the path to be in file (B) so that I can change the name easily instead of changing all formulas. Hope I have clear explanation! Thanks, The function you'd want to use is =indirect(). But =indirect() won't work ...

I'm trying to get two prints of the same document: one showing absolute numbers and the other showing the formulas. When I'm pressing ctrl+~ to show functions for my 2nd print, the format is changing. The cells are getting too wide, so I end up getting only the left half of the document in print preview. How do I change the document to show functions with the format staying the same so I can print it??? Thanks. One way: Page Setup/Page/Fit to 1 page(s) wide. In article <D2EAE056-FC1E-4F40-BA48-974B191C4B8D@microsoft.com>, "bruno" <bruno@discussions.micro...

I've just installed Excel 2003 at home. When I select a function in the Insert Function dialog, it takes FOREVER for the Function Arguments window to appear. The Arguments windows is animated to appear to pop up from under the formula bar. I can count the seconds watching the animation of the box appearing, and it's so annoying. In Excel 2000 I could have already completed entering the arguements by the time the window comes up. None of the other menus appear to be slow. I've tried looking for a way to turn off menu animations both in Excel and Windows in general and nothing seems...

Hi Using Excel - In column A I can enter 4 different cash values e.g. 30 / 13 / 10 / 5 using the formula in column B/C/D/E =IF(A3=Rate_30,A3,"") 30 shows in the cell B3 cells C3 / D3/ E3 for that row shows zero However when I need another rate e.g. 50 to enter column F and changing the above formula to =IF(A3=Rate_50,A3,"") the cell shows #NAME? - what am I doing wrong ???? Any much appreciated Cheers --- Mully Hi Mully, Rate_30 is a defined name, has nothing to do with the number 30. You can either define a name Rate_50 (menu: Insert>Name>Define) or use...

When combining text and cell values in a concatenate function, I would like to control the number format in the concatenate cell: comma separator, decimal place, etc. ="this is text " & text(a1,"$000,000.00") & " due on: " & text(b1,"mm/dd/yyyy") R1dgeway wrote: > > When combining text and cell values in a concatenate function, I would like > to control the number format in the concatenate cell: comma separator, > decimal place, etc. -- Dave Peterson ...

SSBjYW4ndCBzZWUgIkluc2VydCBmaWxlIGFzIHRleHQiIGFueXdoZXJlLCBuZWl0aGVyIGNhbiBJ IHNlZSAiVmlldyBSZXBsaWVzIHRvIG15IFBvc3RzIiBpbiB0aGUgVmlldyBzZWN0aW9uLg0KQXJl IGVpdGhlciBvZiB0aG9zZSB0aGVyZSwgb3Igc2hvdWxkIEkgcHV0IGluIGEgcmVxdWVzdCB0byB0 aGUgQmV0YSBmZWVkYmFjaz8= This is a multi-part message in MIME format. ------=_NextPart_000_006E_01CB390B.9FA7D130 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Ribbon/View tab/Filter Messages - same 4 options as in Wave 3 RTW version(Show All, Show Downloaded, = Hide Read, Hide Read or Ignored) I...

next to the function button as it did in Excel 2000?? Why did they create this function wizard and make things more cumbersome?? Only the developers know why they did away with this in Excel 2002 and 2003. The return of this feature is on the wishlist for the next version. Adoption and Implementation of features on the wishlist is a whole 'nother matter. Gord Dibben Excel MVP On Mon, 24 Jan 2005 15:17:06 -0800, pvmonsour <pvmonsour@discussions.microsoft.com> wrote: >next to the function button as it did in Excel 2000?? Why did they create >this function wizard and ma...

Thought I might use arrays to collect chart series X and Y values from a mass of data (simplified example code below) I found that with simple integers I can load a maximum of about 140 values. With DateTime numbers this comes down to about 16 maximum. Is this to be expected? (Excel 2003) Dim myChtObj As ChartObject Set myChtObj = Sheets("chart").ChartObjects("Chart 1") With myChtObj.Chart .SeriesCollection(1).Values = Array(4,5,6,...,n) .SeriesCollection(1).XValues = Array(1,2,3,...,n) End With The Series formula is limited to an absolute maxim...

OK, maybe that wasn't the best way to ask the question... I need to create this scenario: Question: "Lot Size?" Answered by "X" under "Small"(A3) "Medium"(A4) or "Large"(A5) If (A3) = "X", then (J2)= 500 If (A4) = "X", then (J2) = 600 If (A5) = "X", then (J2) = 700 Obviously, these function will have to recide in the J2 box. I can get it to work individually using the "=IF" function, but how can I insert multiple "=IF" functions into the (J2) box? I have tried using the "=OR"...

I have a strange requirement maybe, but I do a budget spreadsheet on which I highlight items that have cleared my checking account. I would like to be able to write a formula that sums only the cells that are highlighted a certain color. Is this possible? let me clear this up a little. I don't really mean "highlighted" as in I highlighted them with a mouse. What I mean is formatted with a fill color. sorry for the confusion. "Robin" wrote: > I have a strange requirement maybe, but I do a budget spreadsheet on which I > highlight items that have ...

Hi I need formula which calculate finish day. I have start day and number of hours for task - how to calculate automatically finish day?? Regards -- jkazan ------------------------------------------------------------------------ jkazan's Profile: http://forums.techarena.in/members/134110.htm View this thread: http://forums.techarena.in/microsoft-project/1294750.htm http://forums.techarena.in Why don't you let Project do the calculation? That's the point of the software.... - Andrew Lavinsky Blog: http://blogs.catapultsystems.com/epm > H...

I have a workbook with many sheets that all have formulas and links to other data. I want to save the workbook as another name with all the worksheets keeping the values only (no links or formulas). Is there a quick way to do this for everysheet without having to special paste every sheet in the workbook. So can I save everysheets data values at workbook level. See this page for a code example http://www.rondebruin.nl/values.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "lex63" <lex63@discussions.microsoft.com> wrote in message news:ED708...