Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

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


Post your answer or comment

comments powered by Disqus
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 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 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

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?

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.

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?

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?
>
> Many thanks in advance.
> Steve
>
>
>

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

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?

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?

Many thanks in advance.
Steve

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

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.

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

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

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

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

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.DisplayAlerts = 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'

.LookIn = "C:UsersShaneDocumentsESPMorning ReportsMorning Reports"
.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
Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
'DO YOUR CODE HERE
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.DisplayAlerts = True
Application.EnableEvents = True
End Sub

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


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

-Adam Krug


	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.

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

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 
    ActiveSheet.Shapes.AddChart.Select 
    ActiveChart.ChartType = xlXYScatter 
    ActiveChart.SetSourceData Source:=Range("Sheet10!$A:$A,Sheet10!$D:$D") 
    Range("A:A,I:I").Select 
    Range("I1").Activate 
    ActiveSheet.Shapes.AddChart.Select 
    ActiveChart.ChartType = xlXYScatter 
    ActiveChart.SetSourceData Source:=Range("Sheet10!$A:$A,Sheet10!$I:$I") 
    Range("A:A,N:N").Select 
    Range("N1").Activate 
    ActiveSheet.Shapes.AddChart.Select 
    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

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

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]'
(http://allyoucanupload.webshots.com/...86703162621086)

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?

Thanks in advance,
Scott

--
sdubose99
------------------------------------------------------------------------
sdubose99's Profile: http://www.excelforum.com/member.php...o&userid=27538
View this thread: http://www.excelforum.com/showthread...hreadid=568464

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.

Thanks in advance!


No luck finding an answer? You could always try Google.