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

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

- Graph using 2 x-axis and 1 y-axis
- Can time be used as both the x and y variables in scatter plots?
- Can time be used as both the x and y variables in scatter plots?
- Graphing Y axis vs 2 X axis
- Multiple x-axes on an x-y chart
- Not at all clear on use of variables and/or object variables
- Interpolating an x, y point from known x's and y's
- Multiple results in sheet 1, one (complex) formula in sheet 2
- Multiple x-axes in chart
- Interpolating an x, y point from known x's and y's
- A charting challenge...
- User Defined Function Help
- Excel 2003 Regression using more than 1 independent variable
- Graphing formulas woth continuous variables
- Multiple X variables
- Graph Multiple Regression
- Copy Data from Multiple Closed Workbooks and Paste into Another W/Folder Browser
- Match combined variable and string to cell value
- Define Column as a Variable and Use Loop to Increase Column Placement
- Multiple variables and criteria
- Create Charts using VBA and variable reference for Y-axis values
- VBA -- SUMPRODUCT with VARIABLES and ORing
- Multiple X and Y Axis labels
- Variables with times between Midnight and 1:30am

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

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

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

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.

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?

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

>

>

>

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

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

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

and 1 optional variable. Is there any way to display which variable I am

adding like the built-in functions do?

Mike D.

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

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

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

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

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

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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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:Thanks again. Any help is much appreciated.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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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

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:Windows 7 / Safari 535.1Xcol = 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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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

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

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!