For the odds range, i have already two cells(the upper(F9) and the lower(E9)) and i have a cell named the current odd(G9). What are the formula together for the attribut cell(H9) for the following data:
1.0 : The current price is outside and below the odds range spread. For example, if the range is between 2,00 and 2,20, the range’s attribute will be 1.0 if the current price is below 2,00
NOTE: Obviously the formula for this one is =IF(G9
1.1 : The current price is on the bottom edge of the “odds
range” figure. For example, if the range is between 2,00
and 2,20, the range’s attribute will be 1.1 if the current
price is 2,00
NOTE: The formula for this one would be =IF(G9=E9,1.1)
1.2 : The current price is inside the “odds range” spread
and is placed one or two clicks from the bottom edge. For
example if the range is between 2,00 and 2,20, the range’s
attribute will be 1.2 if the current price is 2,02 or 2,04
NOTE: I have a hard time creating a formula with this one.
2.0 : The current price is outside and above the “odds
range” spread. For example, if the range is between 2,00
and 2,20, the range’s attribute will be 2.0 if the current
price is above 2,20
NOTE: The formula would be =IF(G9>F9,2.0)
I'm essentially trying to place a column that is largely based on another....a function that will calculate the following information
Carriers with 1 Vehicle - 25.00 Carriers with 2-4 Vehicles - 100.00 Carriers with 5-9 Vehicles - 200.00 Carriers with 10-24 vehicles - 400.00 Carriers with 25-49 vehicles - 750.00 Carriers with 50+ vehicles - 1500.00
In other words.
One column in my spreadsheet has number of vehicles. I want another column that will provide the according application fee as found in the information above.
I've tried many variations but I can't seem to find a solution - I hope you can help. I'd like a formula that will ideally achieve the following:
IF B5:F5 all contain the letter y then return "Pass" IF B5:F5 AND G5:K5 all contain the letter y then return "Merit" IF B5:F5 AND G5:K5 AND L5:K5 all contain the letter y then return "Distinction" IF B5:F5 is blank return blank IF there are Y's in B5:F5 and the total < 5 return "Fail"
I did a sample sheet to show the desired output but don't know if this forum supports attachments.
I have two spreadsheets (the samples for which I have shown side by side in Sheet 1 of the attached file).
Spreadsheet 1 is about 30,000 rows and too large for me to change the formatting and structure.
Spreadsheet 2 is the output I need and the format is required by other stakeholders.
In spreadsheet 1 I want to sum quantity in stock for Type 1, Type 2 and Type 3 for each product and allocate it to spreadsheet 2 according to the month in which the product expires. For example, there will be a total of 92 units of product 413302 which will expire in Nov, 2014. Therefore I want 92 to be placed in cell N6 of Spreadsheet 2.
Unfortunately the product number is not unique - there are multiple sub products in spreadsheet 1 but they all have the same quantities of stock. The sub products are referenced in other parts of the report so I can't consolidate by Product Number. This also prevents me from using the SUMIFS function as it will duplicate the number found in the sub products.
What I need, perhaps in a combination of functions, is to find the first instance of product 413302 in Spreadsheet 1 that is expiring in Nov 14, sum the product types and give the result in cell N6 of Spreadsheet 2.
Is there a way to SET a RANGE that can be used for further editing without the need to specify the range each time for each function to be perfomed? I have a pile of Data, with a Calendar date and 4 columns of data for each date.
I want to make a summary for each year separately of one column of data (Total PL). How can I set a condtion that it will output a summary of data for a given year, e.g. 1990?
Even more useful might be setting up a table for each year, where I will be able to perform easy function like SUM, MAX, MIN but it will use data only from the specific Year in each row. - Is there a way to make it simpler, but not having to specify the RANGE for each Year for each Function?
* I have a complex Excel workbook with several worksheets.
* The first worksheet is for raw data, which consists of a single string of data, separated by spaces, to be uploaded to a mainframe. There is no consistent pattern of length of data or strings in between pieces of data, which can vary widely. An example of the data would be something like this:
XXXXX YYYYY NN XXXXXXX
* For testing purposes, I need to come up with a way to parse the data for easier viewing. The current approach is this:
* Take the string and divide it up into individual cells by using the MID function. In the above approach, this would split the data into four (4) different cells.
This is going to be a very complex workbook with data strings that can have 20+ elements in them, and have lots of separate records, etc. While this way should work (if everything is entered perfectly and no data structures ever change, etc.),
I am trying to somehow use multiple IF functions. I was able to use one IF function to disply '--' in a cell if a particular formula = 0. However I ran into a problem when negative values were displayed. I wanted the value to be a positive number. I thought I could use another IF function to multiply the formulas contents by -1 if it were <0, but I keep getting a #VALUE error in my cells. Is there a way I can have multiple conditions?
I am trying to create a custom function to act much like the database capabilities of excel in summing a column based upon multiple criteria of other columns. First, before I get too far, does anyone have an already built custom VBA function that will accomplish this? I find that using the built-in database functionality in excel is VERY slow!
Anyways, I am trying to pass an argument that contains multiple ranges (much like you can do with the SUM() function):
The first set of arguments is the ranges that I need to look through. The second set of arguments is the values that I need to search within the first two ranges. The third argument is the column that I need to perform a SUM function on.
Instead of doing this, how can I create a function that will allow me to say, "Add another criteria range/value" and it will add another criteria to the list. I am currently passing a string, and that is not ideal, obviously.
I have a spreadsheet using Data Validation to offer a list of text values for cells in the range E6 - E100. I have a worksheet change funtion to change the colour of the entire row based on the text chosen from the list.
What I would like to add is an input box that will appear when the value in the cell = "COMPLETE" asking for a completion date and adding the inputted date to the cell in column I in the same row.
Existing Worksheet Change Code;
Private Sub Worksheet_Change(ByVal Target As Range) 'Colour code rows based on order status Dim rng As Range, i As Long Dim cell As Range, Answers As Variant Dim Colors As Variant Colors = Array(24, 15, 38, 44, 42, 20, 36) Answers = Array("CLOSED", "SUSPENDED", _ "COMPLETE - Awaiting Inspection", "COMPLETE", "WORKING", _ "SCHEDULED", "READY") Set rng = Range("E6:E100") rng.EntireRow.Interior.ColorIndex = xlNone For Each cell In rng For i = LBound(Answers) To UBound(Answers) If LCase(cell) = LCase(Answers(i)) Then cell.EntireRow.Interior.ColorIndex = Colors(i) Exit For End If Next i Next cell End Sub
I need to create a formula that counts the number of times that an age range appears within a column. In column G, there is a list of ages based on a demographic collection. The ages range from 13 to 50+. I want to designate another cell to count the number of times the characters between 13 and 18 occur within that column. I have =COUNTIF(G8:G20,"13") How do I add "14", "15", "16", "17", and "18"?
I have multiple IF functions in a formula and found out that the maximum allowed is 7 and should use Lookup instead. The formula is to calculate the Present Value of an amount with the corresponding interest rates and number of days left.
I am currently having trouble with what I thought was a simple IF statement. As you will see from the formula I want to complete a statement for every month of the year but Excel will not let me go past July. Is there a limit to the number of arguments in an IF statement and how do I overcome this? =IF($A$3="Jan'08", SUM(C7),IF($A$3="Feb'08",SUM(C7:D7),IF($A$3="Mar'08",SUM(C7:E7),IF($A$3="Apr'08",SUM(C7:F7),IF($A$3="May'08",SUM(C7:G7),IF($A$3="Jun'08",SUM(C7:H7),IF($A$3="Jul'08",SUM(C7:I7),"n/a")))))))
I am having trouble evaluating two conditions with nested logical operators to produce the output I need.
For example Condition 1 has 5 choices (A,B,C,D,E) and Condition 2 has 3 choices (a,b,c).
Breaking it down, [Condition 1] option "A" always applies and should give "Yes" (No matter [condition 2], "A" always gives "Yes") [Condition 2] option "c" always applies and should give "Yes" (No matter [condition 1], "c" always gives "Yes")
[Condition 1] option "D" never applies and should give "No" (No matter [condition 2], "D" always gives "No")
[Condition 1] options "C" and "B" only apply if [condition 2] is "b" (if so, the answer is "Yes")
What I need is the equivalent of:
if ((([condition 1] == A) || ([Condition 2] == c)) || (([condition 1] == B || C) && ([condition 2] == b)), "Yes", "No")
I have a model that does a projection for 36 months ahead on a spreadsheet. The projection depends upon a randomising "volatile" function. When I press recalculation a new projection is generated. I want to capture the set of numbers that appears at month 36 through repeated recalculations written to a separate sheet. I want around 500 of these numbers.
I have no experience with macros so doing it with preset functions would be preferable. If it has to be a macro, the easier the better. If invoking the spreadsheet and outputting to a flat file is easiest that is fine too.
I have several formulas that I use over and over, so I'm creating VBA functions that I can call from a worksheet.
But...when I have multiple inputs going into a function, is there a way to remind the user which input comes next in the same way that Excel does with built-in functions?
For example, when you type in =PMT( Excel brings up the helper text so you know that the order of inputs is (rate, nper, pv, etc....
Is there a way to bring up that same helper text in my own function? So if I have up, down, right, left as inputs and give the spreadsheet to someone else, they can see it's up, down, right left and not right, left, up, down .
I think this is an easy one, but i can't figure it out. I have a spreadsheeet with multiple concatenate functions programed in. All of them work. If, however, i try to change the formula inside the concatenate function it freezes and will not calculate the new cell. I have the calculation tab set to automatic in the options tab...
I've got an indexmatch that works great =IF(ISERROR(INDEX(accountstaff,MATCH(B$20,INDEX(accountstaff,,1),0),MATCH($ A55,INDEX(accountstaff,1,),0))),0,INDEX(accountstaff,MATCH(B$20,INDEX(accou ntstaff,,1),0),MATCH($A55,INDEX(accountstaff,1,),0)))
But I need to incorporate IF statement based on varying levels of revenue and think VBA will be less cumbersome, but don't know how to combine else if and index match.
I am trying to accomplish this: IF B$20 (which is a dollar amount is = X, then index, match.... IF B$20 is > Y but < Z, then index, match...
I have 3 calculations I would like to make based on data in the spread sheet and I can't seem to get them to work with data from the two separate columns.
I tried a few of the index match max formulas I found here and could only get them to work with one column of data.
I have the spread sheet attached and the 3 calks I want to do are blank on the bottom.
I am trying to make a multiple nested spreadsheet. I get as far as the first 2 and then can't seem to get the data validation to work. I tried several different formulas (indirect(vlookup)) ones but must be doing something wrong. I have the "lists" on the second sheet to be referenced for the first sheet as drop down menus.
I do building material appraisals and this sheet will be to help me with the inspections if I ever get it done. It is supposed to work like this - Area - Item - then depending on the item - three consecutive dropdown lists with further information.
I am aware of the following topic in the VBA Help file:
"Using Microsoft Excel Worksheet Functions in Visual Basic You can use most Microsoft Excel worksheet functions in your Visual Basic statements. To see a list of the worksheet functions you can use, see List of Worksheet Functions Available to Visual Basic.
Note Some worksheet functions aren’t useful in Visual Basic. For example, the Concatenate function isn’t needed because in Visual Basic you can use the & operator to join multiple text values."
And I'm aware of how to call Excel funcitons from within VBA; e.g., answer = Application.WorksheetFunction.Min(myRange)
However, not only are some Excel functions not useful; the fact is they cannot be used because VBA has a native function that does exactly the same thing and you have to use that native VBA function to achieve your goal. It is these overlapping functions that I am especially interested in. I want to know what I should use directly in VBA and what I need to go to Excel for.
Is it possible to use excel functions with in VB? (such as CONCATENATE, LOGEST etc.) For example, if I have a site no. and data for this site in an array could I append the site number to the data by:
i have a spreadsheet acting as a type of database (our work wont pay for more licences for access!), and it has multiple functions running. well, it has 3000 lines x2 for 2 sheets with about 5-10 full colums of functions. this means the template file i have starts off at about 10mg. with data, this goes upto around 15-20mb. howver, we get a lot of the massive size increases. however, the sizes we get are often upto 150mb! so, i dont know vb at all, and have only learned functions recently. was hoping someone might be able to help out sorting this out? i dont know if i can upload a coppy online, but i can email.
