Creating Multiple Results From Volatile Functions

Aug 26, 2012

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.

View 3 Replies


ADVERTISEMENT

User Defined Functions And Using Application.Volatile To Update Sheet Automatically

Jan 13, 2009

I created a user defined function and using Application.Volatile to update sheet automatically.

But this slowing down my sheet execution alot.

When i remove that it doesnt take much time but no automatic update.

View 9 Replies View Related

Non Volatile Custom Function Acting Volatile

Oct 15, 2007

I am using a linear interpolation UDF that recalculates every time a change is made to any cell in the workbook, whether that cell is pertinent to the calculation or not. Is this correct behavior?

View 9 Replies View Related

Creating Multiple Results From Two Text Columns That Have Repetitive Info

Dec 3, 2012

I'm not sure what I'm trying to do is even possible, but figured this is the place to ask the question. I'm trying to compare a list of companies (column a) to a list of employee email addresses (column b) and post results (column c) that display all associated email addresses that are unique to company name. Since multiple employee email addresses can be associated with numerous company names.

View 2 Replies View Related

Creating Spaces In Formula Results

Jul 31, 2014

I am using a vlookup formula to link 3 pieces of data together. I would like to insert a space between the 3 pieces. Currently formula result is:

item1item2item3

I would like it to view as:

item1 item2 item3

not sure how to alter the formula to create the space....

View 3 Replies View Related

Creating CheckBoxes With VBA Results In Random Order

Jan 7, 2010

I have some VBA (using Excel 2007) that deletes and re-creates a bunch (20-ish) checkboxes in a spreadsheet. Since i'm using the controls-based checkboxes, Excel defaults them to object names of "CheckBox1, CheckBox2, CheckBox3, etc" as I create them the first time around.

Later on, i clear all checkboxes and implement the SAME checkbox-creating code to re-create all or some of the checkboxes, but Excel doesn't number them in order the second time around! I end up with something like "CheckBox2, CheckBox19, CheckBox3, CheckBox12, etc."

What could be causing my code to create things out of order the second time around? Is there a way to reset Excel's Checkbox-numbering iterator?

The code i'm using to create checkboxes is:


Dim x As Long
Dim BoxCell As String

BoxCell = ""
x = 0
'j gets passed into the sub as a "Long" somewhere between the values of 5 and 35

View 9 Replies View Related

Creating A Table From Various Results Of Data Validation

Nov 17, 2006

See the attached:

1/ Need to be able to populate a table with the following data (or as illustrated in ' Sheet 1'.

Company Name, Year, Manufacturing, Non-Manufacturing

This data will need to come from the lists for each of the variables as opposed to from the table, as this will be hidden from the user. Also, more company details will be added over time.

View 4 Replies View Related

Creating Chart To Represent Results On ANOVA Analysis

Oct 12, 2012

I am doing an ANOVA analysis of survey results for a set of products, and would like to present a chart to graphically represent the results.

View 1 Replies View Related

How To Return Multiple Values In Multiple Cells If Argument Is TRUE In IF Functions

Jan 8, 2013

I want my IF function to populate two cells with two values if argument for IF function is found TRUE. Is it possible?

IF(logical_test, [value_if_true], [value_if_false])

value_if_true = return multiple values in mutiple cells (for example put number 8 in cell A2 and number 10 in cell A3 if function is TRUE)

How would I do that? I tried putting IF(logical_test, (A2="8",A3="10"), [value_if_false]) but it is not possbile...

View 3 Replies View Related

Indirect To Non-volatile

Feb 26, 2010

why Microsoft have not made the indirect function non-volatile. In 1997, they changed the index function to non-volatile.

View 9 Replies View Related

Non-volatile Timestamp

May 17, 2002

I have a problem similar to Ozone64's posting of 4/23 that I am trying to generate automatic time/datestamps in a cell whenever a value is entered in an adjacent cell. I am currently using the function =IF(A3"",NOW()) but the problem is when data is entered in subsequent rows the previous timestamp is overridden with the new timestamp. Thus at the conclusion of data entry every row ends up with the same date/timestamp as the final entry.

View 9 Replies View Related

Cell With Day Of Month ONLY (and Volatile)

Jan 12, 2009

I have searched around the forum but I could not find anything related. Like the title says, I need to have a cell that is the current day of the month and changes automatically each day.

View 2 Replies View Related

Replacement For Using Indirect (Volatile)

Oct 30, 2009

What can I use to replace the portion in red because it is volatile?

=IF(A3="N","",RANK(B3,$B$2:INDIRECT("$B$17"),0)+COUNTIF($B$2:B3,B3)-1)

View 6 Replies View Related

Volatile References In Formula

Mar 4, 2010

I have a worksheet that will be referencing data from a 2nd open workbook.
Although the 2nd workbook will contain common worksheet names (eg stats), the file name will be volatile.

Consider the formula in wb ws1 cell A1 used to copy a value from wb2 (39961(28-May-09).xls) ws1 (stats) cell B1 if workbook 2's name was static.

='[39961(28-May-09).xls]Stats'!$B$1

In my case, the reference file will always be different and must be substituted in the forumla above (red portion). How can I adapt my formulae recognize the available workbook. I have the file name available now in cell C4 of WB1.

View 9 Replies View Related

Multiple Functions

Jan 25, 2009

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)

View 9 Replies View Related

Non-Volatile Dynamic Named Ranges

Mar 27, 2008

I am looking for a non volatile alternative to using OFFSET in my named ranges as my models are generally quite bulky and it is beginning to make me quite angry having to build these models in calc manual.

Can anybody provide a non-volatile alternative to this?

=OFFSET(Query!$A$14,,,Height-13,19)

With Height being:

=MATCH(BigText,Query!$B:$B)

View 9 Replies View Related

Return Multiple Results In Multiple Cells Based On Criteria In Yet Another Cell?

Feb 1, 2012

Worksheet 1: In column A I have a people's initials. In coumn B I have text boxes with miscellaneous text. (The same person could have multiple rows within this sheet.)

e.g.
AAA blue
BBB orange
AAA round
CCC smelly
AAA elongated

Worksheet 2 I want to show:
A2 = initials, B2 = first text box associated with that person, C2 = second text box (different row) associated with that person (if applicable), D2 = third text box (different row) associated with that person (if applicable), etc.

e.g.
AAA blue round elongated
BBB orange
CCC smelly

View 3 Replies View Related

Lost On Multiple IF Functions?

Feb 27, 2014

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.

View 13 Replies View Related

Functions To Be Used By Multiple Macros

Jul 9, 2009

If I want to create a function or sub that can be used by any userform or macro that I create for a workbook:

Where would I define that function? Within the Module?
Do I define it as Public?

View 4 Replies View Related

Multiple IF & COUNTA Functions

Sep 25, 2008

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.

View 9 Replies View Related

Non Volatile Replacement For INDIRECT In SUMIFS Formula

Dec 4, 2013

Okay so I'm trying to us the SUMIFS formula to add values on a different sheet based on criteria on that sheet but I want to use a text string on the sheet with the SUMIFS formula to reference the sheet with the data on it.

My original formula was:

=SUMIFS(INDIRECT("'"&A2&"'!K4:K500"),INDIRECT("'"&$A$2&"'!B4:B500"),"=LEFT(AA1,2)",LEFT(INDIRECT("'"&$A$2&"'!C4:C500"),3),"=MID(AA1,7,3)")

However I kept getting an error and excel said that INDIRECT("'"&A2&"'!K4:K500") was volatile, so I tried to replace it with INDEX and my formula was:

=SUMIFS(INDEX(A2:A2,1)&"'!K4:K500",INDEX(A2:A2,1)&"'!B4:B500",LEFT(AA1,2),LEFT(INDEX(A2:A2,1)&"'!C5:C500",3),MID(AA1,7,3))

However INDEX(A2:A2,1)&"'!K4:K500" returns the value "1251062 EP.EL+CB-A10'!K4:K500" which wont calculate properly because of the quotation marks but excel is auto inserting them.

View 3 Replies View Related

Volatile Formula Returning Date To Be Static

Sep 21, 2008

I've posted this question in another site http://en.allexperts.com/q/Excel-105...xExp_72253.htm , but i haven't gotten any answer. I'm attaching an example of the file to show an idea of what i'd like to do, please check first. As you can see, i have a Due Date and Priority columns. The Due Date column has a formula, that when i choose the Priority level, it calculates the Due Date from the current date and auto populates in the cell. The problem is, when i open the file the next day, and insert another Priority level, it updates everything even the previous Due Date entry. This is the formula i have in place for the Due Date:

=(TODAY())+SMALL(IF(WEEKDAY((TODAY())-1+ROW(INDIRECT("1:"&(VLOOKUP((B3),{"Urgent",1;"High",14;"Low",21},2,0))+ ROUND(30*((VLOOKUP((B3),{"Urgent",1;"High",14;"Low",21},2,0))/30),0))))<6,ROW(INDIRECT("1:"&(VLOOKUP((B3),{"Urgent",1;"High",14;"Low",21},2,0))+ROUND(30*((VLOOKUP((B3),{"Urgent",1;"High",14;"Low",21},2,0))/30),0)))),(VLOOKUP((B3),{"Urgent",1;"High",14;"Low",21},2,0)))

Is there a way to make up a vb macro to be able to make the Due Date output static, without affecting the formula?

View 5 Replies View Related

Multiple Criteria Lookup Functions?

Aug 6, 2014

I've attached a sample of the data I'm using.

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.

View 3 Replies View Related

Set A Range For Multiple Functions/Editing Help

Jul 31, 2009

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?

View 9 Replies View Related

Correct Approach? (Multiple MID Functions)

May 29, 2007

* 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.),

View 11 Replies View Related

Using Multiple IF Functions (value To Be A Positive Number)

May 9, 2009

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?

View 2 Replies View Related

Custom Functions Multiple SUMIF

Dec 5, 2008

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):

=MultipleSUMIF("E11:E14, F11:F15", "A1, E1", G11:G14)

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.

View 9 Replies View Related

Multiple Worksheet Change Functions

Jun 23, 2009

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

View 9 Replies View Related

Multiple Criteria For Countif Functions

Nov 11, 2006

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"?

View 2 Replies View Related

#DIV/0! Dividing Multiple Countif Functions Into Value

Jul 19, 2007

I have this formula

= COUNTIF(AT6:AY6,"F")+COUNTIF(AT6:AY6,"P")+COUNTIF(AT6:AY6,"M")+COUNTIF(AT6:AY6,"D")

that returns the number 2 (which is correct). However, if I precede it with

AZ6/COUNTIF(AT6:AY6,"F")+COUNTIF(AT6:AY6,"P")+COUNTIF(AT6:AY6,"M")+COUNTIF(AT6:AY6,"D")

it returns a DIV/0 error, even though AZ6 has a value of 24.

Surely 24/2 would return a value of 12? NB AZ6 cell value is derived from the result of a formula.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved