If I have a set of veritcal data for each employee, but each set of data could range from 1 to 4 vertical values, is there a way to change the following formulas to accomodate moving the data from horizontal to vertical?: =INDEX($A$2:$A$1690,1+(8*(ROWS(H$1:H1)-1))) =OFFSET($G$2,ROW(A1)*8-8+COLUMN(R2)-18,) Data Sample: ID Data 275 Capitalize on Technical and Professional Know-How 275 Foster Teamwork 275 Value Others 933 Demonstrate Agility 933 Drive for Results 933 Team Leadership 933 Use Technical/Functional Expertise 658 Capitalize on Technical and Profess...

Using: Excel 2000, on Windows 98 Good morning! I'm hoping that someone on here can possibly help me. I have basic Excel knowledge, but this is out of my skill set. My boss has given me a project to work on, in which I have a workbook with two worksheets that I am dealing with. The worksheets are labelled PRICE and SUMMARY. On the PRICE sheet, I have several columns. Column A lists the product name, Columns E list the per page charge for the *red* program, and Column G lists the per page charge for the *blue* program. Those are the columns we will be dealing with. All specific inform...

After the formula in a cell has calculated a value I would like to reset the cell for the next use by deleting the value but keeping the formula in the cell. Is this possible in Excel 2007? You need to explain that a bit. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Lofred" <Lofred@discussions.microsoft.com> wrote in message news:D3DE79C2-C41E-46C7-8F6C-EAD9960EC528@microsoft.com... > After the formula in a cell has calculated a value I would like to reset > the > cell for the next use by deleting the value but...

Hey everybody, I'm probably missing something obvious, but please help me if you can. Here is my formula and now it just results in an empty cell. It won't produce a result. THANKS IN ADVANCE. =SUM(IF($C$73:$C$77="Large-Cap/Growth",IF($C$73:$C$77="Large-Cap/Value",IF($C$73:$C$77="Small/Mid-Cap/Growth",IF($C$73:$C$77="Small/Mid-Cap/Value",$D$73:$D$77,0),0),0),0)) Originally I was trying to sum a table and have the answer appear on a different worksheet and I kept getting a Runtime error 1004: unable to get the formula array property of the Range ...

Hi, I need the formula for splitting names... i.e need "Martyn Williams" in A1 to be splitted to "Martyn" and "Williams" in B1 and C1 TIA One way, assuming the names are all in a "2 word" structure: In B1: =LEFT(TRIM(A1),SEARCH(" ",TRIM(A1))-1) In C1: =MID(TRIM(A1),SEARCH(" ",TRIM(A1))+1,99) ("99" is arbitrary, just choose a number high enough to extract the max likely # of characters in the 2nd word) Another easier? way to try is to use: Data > Text to columns Select A1 Click Data > Text to columns > Next In...

Hello. A customer can't use "Copy Special" in Office 2007. He has upgradede Office 2007 from 2003. When he copy cells to another workbook, the formulas aren't copied. And when he tries the "Copy Special" a windows comes with questions about objects. He want's to be able to copy cells without formulas but with nubmer formats. I'm able to do so on several PC's. Not on his. Because of the upgrade? -- Regards, Steffen My first guess is that the different workbooks are open in different instances of excel. Close one workbook and that instance of exce...

Hi there, Does anyone know how I can replicate formulas (verbatim) between difference excel files? I've tried to cut and paste between the separate files, but to my dismay it makes a reference to the original file. Regards, Nickchups The best procedure depends on how many you're trying to copy. For one or two, you can select the *entire* formula in the formula bar, right click and choose "Copy", then hit <Enter>. Now you can navigate to wherever you wish, and Paste it as many times and/or places you want. For numerous formulas, *unformulate* them by replacing t...

Hello, I have Excel 97, and am trying to learn formulas. I need to find the "average" of certain values in column B below. This is an example of my worksheet A B 1 TRUE 1.0% 2 FALSE -.8 3 TRUE -.5 4 FALSE -. 5 TRUE 1. 6 TRUE -. 7 FALSE . What I need: In Cell C50 I want the calculated result in the form of a "%". FIRST finds the "TRUE" ocurrances in column "A", then calculate the average % (both positive and negative) in column "B" that are next to "T...

Hi there I am having trouble trying to display result. Basically say A1 has 10 in it and A2 has 5 in it A3 has the formula A3 =sum(A1-A2) {answer =5} What I want it do do is... say A4 displays HW as per the key. so if A1 = 2 and A2 = 7 A3 answer is -5 - A4 then displays AD and so on the key 0 to 3 = HD 4 + = HW -1 to -5 = AD -6 + = AW Any help gratefully appreciated. cheers Paul PS: I have posted in excel worksheet function forum too. Hi Paul no need normally to post in more than one ng =IF(A3>=4,"HW",IF(A3>=0,"HD",IF(A3>=...

I need a easy way to count the following cells with numbers less then 10. columns c to h, rows 2 to 20, 30 to 40 & 50 to 60 in each column. The next 5 sheets I need to do the same thing but the rows are different on each sheet. I was going to use =(COUNTIF(c2:c20,"<10"))+(COUNTIF(c30:c40,"<10"))+(COUNTIF(c50:c60,"<10"))+. ... and so on until I have all the rows and columns covered but the formula will get very long. Is there a shorter formula I could use to save from using this very long formula??? Thanks If the dimensions are identical in the...

When I input the string 4/9/4 into a cell having a date format of dd/mm/yy, Excel first converts the entry into the formula =4/9/4 (or 0.111111) and then converts the result into the date 01/00/00. I assume I need to change a setting somewhere to get 04/09/04, but I have grown weary of looking for it. Thanks. The only way I can get that to happen is if I type in =4/9/4 into th cell. You should be typing 4/9/4 (no equal sign). Maybe? -- Message posted from http://www.ExcelForum.com I'm not typing the equal sign. Excel is adding it. >-----Original Message----- >The only...

Hi All, I'm working on VSTO Excel 2007 AddIn in C#. I need to call Formula Dialog and than to fetch the formula string from that dialog. Problem is that dialog doesn't offer something like dialog.FormulaText but instead inserts formula in ActiveCell. So code like this: var dlg = this.Application.Dialogs[Microsoft.Office.Interop.Excel.XlBuiltInDialog.xlDialogFunctionWizard]; dlg.Show(missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, miss...

Have some formulas like the following:- =IF(B10=0," ",LOOKUP(B10,Menu!$A$1:$A$146,Menu!$B$1:$B$146)) wanted to change the lookup range, but every time i change the formula it just displayes the formula, and not the resulting value. WHY?? I have checked the View Options and they are OK. Tried deleting an retyping the formula. Nothing helps Can some1 help me please ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Actually what you want to make sure you have on ...

Hi Kitty, =DATE(YEAR(A1),MONTH(A1)+1,1) -- Kind Regards, Niek Otten Microsoft MVP - Excel "kitty" <kitty@discussions.microsoft.com> wrote in message news:2C586891-2883-47B9-A3DE-AF77D4A9799E@microsoft.com... > Hi kitty, One way of doing it is with the formula below: If you have a date is cell A1 (01/01/04) and you want cell B1 to sho you the following month insert in B1 the formula below. =EOMONTH(A1,0)+1 You may have to install the "add in" the ToolPak in order for th formula to work other wise you will get a #NAME?, error. Hope thi helps -- Fa...

I have entered a conditional formula, that is if an adjacent cell contains a number then the NOW function will enter the current date and time into another cell. If I copy this formula in subsequent cells and enter a number into the conditional cell excel recalcs both cells, however I want the first cell to calculate once then freeze. The reason for this is i want to generate unique document numbers from exact times and dates. You can use shortcut keys to enter the current date and time: CTRL+; for the date and CTRL+SHIFT+: for the time. On Thu, 5 Aug 2004 16:07:02 -0700, "cameron&...

Hello, I want to make all the conditions occur in cell b1: if cell a1 = 1 then cell b = 7 if cell a1 = 2 then cell b = 6 if cell a1 = 3 then cell b = 5 if cell a1 = 4 then cell b = 4 I know how to do =IF(G2=1,"7") but I can't figure out how to add the rest of the conditions to it so they are all in cell b1. I've tried a dozen different combinations of commas, brackets, colons, etc. and searching "help" isn't helpful! How do I combine all these conditions? Thank you. Sorry, I meant to say b1 for all the conditions where b occurs. So for ...

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...

June 30, 2010 A B C D Birth Date Date of Hire Appt. Int/Act. Appt. VP November 22, 1962 September 1, 1988 N/A September 1, 1998 Total Admin Service Total Bd Service E F 11.84 21.84 I am usint June 30 , 2010 as the end date for the formula. From Sept 1,1998 to June 30 I get 11.84 years. this should be 11 years 10 months and then From Sept 1, 1988 to June 30, 2010 should be 21 years 10 months. I tried...

What is the formula for rounding a dollar amount to the nearest nickel? Didn't make sense... I meant cents rounding to nearest nickle,Example: 1.22 = 1.20 or 1.23 = 1.25. ;-) "JeriSys" wrote: > What is the formula for rounding a dollar amount to the nearest nickel? Try this: For a value in A1 B1: =ROUND(A1/0.05,0)*0.05 Does that help? *********** Regards, Ron XL2002, WinXP-Pro "JeriSys" wrote: > Didn't make sense... I meant cents rounding to nearest nickle,Example: 1.22 = > 1.20 or 1.23 = 1.25. ;-) > > "JeriSys" wrote: > >...

When one is in the process entering a formula into an EXCEL spreadsheet it is possible to click on another cell to automatically have this cell reference placed into the formula. However this entry always is entered as a RELATIVE cell reference. Is there a way to have EXCEL insert an ABSOLUTE cell reference into the formula?? It seems to me that it would be so common and yet I have never been able to figure this one out. Please Help Michael Karas Hi Michael! Is this what you want? Click the cell and then press F4. F4 acts as a toggle going through the four reference options. -- ...

I need to try to create a formula to convert numbers to letters using the following scale: 0 = A 1 = B 2 = C 3 = D 4 = E 5 = F 6 = G 7 = H 8 = I 9 = J Such that 12.34 would be transformed to BCDE. This is the formula I had tried, but it won't accept: =CONCATENATE(HLOOKUP(RIGHT(ROUNDDOWN(M2/10000,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2/1000,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2/100,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2/10,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2*10,1),1),A1:J2,2)) M2 is my starting number and the...

Can anyone help me with a question about an IF formula? Is there any way to write an IF formula that causes multiple actions or results to occur as part of the value_if_true or the value_if_false portions of the formula. For example if an IF Formula reads: IF(A1="1",1,0)) -- the A1="1" portion is the logical_test, and 1 is entered as the value_if_true and 0 is entered as the value_if_false is there any way to make the value_if_true cause more than one result, i.e. B1=1 and C1=1. Thank you in advance to anyone who can help. Hi Ed, No. A formula can only return a value, ...

I tried to do that but it will not work in the actual cell reference I'm stumped. humejap Wrote: > Have you tried the formula > > =Concatenate("$J$",H1) > > > -- > humejap > ------------------------------------------------------------------------ > humejap's Profile > http://www.excelforum.com/member.php?action=getinfo&userid=5506 > View this thread > http://www.excelforum.com/showthread.php?threadid=39509 -- jhockstr I missed the original question, but whilst =Concatenate("$J$",H1) will join to give $J$ joined to t...

I've tired =j6+g21 but from different sheets, so its possible when i change one the other changes also. What formula do I need, because thats just not working Type the = sign, then go to the other sheet and click in J6, then hit the + sign, and go to the relevant sheet and click in G21. Hit enter. You should have a formula that looks something like =Sheet2!J6+Sheet3!G21 -- David Biddulph "gnagy84" <gnagy84@discussions.microsoft.com> wrote in message news:250E38BC-6FDF-4AE0-BB5D-EFBCA737B573@microsoft.com... > I've tired =j6+g21 but from different sheets, so i...

I would like to use the same name to refer to the same set of cells on different worksheets: SheetTitle=$A$1 but when I try this it reverts to SheetTitle=Sheet1!$A$1 Any ideas how to do it? I could use =IF(,,,) if I knew how to look up the currently active sheet; =IF(SheetName="Sheet1",TRUE,FALSE) Any ideas how to reference the name of the sheet? Well, you could do something like this: SheetTitle=!$A$1 The exclamation point means that the Name will refer to A1 on the active worksheet. Excel uses the SheetName to tell things apart. If I try to give to different A1'...