Reply:

Help simplifying a SUMPRODUCT formula
The following SUMPRODUCT formula produces the correct results but I'm reasonably certain that there must be a more efficient way of constructing the formula. There are basically two components to this formula separated by the + sign. Each component performs the same calculation: the first part for Class="MS" and the second part (after the +) for Class="MSTV". Isn't there a way to construct the formula so that it would be calculated for Class="MS" OR Class="MSTV" and eliminate the need for two steps? I tried to incorporate the OR formula wi...

Help With an Excel Formula
I am in excel and I have the following formula = Q:26 typed into Cell C:2. What I want to do is have a second cell D:2 that takes what ever cell reference I enter into C:2 and adds 1 row to it. So cell D:2 would be equal to Q:27, which is 1 row down from row Q:26. Is there a way to do this? Not possible unless you use an add-in or user define function that reads the text of the formula in C2 D McRitchie has UDF for that http://www.mvps.org/dmcritchie/excel/formula.htm then you could use =OFFSET(INDIRECT(SUBSTITUTE(getformula(C2),"=","")),1,) Regards, Peo Sjobl...

How to vlookup and summing value?
Hi, please help.... Worksheet name: "Pages" Column A Column B James $100 James $50 AJ $30 Light $46 James $80 When i type on cell A1 on a separate worksheet this formula =VLOOKUP(A1,Pages!A2:B5,2,FALSE) , it only provide me $100. Is there a way to sum up the total to be $230 under "James"? Hi, =SUMPRODUCT((A1:A15="James")*(B1:B15)) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the ...

Formula help needed
Can someone help me to shorten this formula, it slows down my computer so it takes ages to recalculate the sheet when I enter something in it. It is used to calculate time, basic formula (A2-A1+(A2<A1)) from C Pearsson�s site OFFSET(INDEX(Tid3;MATCH(A8&1;Feb!$A$9:$A$250&Feb!$B$9:$B$250;0);MATCH(Feb!$C $8;Feb!$A$8:$D$8;0);1);1;0)-INDEX(Tid3;MATCH(A8&1;Feb!$A$9:$A$250&Feb!$B$9:$ B$250;0);MATCH(Feb!$C$8;Feb!$A$8:$D$8;0);1)+(OFFSET(MATCH(Tid3;PASSA(A8&1;Fe b!$A$9:$A$250&Feb!$B$9:$B$250;0);MATCH(Feb!$C$8;Feb!$A$8:$D$8;0);1);1;0)<IND EX(Tid3;MATCH(A8&1;Feb!$A$9:$A$...

Convert String to Numeric Values
I have data string that I must convert to numeric values. Ex: 00100200300400500600700800901000110015016 How do I get the above data string example to return the numeric values that are three digit integers. How do I get the number 16 for example? Any assistance is appreciated -- JenISCM Try this: Public Sub MySplit(txtIn As String) ' copy/paste to a standard module ' input from debug window: ' call mysplit("00100200300400500600700800901000110015016") Dim i As Integer Dim n As Integer i = 1 For n = 1 To CInt(Len(txtIn) / 3) Debug.Print n & " - " ...

getting value from a cell into another cell behind a text string
Hi, I'd like to know how to get a value from a cell into another behind a text string by using a formula. For example, A B 1 20 Result (20) Thanks, Xianbo Use formatting Pull-down Format > Cells... > Number > Custom and the the field enter: "Result ("General")" The advantage of using formatting is that the cell can still be used for calculations -- Gary''s Student "Xianbo" wrote: > Hi, > > I'd like to know how to get a value from a cell into another behind a text > string by using a formula. Fo...

display order of values without sorting
I would like to create a sheet that can give me a weighted score at the end of the row. What I mean by this is I have a sheet with 4 colums and 10 rows, each of the rows will have a weighted number in each of the colums with a total at the end. With out sorting the rows I would like to rank them in importance, the highest number being a 1 the next a 2 the next highest a 3 and so on. Is there a function that does this or does someone have a formula? Thanks Try this… This formula will create logic and will assign a value for the equivalent word. =IF(A1="two"...

FORMULAS please help
Hi there I am trying to create a formula that refences <> numbers in a columm then adds up the from a different columm i.e add b1:b100 only if a1:a100 is >10000 <12000 =46 a b 10000 32 9000 64 12001 86 11999 14 9990 12 One way =SUMPRODUCT(--(A1:A100>10000),--(A1:A10000<12000),B1:B10000) Regards, Peo sjoblom boogie wrote: > Hi there > I am trying to create a formula that refences <> numbers in a columm then > adds up the from a different columm > i.e add b1:b1...

Repeating formula in Excel
Hi. I need to copy a number into 500 rows, with each number increasing by 1 in value. These numbers are used to track call numbers, so the format is like this: 2005-001 and I need it to repeat itself up to 2005-0550, and I don't want to spend the time typing each value in manually. Someone please help!!! One way: Delete the dash from your number. Select the cell with the number. Go to Format | Cells | Number. Choose Custom and type: 0000-000 click okay. Then, (still with the same cell selected) go to Edit | Fill | Series. Make sure the Step Value is 1. Change to Columns and put a s...

Replacing formula to cells EXTREMELY slow
Hi! I have the following code ------------------------------------------------------------------------------ Private Sub ToggleButton3_Click() Dim i As Long Dim Lastrow As Long Application.ScreenUpdating = False Lastrow = 1000 If ToggleButton3.Value = True Then 'Fill in the checking formula For i = 0 To 5 Cells(4, i + 9).Formula "=IF($A4="""","""",COUNTIF(INDEX('Check'!4:4,1,$AI$2):INDEX('Check'!4:4,1,$AI$3), & i & "))" Next Else For i = 0 To 5 Cells(4, i + 9).Formula "=IF($A4="""",...

Excel chart data labels showing % change instead of value?
I'm needing to have my excel chart show me the percent change in my 2 series. I would like this in a data label over series 2 on the chart, but all I can figure out to do is have the value labeled. Does any one have any suggestions? I'm believe I'm using Excel 2002. You can use Rob Bovey's XY Chart Labeler. http://www.appspro.com/Utilities/ChartLabeler.htm "MFritz" <MFritz@discussions.microsoft.com> wrote in message news:29317BE2-4574-4623-8D5E-A3BA97B3FAE6@microsoft.com... > I'm needing to have my excel chart show me the percent change in my 2...

Formulas dont work
I am trying to do a vlookup function with data that is imported and calculated from another cell, and it shows a result of #na. c3 is formated to custom "m" to show the number of the month from cell b3. b3 is "October-04" c3 shows "10" d3 "job name" b100 "10" a100 =vlookup(b100,c3:f20,2,false) result is "#na" If I enter "10" into cell c3 with format set to general, then the result shows the correct answer. Is there a way to correct this? -- comotoman -----------------------------------------------------------------...

Automatic Pareto data with Formula
I want to create a data table for use to create an Automatically updated Pareto Chart, for this I need that the values automatically be sorted in descending order, so I have the following example scenario: Column A has Labels and Column B has Values A -1 B -2 C - 3 D - 2 E -1 If I use the following formula: =INDEX($A$1:$A$5,MATCH(LARGE($B$1:$B$5,2),$B$1:$B$5,0)) I get B, but there are two 2 and if I use the 3rd largest: =INDEX($A$1:$A$5,MATCH(LARGE($B$1:$B$5,3),$B$1:$B$5,0)) I also Get B instead of the required D. How can I get it so the end results looks like this: C ...

Use input box to fill cells?
My question may be quite simple, but I have not been able to find any info on it (due, no doubt to ignorance) I want to use the info from an input box to fill certain cells, basically prompting the user for the info to populate the cells. Let's say that I want to put customer names in A8-A100. How can this be written to allow the user to have this info put in, be able to exit at any point, and possibly insert cells if they go beyond the range stated above? Thanks Maybe you could select the range, then do data|form? David Sizemore wrote: > > My question may be quite simple, but ...

Display Formula Problem
All, I have search Microsoft's Knowledge Base, but could not find an answer. Hopefully, someone here can solve my problem. I am using Excel 2002. I have several project managers maintaining budgets in Excel workbooks. I am creating a summary Excel workbook. To get the data out of the workbooks, I start with entering an equal sign, click on the cell in one of the other workbooks, and press enter. The value from the source workbook is displayed correctly. Since I want to capture several columns and rows around this first cell, I want to copy formula in the first cell into near cells, I mu...

Returning a Value from Another Worksheet
I have two worksheets in one Excel 2007 file that I'm working with, in which I want the second worksheet to return values of the first worksheet. Hope I can explain it clearly here: The first sheet contains a list of data and numbers to caculate a points system. For example: Worksheet1 Name Column B Column C Points Data1 100 5 4 Data2 75 2 1 ... 2) In the second worksheet, I want to be able to input a specific value from Column A (i.e., I inputted "Data2" from Workshee...

Setting a conditional value in a query to perform an additional qu
Ok, I have a slight brain twister here and wondering if anyone could help shed some light on this. :) Im in the midst of creating a database that will hopefully replace a paper-based system of storing 'shift runsheets' from a 24/7 office. There is 3 shifts per 24 hours; Morning (6am-2:30pm), Afternoon (2pm-10:30pm) and Night (10pm to 6:30am). Each day it adds a new row of data to the table "shift" and users can peform searches on previous sheets by searching the table "previous" which links to "shift". When a user wishes to search, they select the ...

percentage formula #2
What is the formula for using one cell to figure a percentage of another cell. Example: colume A colume B 20000 13% of colume A Thanks One way: A1: 20000 B1: =A1*13% ==> 2600 In article <093f01c3861f$4a6902d0$a301280a@phx.gbl>, "Shawn S." <shawn3731@yahoo.com> wrote: > What is the formula for using one cell to figure a > percentage of another cell. Example: > colume A colume B > 20000 13% of colume A > Thanks ...

From ListBox selection >labels on userform to show sheet cell valu
I have 3 Userforms; the first contains a ListBox from which user select a specific Quotation number (column A on underlying worksheet). The first UserForm also contains text boxes that, upon a "cmdShow" button go and get relevant data from the row chosen by the user in the ListBox and show these values in the textboxes on UserForm1. All fine. On Userform2, I do not want user to have to choose Quotation number from the same ListBox (also on Userform2) in order to populate the textboxes on UserForm2 (different cell values from same row as User selected when on UserForm...

Formula using hours
How can I create a formula that will say: If this cell is higher than 9:01 but but lower than 9:04 show a number? Thanks for your help! Hi Ali, =IF(AND(A1>TIME(9,1,0),A1<TIME(9,4,0),17,"") -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Ali" <anonymous@discussions.microsoft.com> wrote in message news:731101c3e72a$ceb69220$a501280a@phx.gbl... > How can I create a formula that will say: > > If this cell is higher than 9:01 but but lower than 9:04 > show a ...

Excel Formula #21
Thankyou McGimpsey for your quick reply appreciated, and the frormula also works -- SAMSON ...

Create value list from a range of values
I've got a range of cells containing state abbrev. that I want to lis in another range excluding all duplicated states abbrev's. Is there a way to list the non-duplicated values automatically with function? I've tried using the excel Data Filter menu but it seems that needs t be performed manually. I need a function that automatically calculate when there is a change or addition to my list of states. Thanks for your help.. -- mallets12 ----------------------------------------------------------------------- mallets123's Profile: http://www.excelforum.com/member.php?action=...

Maintain cell reference after value is moved
How can you keep a reference to another cell from changing even if the other cell's contents are moved? The objective is to maintain a column of running summations, such as a projected bank balance, that refer to anticipated credits or debits in another column, even as those entries are moved about. For example, cell G100 has the formula =SUM(D$4:D100), which works only until the value in D100 is moved, say to D150, whereupon the formula in G100 is automatically changed to =SUM(D$4:D150). I can copy and paste the value to D150 and then just delete the old value in D100...

Add values of cells
I need to sum the values of a column of cells with both positive and negative numbers, but I want to exclude the negative numbers? Use: =SUMIF(D2:D5,">0") With D2:D5 replace by your range. -- Message posted from http://www.ExcelForum.com Hi try =SUMIF(A:A,">0") -- Regards Frank Kabel Frankfurt, Germany B2 wrote: > I need to sum the values of a column of cells with both > positive and negative numbers, but I want to exclude the > negative numbers? ...

Advanced Vlookup Formula
Hello, I have a list of unique values in Column A in sheet 1. In sheet2, I am have data laid out in a table with row headers and column headers. I am trying to come up with a formula that will display the value that intersects in sheet 2, based on the combined column header and row header. So for example. Sheet 2 Column Headers Row Headers State City Weight Jeff TN Nashville 200 Tim FL Miami 155 Eric GA Atl 225 Sheet 1...