Solution/altenative To Data Table Using >2 Variables

Feb 9, 2010

Is there a way to test out a formula using Excel that has more than 2 variables? (If only 2 variables, I would use a simple data table). The formula I am testing has 50X50X20 variables (and as a subset, each variable has 2500 lines of assumptions!). I don't need to see all the results, only the result that produces the highest result. For simplicity (if someone has an answer) let's assume 3 variables are A1, A2 and A3. Formula is in A4 and formula is A1+A2+A3. Assuming A1 can be 1 to 5 and A2 same , A3 same, the long hand calc is for me to run a data table using variables in A1 and A2 the fixing A3 (as 1)..note maximum result....then run data table again using variables in A1 and A2 and changing fixed value in A3 to the number 2...and so on and so on....in this simple example I know the max result would be 15, but much more difficult in my spreadsheet test.

View 9 Replies


ADVERTISEMENT

Two Variable Data Table - Both Variables In Rows?

Nov 29, 2013

I'm interested in creating a two variable data table with the two variables in the top two rows.

I'm aware that two variable data tables are usually done in an array with variables in the top row and leftmost column. However, as there are multiple outputs to the variations that I want to make it makes sense to have the variations in the two top rows.

View 8 Replies View Related

Code To Generate Data Table Based On Variables

Aug 26, 2008

I was trying to use the VBA code to generate a data table:

Private Sub createDataTable(WS As Worksheet, initialRow As Integer, numCol As Integer, numRows As Integer)
Dim initialCell As Range 'specify the upper left cell
Dim RefCell As Range 'the reference cell on the caculator sheet

'activate source sheet
ActiveWorkbook.Sheets("Calculator").Activate
Set RefCell = ActiveSheet.Cells(2, 3)
WS.Activate
Set initialCell = ActiveSheet.Cells(initialRow, 1)
initialCell.offset(numRows, numCol).Select
Selection.Table ColumnInput:=RefCell
'the calculation should be automatic, if not, then calculate
WS.Calculate
End Sub

and it kept giving me an input celll not valid error on this line: Selection.Table ColumnInput:=RefCell the thing is, I have defined RefCell as a range object, so it should be OK rite?

View 4 Replies View Related

Copy Input Data To Output Table Based On Variables

Mar 20, 2008

For what I'd like to do, the attached workbook actually represents 3 worksheet. one input sheet and two output sheets (output1 and output2)

I plan to use command button " CommandButton1" on input sheet to start the calculations

Here's what I want to do after pressing the " CommandButton1" :

1. start with case 1 and find the value of Input A to Input J from input table using Vlookup and paste those values to respective palceholder for inputs in blue colour area above the input table for each input.

2.for empty cell in the input table paste 0 (zero)

3. after this will give results for case 1 inputs on sheets "output1" and "output2" on basis of formulas used for each output

4.then copy the results for case 1 inputs from sheet "output1" and "output2" and paste them on "input sheet " in Output table (its below Input Table) next to case1

5.Do this for all cases (in actual workbook there will be more than 100 cases)

View 9 Replies View Related

Locating Important Columns In A Table Of Raw Data - Setting Variables Using A Loop

Feb 22, 2012

I need to be able to locate some important columns in a table of raw data (the column locations are not fixed).

I would like to identify the locations (based on the heading values in Row 1) and store them as Public variables. The Match function works fine for this, however I'd like to make a simple loop to set these variables (opposed to repeating the function for each).

In the example below, I can't figure out how to reference "List1(Count)" as the name of the variable I'm trying to set.

Code:

Public Field1 As Long, Field2 As Long, Field3 As Long 'The column numbers will be stored here
Sub FindFields()
Dim List1(3), List2(3)
Dim Count As Long
'Public variables (declared above)

[Code] ......

So after running FindFields(), the Test1() macro should give "1 - 2 - 3" (for example) as the locations of the fields in Sheet1. But currently this doesn't work.

View 2 Replies View Related

VBA Solution To Compare/replace Data Between Columns

Jul 4, 2009

I have two columns of adjacent data Call them A & B (although this could differ)

If the data in the second colum is not equal to, blank cell or contains the words "CAT", "DOG" or "ELEPHANT" then the content of the cell in the second column should REPLACE the content of the adjacent cell in column A (in this example) leaving B blank, otherwise column B remains unchanged.

View 9 Replies View Related

Correlation From Pivot Table On 3 Variables?

Dec 6, 2013

see attached pivot table... I believe that there is a statistical relationship between column B & C (together, i.e. their combined effect) upon column D. i.e. (B & C) - have some relationship to effect D.

(1) What formula would I use to measure this correlation?

View 3 Replies View Related

Vlookup, 9 Different Variables, 9 Different Table Array

Jun 11, 2009

I need to be able to do a look up in 9 different table arrays using 9 different ranges.

I have attached a worksheet to help explain. My problem is that I can't have 9 IF statements in one formula.

I am not sure how to make this smaller or work.

View 14 Replies View Related

Locate Value From Table, From Two Predefined Variables?

Oct 12, 2009

Struggling a bit on this one! I have tried the index function, but doesn’t quite work how I need it. Consider the attachment as an example:

I have three cells, 2 of which are drop down lists, and the third is simply the result from the input variables. The yellow and green section contains the info for my drop down lists, and the orange contains the data range. Trouble is ‘Index’ is looking at the contents of my cells, and not the corresponding cell address. So lets say I have cells F5 and A8, the answer I would be looking for is 23.7, but because the contents of my input cells are 185, and 5, its looking for column 185 and row 5, when I want it to look for the column/row that relates to these values, i.e. column 6, row 8.

View 5 Replies View Related

Building A Lookup Table With 3 Variables/3 Answers

Mar 25, 2009

I'm trying to figure out how best to build a table to do:

IF A1="yes", "no", "na" and
IF B1="yes", "no","na" and
IF C1="yes","no","na" then
"AT" or "IC" or "VE"

So, I need a table that can look up responses to three questions, then lookup the corresponding answer (AT, IC or VE). Some of the combinations of anwers will yield the same anwer (i.e: yes, yes, yes=AT and yes,yes,no=also AT). I'm trying to come up with the most probable answer by evaluating these three questions; Thus; 10 combinations will yield an answer of "AT", 10 combo will yield "IC" and 7 combo will yield "VE". I've done this with 2 variables, but can't seem to get it to work with three...

View 4 Replies View Related

Create Table Of Dimensions With Multiples Of Variables

Oct 11, 2007

I am trying to create a spreadsheet containing a table of dimensions that are multiples of up to six loadable variables. I work for a stainless steel tank manufacturer. The body of tanks are formed from coil stock of varying widths. Typically these might be 1200mm, 1500mm, 2000mm and possibly 1800mm as well as the imperial equivalents. This could mean up to eight variables but in reality all are unlikely at any one given time.

We are told the volume that the tank is required to contain and a target preferred diameter. The theoretical height is therefore derived from this information. But now comes the tricky bit. It is desirable, to minimise cost of welding by minimising the number of strakes or wrappers. Can anyone give me a formula or whatever that can be used to do this and hopefully generate a table of close heights up to say 40 meters. Thus allowing an operator to select from say five presented options.

I have a table in a spreadsheet that the three principal widths of 1200mm 1500mm and 2000mm generate. It was created for me a while ago by a gentleman working at the Amsterdam Museum that I’m happy to send for perusal. The trouble is he never told me how he did it and no matter how hard I try, I cannot discover the method or formula. The first column gives the total combined height, the second the number of 1200mm wrappers, the third the number of 1500mm and the last the number of 2000mm. It is that simple

View 4 Replies View Related

Copy Table Values To Array Variables And Use Them In Another Sheet?

Nov 20, 2012

I have a query about using Array Variables in excel VBA. I have a set of lookup tables and a main data table. The data table will be downloaded everyday. I need to replace the ID's in the main table with actual data from the Lookup tables.

e.g.
Main Data Table
Color Operator
005--325 005
004--326 004
003--327
001--328
002--322

Lookup Color
001 - Red
002 - Blue
003 - Green
004 - Violet
005 - White

Lookup Operator Table
323 - Operator 1
324 - Operator 2
325 - Operator 3
326 - Operator 4
327 - Operator 5
328 - Operator 6

I have a huge amount of data in the main table. So I can't use a lookup formula for automation. Rather I would like to use VBA to create array variables, scan and copy the lookup data into the array and replace the ID's in the main table.

Only that I am unable to achieve this using arrays. I am very basic in executing code related to Arrays.

View 2 Replies View Related

Having A Solution Returned As A Value

Dec 9, 2009

Is it possible to have a formula that will return the answer as the value.

For example if I have 15.75 in A1 and I use the formula =ROUND(A1,0), the solution is 16, but when you click on the cell it shows the formula and not the value.

I know about Paste Special, but was wondering if anything could be added to the formula to provide the solution.

Would this require a macro?

View 5 Replies View Related

SumProduct Fast Solution

Jan 30, 2010

my questions below are more oriented to hows & whys, as opposed to asking for a fast solution to a pressing problem.

I'm just reviewing the basics of SumProduct to be sure that I understand what I'm really doing when I deploy the function. So, I have the following formula:

View 3 Replies View Related

Unique Job #'s By Date VBA Solution

Jan 20, 2009

I'm looking for a VBA solution that will return a list of unqiue job numbers in B by date, the below is some sample data in Sheet 1

Sheet 2 is the result I'm looking for and usually there will be muliple job mumbers for the same date and in this case I'm fine to leave it as a blank cell in sheet 2 for that day.

Unable to use advanved filtering as it's a data table thats changing all the time. It's verly likely that the same job numbers will be in multiple days....

View 11 Replies View Related

VBA Solution - Inserting Machine Name

Apr 23, 2009

I'm looking for a VBA solution that will look at the machine name above in blue and insert in in column E

Machine 2's expected results have been filled in....

View 9 Replies View Related

Provide Both Formula And Vba Solution.

Jan 24, 2010

I have an excel sheet which has been exported from an 'Employee Attendance Software',

http://www.flickr.com/photos/34247990@N06/4299288997/

In the above screenshot, The employee name highlighted in Yellow should feauture in column A in the cells highlighted in Yellow.

Similarly, there are some other employees in the list for whom I have to populate the data in a similar fashion

http://www.flickr.com/photos/34247990@N06/4299288959/

View 9 Replies View Related

Use Lookup Formula For Solution

Feb 7, 2007

I am trying to solve a problem with = LOOKUP. Here's the data. A1 can have a value between 1-100.

IF A1 IS <80.00, NO MULTIPLICATION IS NEEDED
IF AI IS 80.01 TO 85.00, MULTIPLY A2 BY 0.37%
IF AI IS 85.01 TO 90.00, MULTIPLY A2 BY 0.52%
IF AI IS 95.01 TO 95.00, MULTIPLY A2 BY 0.87%
IF AI IS 95.01 TO 100.00, MULTIPLY A2 BY 1.07%

Is there a =LOOKUP formula that can be used here?

View 2 Replies View Related

IF Input And Color Identify Solution

Oct 9, 2008

I have the new version of excel and I can't figure the correct formula to do the following.

Attached is my example.

A1 is available Inventory qty. C1:H1 are forcast qty's. I would like to have the available forcast qty. shown in black and the unavailable forcast qty. shown in red based on available inventory shown in A1

Example
So C1,D1 and E1 would be Black and F1 and G1 would be red. As inventory changes so would the colored numbers on the forcast.

View 14 Replies View Related

Hlookup With Dates Solution Needed

Oct 3, 2008

I am using HLOOKUP to show a date depending on the month shown in B10.

If B10 is January then the dates throughout January ie. 01/01/08 to 31/01/08 are shown in the cell range of F13 to F43. There is no problem with this unless there are 30 or 29 days etc in a month. So if November is in B10, in F43 I have the value of 00/01/1900. Can I modify my formula so if there is no data in the lookup table to return a blank value ie. "".

Here is my formula:

=HLOOKUP($B$10,DATES!$B$1:$M$32,2,FALSE)
I obviously change the 2 to 3, 3 to 4 as I copy the formula down the table.

View 9 Replies View Related

Returning A Solution, Of A Formula, To A Different Cell

Mar 3, 2010

I would like to have the answer to a formula, say in cell G6, to show up in say G5. I notice in my college coarses, doing our homework through excel, the solutions are always in a cell around the entry cell.

View 9 Replies View Related

Multi-dimensional Array Solution

Jan 12, 2007

I am currently using a macro which highlights keywords and associated page views from an array in my website stats spreadsheet. Now I need to take it to the next level:

I want to use a multi-dimensional array to pull out any keywords that are duplicated, and display them at the top of my spreadsheet with the totaled page views next to them in a different color (red).

Here is my existing Keyword highlighting
Sub Highlight_Keywords()

Dim vntWords As Variant
Dim lngIndex As Long
Dim rngFind As Range
Dim strFirstAddress As String
Dim lngPos As Long

View 9 Replies View Related

Text Overwrites Blank Cells! Solution?

Jan 28, 2009

My problem is that long text for the cell overlaps any blank cells to the right. I have more then 20.000 rows, so I would not like to put a space character in all the blank cells. Is there a way of formatting which will stop this? I don't want these texts to overlap the blank cells!

View 3 Replies View Related

Conditional Formatting Is Not Resulting Of Desire Solution

Dec 29, 2009

I have the attached table. I need to format with conditional formatting with the following criteria:

1) If >= 80% , then flag as green
2) If >=1% but <80%, then flag as yellow
3) If 0%, then flag as red.

I did the formatting in conditional formatting but the it did not results in the desired solution.

View 4 Replies View Related

Extending Previous 'conditional Statements Solution'

Jan 30, 2010

use of SUMPRODUCT and what I believe is called a double unary operator (--). Here's the original post with Paul's solution:

http://www.excelforum.com/excel-gene...-new-post.html

I've since discovered that a previously unknown column of data (I'm transcribing data from paper documents) needs to be included in the calculations. Specifically, the new column of data (shown in yellow in the attached example) has to be taken into consideration when counting product quantities. An explanatory pseudo code statement, also appearing in yellow in the example sheet, describes.

View 2 Replies View Related

Desperately Seeking Solution - Payroll Tax Withholding

Feb 1, 2007

I need to determine federal tax withholding from gross pay.

If gross amount (for S-0 = single no dependents) is
Over.................But NOT Over............Then Subtract.........And multiply
$0.00...............$195.00.....................$5 1.......................10%
$195.00............$645.00.....................$99 .......................15%
$645.00............$1,482.00..................$351 ......................20%
$1,482.00..........$3,131.00..................$447 ......................25%
$3,131.00..........$6,763.00..................$849 .....................30%

If gross is $400 then what are the total taxes taken out? Could someone please provide a formula?

I tried this one: =IF(M10

View 9 Replies View Related

Finding Effective Solution For Updating Links

Apr 4, 2006

I've got a workbook in which there're many formulas linking to other workbooks. Coz these other workbooks are frequently updated, so the calculation (update) in my workbook is painfully slow and time consuming. I wanna improve this, maybe by VBA or other way instead of using link like this. Anyone can give me some hints or way how to improve this condition???

View 7 Replies View Related

Unzip Code - Works Without Variables, Breaks With Variables...

Feb 5, 2009

Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...

I have the

View 2 Replies View Related

Parsing A Vba's Variables Data

Jun 13, 2008

one="dog"
two="cat"
three="bug"
four="bird"

I have a 5th variable with a sentence in it for example

Sen=”The bug and bird are in the park”

What I need to know is which of the variables content appears in the variable Sen first and what position it is. In this case it would be variable three position 5.

View 9 Replies View Related

Use Variables To Upload Data

Feb 28, 2008

I want to create a vba code in excel to select rows and upload into an external application called siebel. i can take care of the later part but I am not sure how to select the records and do the upload. In my case I would like to select records from the 17th row until the EOF..

Also pls suggest which is a better option

1) Selecting first row, upload it and then select the next row and upload (OR)

2) Select all the records at a time and dump the data..

View 5 Replies View Related







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