Free Microsoft Excel 2013 Quick Reference

Multiple X variables and 1 Y variable - Graph???

Hello.

I have a data set of 5 variables and 1500 observations for each variable.

Excel allows me to plot one X axis variable and the remaining four variables on the Y axis.

But, I want the reverse.

Only one variable on the Y axis (price) and 4 different X variables (Supply)

Any tips would be greatly appreciated.

Thank you.

subbu

Related Results

Graph using 2 x-axis and 1 y-axis

Hi All,

How do you create a graph using Excel 2007 that represents 2 x-axis and 1 y-axis. For example, I'd like the data in Column A and Column B to form two plots on the x-axis and the data in Column C to form a plot on the y-axis. Please see attached and thanks again in advance.

-Mario

Can time be used as both the x and y variables in scatter plots?

Can time be used as both the x variable and the y variable when
creating a scatter plot?

I want to determine the correlation between the time one of our company
TV commercials is aired versus the time of day that we receive phone
calls. My objective is to determine which commercials generate the
most calls. I am entering the times as "10:30", "1:15" etc.

Thanks!

-J

Can time be used as both the x and y variables in scatter plots?

Can time be used as both the x variable and the y variable when
creating a scatter plot?

I want to determine the correlation between the time one of our company
TV commercials is aired versus the time of day that we receive phone
calls. My objective is to determine which commercials generate the
most calls. I am entering the times as "10:30", "1:15" etc.

Thanks!

-J

Graphing Y axis vs 2 X axis

Hey,

Can i plot a graph in which i have 2 x axis and 1 y axis, or , can i find the difference between two linear lines?

Multiple x-axes on an x-y chart

Does anybody know of a way of creating a chart with multiple x-axes and a single y-axis in Excel with x-y data?

I have several kinds of data acquired at different depths below the sea surface that i want to display on a chart with a single y-axis (representing depth below sea surface) and multiple x-axes (on different scales). Two x-axes would be OK, 3 better.

I assume I would need a custom control, but haven't found one i can easily use in excel yet.

Not at all clear on use of variables and/or object variables

I jumped on the Excel (VBA) bandwagon some years ago. I have
dozens of books and reference them constantly. In no book or
in my visiting any newsgroup have I been able to clearly make
the distinction between when to engage a regular variable versus
an object variable - particularly as regards the Range object.

Can someone make an attempt to CONTRAST these two elements?

Interpolating an x, y point from known x's and y's

You can use the FORECAST() function to interpolate as well as extrapolate.

See Excel Help
--
Gary's Student

"Steve" wrote:

> Hi
>
> This is probably a simple task and it is my own lack of experience in
> Excel -- I would like to derive a y value for an arbitrary x value from a
> array of known x's and known y's for some unknown function y = f(x) a la:
>
> known x, y
> 0.123, 4.567
> 0.257, 10.4567
> 0.4321, 20.3241
> 0.703, 10.345
> 0.804, 2.345
>
> say I want to derive a y value for x=0.5 from this data set using a linear
> or higher order fit -- is there an appropriate worksheet function for this
> or do I have to resort to programming?
>
> Steve
>
>
>

Multiple results in sheet 1, one (complex) formula in sheet 2

I have a workbook with two sheets. In the first sheet, I have 3 variables and 1 result cell for each column. The 3 variables are input to the second sheet, which does some complex calculations (10 different formulas, with several lookup functions). The output from the second sheet is in one single cell. I want to copy this result to the result cell in the first sheet for each column.

This seems like a simple problem, and might have a simple solution for all I know. But I have no idea of where to start looking. If the formulas in the second sheet where simple, I would of course just copy the formula into the result cell in the first sheet, but they are way to complex to do that.

Using Excel 2007

Regards,
janroger

Multiple x-axes in chart

Is there a way to plot mutiple x-axes with a single y-axis in Excel 2007? I have data which is y-axis vs. temperature. I'd like to have temperature on the bottom of the x-axis and 1/T (1 divided by temperature) on the top. Can this be done?

Interpolating an x, y point from known x's and y's

Hi

This is probably a simple task and it is my own lack of experience in
Excel -- I would like to derive a y value for an arbitrary x value from a
array of known x's and known y's for some unknown function y = f(x) a la:

known x, y
0.123, 4.567
0.257, 10.4567
0.4321, 20.3241
0.703, 10.345
0.804, 2.345

say I want to derive a y value for x=0.5 from this data set using a linear
or higher order fit -- is there an appropriate worksheet function for this
or do I have to resort to programming?

Steve

A charting challenge...

I need to chart the following cumulative probability jugments from seven experts. On the x axis I need the value (0 to 900) and the cumulative probability on the y, with each expert's data appearing as an "s" shaped probability curve. Ideally I'd add multiple x-axes and one y, but I don't think I can do this, or to create the illusion of it.

Any ideas much appreciated.

Graham

Expert 1 Expert 2 Expert 3 Expert 4 Expert 5 Expert 6 Expert 7
0.01 2 4 2 2 2 2 2
0.1 20 40 10 20 20 20 5
0.25 90 120 90 15 90 90 20
0.5 180 240 95 240 180 180 90
0.75 240 300 100 250 240 240 120
0.9 260 320 120 260 240 400 200
0.99 270 400 150 600 270 600 900

User Defined Function Help

I built a user defined Excel 2007 function with 2 mandatory input variables
and 1 optional variable. Is there any way to display which variable I am
adding like the built-in functions do?

Mike D.

Excel 2003 Regression using more than 1 independent variable

How do you run a regression with more than one independent variable through
the menu Tools, Data Analysis, Regression?
On the help section it says you can run regressions with up to 16 variables.
After selecting my range for Y, I try to select more than 1 column for my X
variable and it always gives me an error message. Thanks

Graphing formulas woth continuous variables

Hello

I need to graph a function of SIN(X),

I did it by writing my x-values in one column with small steps between each value and then calculating the sin(x) value in the column next to it and then graphing the two columns.

But this raquires a lot of space on the spreadsheat and it would be convenient to be able to write it on the form

y=sin(x)

having x defined as a continuous variable. and then plotting the graph

Thanks
/Arvid

Multiple X variables

Can I just use a normal Trend function if I want to us several X variables (or predictor variables/factors/whatever) to predict one Y? Can I just do this by highlighting all the columns which have these X variables and including them in my trend function as the X variable?

Or should I use a completely different function/formula?

I've also thought of just averaging together the resultant predicted, or I guess you could say trended, Y value.

I use Excel 2010 btw

Graph Multiple Regression

Hi,

I have 10 independent (Xs) variables and 1 dependent (Y) variable. I'm using the LINEST() array function to pull in all associated statistics (slopes, standard errors, R2, F-Ratio, ESS/RSS, etc.). How do I graph multiple variable regression data? I know how to graph simple linear regression data, but I'm a little confused on this one because of all the dimensions.

Thanks!!
John

Copy Data from Multiple Closed Workbooks and Paste into Another W/Folder Browser

I hope I am posting correctly. Ozgrid has been extremely helpful in the past and now I am stuck again. I am trying to copy data from the same range of cells in multiple closed workbooks and paste into a Summary worksheet. I have had success with this in the past and below is the code I used from Ozgrid in the past. Some of the data is in merged cells and will paste into merged cells. I would like to have a pop up menu ask the user to browse to the folder where the files to copy from are located. I designed the data copy/paste sheets to be the exact same range of cells for simplicity. I can email the actual workbooks if that helps. I'm sure I'm just missing a simple step so any help would be greatly appreciated. Thank you.

My code and tweaks are in RED and the rest is from from Ozgrid several years ago.

Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook

Application.ScreenUpdating = False
Application.EnableEvents = False

On Error Resume Next

Set wbCodeBook = ThisWorkbook

With Application.FileSearch
.NewSearch
'Change path to suit
'Need a user input box that allows user to select directory that the files are located in, then the response pastes that location below'

.FileType = msoFileTypeExcelWorkbooks
'.Filename = " Book*.xls"
'Once the user selects the directory a message box asks user to confirm that they are ready to execute'
'The user execute confirmation OK button and the macro runs'

If .Execute > 0 Then 'Workbooks in folder
For lCount = 1 To .FoundFiles.Count ' Loop through all.
'Open Workbook x and Set a Workbook variable to it
Application.ScreenUpdating = False
Sheets("MR Data Sheet").Visible = True
Sheets("MR Data Sheet").Select

If Sheets("MR Data Sheet").Range("A5:N6") 0 Then
Sheets("MR Data Sheet").Select
Range("A5: N6").Select
Selection.Copy
Rows("5:6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End If

Range("A5:N6").Selec t
Application.CutCopyMode = True
Selection.NumberFormat = "0"
Range("A5:N6").Select
Selection.NumberFormat = "mm/dd/yyyy"

Rows("5:20").Select
Range("A5").Activate
Selection.Copy
Sheets("MR Data Sheet").Select
Range("A5").Select

Sheets("MR Data Sheet").Visible = True

Application.ScreenUpdating = True

wbCodeBook.Worksheets("MR Data Sheet").Range("A5" & wbCodeBook.Worksheets("MR Data Sheet").Range("A20").End(xlUp).Row).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

wbResults.Close SaveChanges:=False

Next lCount

End If
End With

On Error GoTo 0
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Match combined variable and string to cell value

Can anyone tell me why this code is not working? It does not seem to be able to match the value of the cell with my combined variable and string i don't know what i am doing wrong..Nani.xlsx.xlsm

```
VB:

Dim FRCfinal As String
Dim S As String
Dim Dopple As String
Dim Ganger As String

Dim attribs As String
S = "size[Small]"

Dim sh1 As Worksheet

Dim sh2 As Worksheet

Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")

For i = 1 To 5

fcString = sh1.Cells(i, 1).Value
fcString = Replace(fcString, "fc", "FRC")
'MsgBox fcString & "-S"

If sh1.Cells(i, 2).Value  Empty Or sh2.Cells(i, 2).Value = 0 Then

For y = 1 To 5
Dopple = sh2.Cells(y, 1).Value
If fcString = Dopple Then
'MsgBox "OH YEAH?!?!"
sh1.Cells(y, 23).Value = sh2.Cells(i, 23).Value
End If
Next y
MsgBox Trim(sh1.Cells(i, 2).Value)

ElseIf Trim(sh1.Cells(i, 2).Value) = S Then
attribs = Trim(fcString & "-S")
MsgBox "check"

ElseIf Trim(sh1.Cells(i, 2).Value) = "size[Meduim]" Then
attribs = fcString & "-M":
ElseIf Trim(sh1.Cells(i, 2).Value) = "size[Large]" Then
attribs = fcString & "-L":

For j = 1 To 5
MsgBox "OH YEAH Check!?!?!"
If attribs = sh2.Cells(j, 1).Value Then
MsgBox "OH YEAH2!?!?!"
sh1.Cells(y, 23).Value = sh2.Cells(i, 23).Value
End If
Next j
End If

Next i

End Sub

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```

Define Column as a Variable and Use Loop to Increase Column Placement

Hi all,

I'm a bit stuck with my macro. I am trying to read ranges of information placed in rows on a master sheet and then autopopulate them in various spreadsheets in a column format.

On the master sheet, there are two rows of information side by side, one row of information for local data and the
other row for remote data. I am capable of getting the loop to copy one row and then copy the other, and then
move down to another row and start the process all over again. What I can't seem to figure out is how to get the
column placement to increase as the information is pasted.

For example, I have Range B3:E3 copied and then it pastes to Range B31:B35. Then I copy Range F3:I3 and paste it to C31:C35. I am capable of getting my loop to then move to Range B4:E4, but then I don't know how to make that paste
to D31:D35.

What I was attempting to do was define my column as a variable, colplace1 = 2 (since B is the 2nd column) and use
the command "Range("colplace1" & 31 & ":colplace1" & 35).Select" and then at the bottom of my loop use
"colplace1 = colplace1+2" so that I can get the column to move to places to the right. However, I return an error.
So my question is how can I define the location of a column as a variable so that I can then increase its location at
the end of a loop.

My code is pasted to show what I've done so far.

Thank You

```
VB:

Sub Autopopulate()
x = 3 ' setting initial value of x
lastrow = Cells.SpecialCells(xlCellTypeLastCell).Row
Dim i As Integer
For i = 1 To lastrow
If Cells(i, 1).Value = "CP112" Then
colplace1 = 2 ' defining and setting a variable to use as marker for column position
colplace2 = 3
Sheets("Sheet1").Select ' Selects sheet and begins process for local port autopopulation
Range("B" & x & ":E" & x).Select ' Select Range, define row as a variable to allow movement through rows as loop
repeats
Application.CutCopyMode = False
Selection.Copy ' copies selection
Sheets("Sheet2").Select
Range("colplace1" & 31 & ":colplace1" & 35).Select ' Selects range for the proceeding action to take place in
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True ' Transposes copies row selection and pastes it in column form in the defined range
Sheets("Sheet1").Select ' Repeat process for remote Port autopopulation
Range("F" & x & ":I" & x).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("colplace2" & 31 & ":colplace2" & 35).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
x = x + 1 ' increase value of x by 1
colplace1 = colplace1 + 2 ' shift the column placement for local data to the right by 2
colplace2 = colplace2 + 2 ' shift the column placement for remote data to the right by 2
ElseIf Cells(i, 1).Value = "CP212" Then
colplace1 = 2
colplace2 = 3
Sheets("Sheet1").Select
Range("B" & x & ":E" & x).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
Range("colplace1" & 31 & ":colplace1" & 35).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Sheets("Sheet1").Select
Range("F" & x & ":I" & x).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
Range("colplace2" & 31 & ":colplace2" & 35).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
x = x + 1
colplace1 = colplace1 + 2
colplace2 = colplace2 + 2

End If
Next i

End Sub

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```
Thanks again. Any help is much appreciated.

Multiple variables and criteria

I was given the following chart as part of a bigger question:

Amount Loan % Term Interest Rate
0 35.00 1 6.95%
20000 40.00 1.5 6.46%
34500 45.00 2 5.97%
49000 50.00 2.5 5.48%
63500 55.00 3 4.99%
78000 60.00 3.5 4.50%

The organization will give the recipent a percentage of the total loan requested based on its amount as described in the table; however, if the annual income is less than 35ooo and the service years is less than 5 years and the house equity is less than half of the house value, the loan approved will be only 10000.

I need a formula to express all these variables and critera and someone reccomended that i use the "IF" and "AND" functions but im still not sure if i should use them. Any help that you could provide would be greatly appreciated. Thank you for your time. -Dave

Create Charts using VBA and variable reference for Y-axis values

Hello,

I have a spreadsheet and i need to create 93 charts. What i want to do is use a macro. See what i have started below. The problem is, the range A is always going to be my x values, but they y values will come from different columns on each loop. For example the 1st loop is A for x and D for y. The Y will always be 5 columns to the right of the previous Y. I haven't written a loop but i think i can design one once i figure out the column problems.

```
VB:
Xcol = Col 1
Y Col = Col 4
Next Y = Col 4 +5
Next Y = Col 4+5+5

Sub Create_Charts()
'
' Create_Charts Macro
'
'
Range("A:A,D:D").Select
Range("D1").Activate
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Range("Sheet10!\$A:\$A,Sheet10!\$D:\$D")
Range("A:A,I:I").Select
Range("I1").Activate
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Range("Sheet10!\$A:\$A,Sheet10!\$I:\$I")
Range("A:A,N:N").Select
Range("N1").Activate
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Range("Sheet10!\$A:\$A,Sheet10!\$N:\$N")

End Sub

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```
Windows 7 / Safari 535.1

VBA -- SUMPRODUCT with VARIABLES and ORing

I seek your help with the following VBA use of SUMPRODUCT with Variables and OR-ing:

My Goal: Using VBA with SUMPRODUCT and EVALUATE and Variables, write code that will do the following (as an example):

If (Col A='PGV' or Col A='OAK') and (Col B='2008') and (Col C='Real') then add Col D

Worksheet Details:

---A-------B------C------D---
--PGV----2008---Real---400--
--OAK----2008---Real---200--
--BBB-----2008---Fake--100--
--PGV-----2007---Real---600--

The results of the above equation applied to the worksheet would be 600, record 1 and 2 would add, but records 3 and 4 would fail the conditional statement (record 3 because Col A = 'BBB', and record 4 because Col B = '2007').

Working VBA:

The following VBA code works perfectly:

Answer = Evaluate("SUMPRODUCT(--((\$A\$3:\$A\$1000=""PGV"")+(\$A\$3:\$A\$1000=""OAK"")), --(\$B\$3:\$B\$1000=""2008""), --(\$C\$3:\$C\$1000=""Real""), --(\$D\$3:\$D\$1000))")

PROBLEM VBA:

I need to make this code flexible to allow for multiple uses. If I replace the above code with variables, I have the following (this does not work):

Dim Piece1 As String
Dim Piece2 As String
Dim Piece3 As String
Dim Piece4 As String

Piece1 = "--((\$A\$3:\$A\$1000=""PGV"")+(\$A\$3:\$A\$1000=""OAK""))"
Piece2 = "--(\$B\$3:\$B\$1000=""2008"")"
Piece3 = "--(\$C\$3:\$C\$1000=""Real"")"
Piece4 = "--(\$D\$3:\$D\$1000))"

Answer = Evaluate("SUMPRODUCT(" & Piece1 & "," & Piece2 & "," & Piece3 & "," & Piece4 & ")")

HINT #1 TO SOLUTION:

If I remove - from Piece1 - the second conditional statement, the following code does work:

Dim Piece1 As String
Dim Piece2 As String
Dim Piece3 As String
Dim Piece4 As String

Piece1 = "--((\$A\$3:\$A\$1000=""PGV""))"
Piece2 = "--(\$B\$3:\$B\$1000=""2008"")"
Piece3 = "--(\$C\$3:\$C\$1000=""Real"")"
Piece4 = "--(\$D\$3:\$D\$1000))"

Answer = Evaluate("SUMPRODUCT(" & Piece1 & "," & Piece2 & "," & Piece3 & "," & Piece4 & ")")

HINT #2 TO SOLUTION:

If I remove - from Piece1 - the second conditional statement, but keep the "+" operator (as an OR) the following code does work:

Dim Piece1 As String
Dim Piece2 As String
Dim Piece3 As String
Dim Piece4 As String

Piece1 = "--((\$A\$3:\$A\$1000=""PGV"")+1)"
Piece2 = "--(\$B\$3:\$B\$1000=""2008"")"
Piece3 = "--(\$C\$3:\$C\$1000=""Real"")"
Piece4 = "--(\$D\$3:\$D\$1000))"

Answer = Evaluate("SUMPRODUCT(" & Piece1 & "," & Piece2 & "," & Piece3 & "," & Piece4 & ")")

IN SUMMARY:

I'm trying to use SUMPRODUCT with EVALUATE with VARIABLES and ORing.

I'm able to write working code with all of these pieces, except that the ORing statement is making for sleepless nights.

Can you help me with this?

Thank you

Multiple X and Y Axis labels

Hi again -- I'm trying to create a chart with multiple X and Y axis
labels. These labels are not associated with different data series,
but are associated with the same data -- just different labels on the
axes.

Like this which I created manually. Is there another way to do this?
'[image:
http://aycu35.webshots.com/image/3674/1140386703162621086_rs.jpg]'

I'm familiar with Peltier's methods and Office Expander's multiple Y
axis tool http://www.officeexpander.com/multy_y/index.html but don't
think those can do what I'm trying on both X and Y.

Any ideas?

Scott

--
sdubose99
------------------------------------------------------------------------
sdubose99's Profile: http://www.excelforum.com/member.php...o&userid=27538

Variables with times between Midnight and 1:30am

I have an interesting problem, I am writing a function where there are
a few hard coded times for the function to use. I am however having
problems applying a variable value to 1:00 AM. Here is my code for the
variable application

ThirdStart = #1:00:00 AM#

However when I would expect to see the variable (defined as a double)
contain "0.041666666666", the variable contains "4.16666666666667E-02".
If I set the hard coded value to:

ThirdStart = #1:30:00 AM#

The variable contains the expected result of "0.0625".

I have also checked my hardcoded midnight value:

Midnight = #12:00:00 AM#

which also contains the expected value of "0".

This only happens on times between #12:00:01 AM# and #1:29:59 AM#.

Anyone have an answer to this problem? I am programming in Excel 2003
for reference.