excel help

Bits · 2012-02-20 16:12

I am trying my best to plot a function in excel. Can someone give me an example?

I want to plot these data using a transverse equation. Most of the variables will be static yet I want to be able to view the changes.

Starting at row 1 column A

[TABLE="width: 128"]
  [TR]
  [TD="width: 64"]Vin[/TD]
  [TD="width: 64, align: right"]2.5[/TD]
 [/TR]
 [TR]
  [TD]Rf[/TD]
  [TD="align: right"]15[/TD]
 [/TR]
 [TR]
  [TD]Rc[/TD]
  [TD="align: right"]15[/TD]
 [/TR]
 [TR]
  [TD]Vref[/TD]
  [TD="align: right"]2.5[/TD]
 [/TR]
 [TR]
  [TD]Vout[/TD]
  [TD="align: right"]2.5[/TD]
[/TR]
[/TABLE]

With the equation

 =(-B1 * (B2/B3) )+(B4 *((B2+B3)/B3))

Martin_H · 2012-02-20 16:22

I am not an Excel power user, and frankly have no interest in VBA. So I do all my data crunching in another language (C++,C#, or Java) and output a comma separated values (csv) file. Excel can load these and then I use the Excel chart functions for, visualization. The chart functions display columns versus the rows, so they work best when the column values are a function of the rows.

jim N8RHQ · 2012-02-20 20:24

Something like this, or do I not understand your question?

Bits · 2012-02-21 06:39

Yes, except I want to plot these data. Can B6 be plotted in some fashion in that when I manipulate some of the variables the plot would reflect it?

Duane Degn · 2012-02-21 07:27

I think (I'm not an Excel expert) Excel's graphing features usually just use two variables. You'd want one variable dependent on another and use a X,Y scatter plot.

Arrange your data on the spreadsheet so the two data sets are side by side (x and y), select the data, click the graphing wizard icon and use a X,U scatter plot.

You could have any of the values in your equation be the dependent ("X") value but you'll probably need to use a different graph for each dependent value. The other values in your equation would be treated as constants.

Ron Czapala · 2012-02-21 07:47

@bits,

EDIT: Here is spreadsheet with some VBA code to vary the Rf Vin value and the chart will reflect the new value.

attachment.php?attachmentid=89760

Option Explicit
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Private Sub CommandButton1_Click()
Dim i As Single
Dim temp As Single
Range("b1").Select
ActiveCell.Interior.Color = RGB(204, 153, 204)
temp = Range("b1").Value
For i = 0.5 To 3 Step 0.25
  Range("b1").Value = i
  ActiveSheet.Calculate
  Sleep 500
Next
ActiveCell.EntireRow.Interior.ColorIndex = xlColorIndexNone
MsgBox "Done"
Range("b1").Value = temp  'restore original value
ActiveSheet.Calculate
End Sub

Ron Czapala · 2012-02-21 08:17

Without knowing what the formula actually represents, I changed the XLS in the previous post to vary Vin (cell B1) rather than Rf (cell B2), since varying Rf had no effect on the result.

jim N8RHQ · 2012-02-21 11:28

I've expanded across a few more fields, you can vary any value, the result will update, and so will the graph

Duane Degn · 2012-02-21 16:04

Either I'm way off in thinking what you want or the others posting here are.

You can only make a two dimensional plot with Excel (as far as I know). You can have multiple data sets ploted on the same graph but I don't think you can graph your function with multiple changing variables.

Here's my attempt at plotting some data.

attachment.php?attachmentid=89771&d=1329869051

attachment.php?attachmentid=89771&d=1329869051

The above is the graph of the result based on two different changing variables (but only one changing variable per graph).

I used one graph with points and the other with just a line for some variety. Many of the data sets aren't very interesting since they just produce a horizontal line.

Ron Czapala · 2012-02-21 17:54

@Duane,

I'm not sure what she is looking for either, but this version has two scrollbars to control Vin and Rc.

More scrollbars could be added...

attachment.php?attachmentid=89777

Duane Degn · 2012-02-21 19:46

Ron Czapala wrote: »

@Duane,

I'm not sure what she is looking for either, but this version has two scrollbars to control Vin and Rc.

That's very cool. I don't think I've seen scroll bars used like that in Excel before.

I've often wanted to graph equations that use more than one dependent variable. It's seems very awkward to have to make a different graph for each variable being changed.

Bits · 2012-02-22 07:09

Sorry everyone I did not mention what I am trying to do. The equation is for selecting an op-amp that is why I wanted to make things dynamic.

Ron Czapala,
Wow that is a cool feature using the scroll bars like that.

Duane Degn,
You have the correct plot.

If I could use Ron's scroll feature and Duane's plot this might work.

Thanks guys.

Ron Czapala · 2012-02-22 07:20

Bits wrote: »

Sorry everyone I did not mention what I am trying to do. The equation is for selecting an op-amp that is why I wanted to make things dynamic.

Ron Czapala,
Wow that is a cool feature using the scroll bars like that.

Duane Degn,
You have the correct plot.

If I could use Ron's scroll feature and Duane's plot this might work.

Thanks guys.

Bits,
I'm glad you found it useful. I really don't use Excel a lot so it was an interesting challenge - the Excel Object Model is complex.

I tweaked the VBA code in post #10 to handle clicking the arrows on the scroll bars in addition to the slider,
and if you change the values in a cell, the scollbar reflects the new value.

The scrollbars MAX/MIN properties should be set appropriately - I multiplied/divided them by 10 in the code so values could be adjusted to one decimal position.

Here is the code:

Option Explicit
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Private Sub CommandButton1_Click()
Dim i As Single
Dim temp As Single
Range("b1").Select
ActiveCell.Interior.Color = RGB(204, 153, 204)
temp = Range("b1").Value
For i = 0.5 To 3 Step 0.25
  Range("b1").Value = i
  ActiveSheet.Calculate
  Sleep 500
Next
ActiveCell.EntireRow.Interior.ColorIndex = xlColorIndexNone
MsgBox "Done"
Range("b1").Value = temp  'restore original value
ActiveSheet.Calculate
End Sub

Private Sub ScrollBar1_Change()
  Range("b1") = ScrollBar1.Value / 10
End Sub

Private Sub ScrollBar1_Scroll()
  Range("b1") = ScrollBar1.Value / 10
End Sub

Private Sub ScrollBar2_Change()
  Range("b3") = ScrollBar2.Value / 10
End Sub

Private Sub ScrollBar2_Scroll()
  Range("b3") = ScrollBar2.Value / 10
End Sub

Private Sub Worksheet_Activate()
  ScrollBar1.Value = Range("b1") * 10
  ScrollBar2.Value = Range("b3") * 10
  MsgBox ("help")
End Sub

Private Sub Worksheet_Calculate()
'  MsgBox ("calc")
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
  On Error Resume Next
'  MsgBox (Target.Address)
  Select Case Target.Address
    Case "$B$1"
      ScrollBar1.Value = Range("b1") * 10
    Case "$B$3"
      ScrollBar2.Value = Range("b3") * 10
  End Select
End Sub

Ron Czapala · 2012-02-22 18:51

I found some VBA code that dynamically creates data in cells and then creates a chart using that data.

I modified the code to create the same data and chart three different ways.

In one the cell names are hardcoded (e,g. "A1").

The second uses a for..next loop and uses the format "Range(Cells(x,y),Cells(x,y)).Select"

The third uses a for..next loop but builds the cell name as a string and uses the format "Range(varname).Select"

You could write code to vary your Vin, Rf, etc values and calculate the Vout value and put them in a range of cells, then dynamically create the appropriate type of chart.

Option Explicit
Private Sub CommandButton1_Click()
'  createColumnChart3
'  createColumnChart3
  createColumnChart3
  MsgBox ("Done")
End Sub

Sub createColumnChart()
  Dim myChart As Chart
  
  Range("A1").Select
  ActiveCell.Value = "Chart Data 1"
  Range("A2").Select
  ActiveCell.Value = "1"
  Range("A3").Select
  ActiveCell.Value = "2"
  Range("A4").Select
  ActiveCell.Value = "3"
  Range("A5").Select
  ActiveCell.Value = "4"
  Range("B1").Select
  ActiveCell.Value = "Chart Data 2"
  Range("B2").Select
  ActiveCell.Value = "5"
  Range("B3").Select
  ActiveCell.Value = "6"
  Range("B4").Select
  ActiveCell.Value = "7"
  Range("B5").Select
  ActiveCell.Value = "8"
  Range("B6").Select
  
  Set myChart = Charts.Add
  With myChart
    .Name = "Chart Data"
    .ChartType = xlColumnClustered
    .SetSourceData Source:=Sheets("Sheet1").Range("A1:B5"), PlotBy:=xlRows
    .HasTitle = True
    .ChartTitle.Text = "=Sheet1!R1C2"
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Chart Data 1"
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Chart Data 2"
  End With
End Sub

Sub createColumnChart2()
  Dim myChart As Chart
  Dim i As Integer
  
  Range("A1").Select
  ActiveCell.Value = "Chart Data 1"
  For i = 2 To 5
    Range(Cells(i, 1), Cells(i, 1)).Select
    ActiveCell.Value = i - 1
  Next
  
  Range("B1").Select
  ActiveCell.Value = "Chart Data 2"
  For i = 2 To 5
    Range(Cells(i, 2), Cells(i, 2)).Select
    ActiveCell.Value = i + 3
  Next
  
  Range("B6").Select
  
  Set myChart = Charts.Add
  With myChart
    .Name = "Chart Data"
    .ChartType = xlColumnClustered
    .SetSourceData Source:=Sheets("Sheet1").Range("A1:B5"), PlotBy:=xlRows
    .HasTitle = True
    .ChartTitle.Text = "=Sheet1!R1C2"
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Chart Data 1"
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Chart Data 2"
  End With
End Sub

Sub createColumnChart3()
  Dim myChart As Chart
  Dim i As Integer
  Dim cellname As String
    
  Range("A1").Select
  ActiveCell.Value = "Chart Data 1"
  For i = 2 To 5
    cellname = "A" & CStr(i)
    Range(cellname).Select
    ActiveCell.Value = i - 1
  Next
  
  Range("B1").Select
  ActiveCell.Value = "Chart Data 2"
  For i = 2 To 5
    cellname = "B" & CStr(i)
    Range(cellname).Select
    ActiveCell.Value = i + 3
  Next
  
  Range("B6").Select
  
  Set myChart = Charts.Add
  With myChart
    .Name = "Chart Data"
    .ChartType = xlColumnClustered
    .SetSourceData Source:=Sheets("Sheet1").Range("A1:B5"), PlotBy:=xlRows
    .HasTitle = True
    .ChartTitle.Text = "=Sheet1!R1C2"
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Chart Data 1"
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Chart Data 2"
  End With
End Sub

Ron Czapala · 2012-02-22 19:33

BTW - here is a link to the Microsoft Excel Visual Basic Reference http://msdn.microsoft.com/en-us/library/aa294264(v=office.10).aspx

Ron Czapala · 2012-02-23 14:52

Don't want to beat a dead horse, but this version uses VBA code to vary Vin from 1.5 to 5.5 and calculates Vout = (-Vin * (Rf / Rc)) + (Vref * ((Rf + Rc) / Rc)).

Constants: Rf = 15 Rc=15 & Vref=2.5

The Vin and Vout values are placed in the A & B columns and the chart is created.

(All data and chart are created by program code).

attachment.php?attachmentid=89951

Option ExplicitPrivate Sub CommandButton1_Click()
'  createColumnChart3
'  createColumnChart3
  createColumnChart3
  MsgBox ("Done")
End Sub

Sub createColumnChart3()
  Dim myChart As Chart
  Dim i As Integer
  Dim cellname As String
  Dim cRange As String
  Dim Vin As Single
  Dim Rf As Single
  Dim Rc As Single
  Dim Vref As Single
  Dim Vout As Single

  ClearChart

  Rf = 15
  Rc = 15
  Vref = 2.5
    
  Range("B1").Select
  ActiveCell.Value = "Vout"
  Range("A1").Select
  ActiveCell.Value = "Vin"
  Vin = 1.5
  i = 1
  Do
    i = i + 1
    cellname = "A" & CStr(i)
    Range(cellname).Select
    ActiveCell.Value = Vin
    Vout = (-Vin * (Rf / Rc)) + (Vref * ((Rf + Rc) / Rc))
    cellname = "B" & CStr(i)
    Range(cellname).Select
    ActiveCell.Value = Vout
    Vin = Vin + 0.1
  Loop Until Vin >= 5.5
  
  cRange = "A2:B" & CStr(i)
  Range("A1").Select
 
  Set myChart = Charts.Add
  With myChart
    .Name = "Chart Data"
    .ChartType = xlXYScatter
    .SetSourceData Source:=Sheets("Sheet1").Range(cRange), PlotBy:=xlColumns
    .HasTitle = True
    .ChartTitle.Text = "Vout based on Vin from 1.5 to 5.5"
    .HasLegend = False
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Vout = (-Vin * (Rf / Rc)) + (Vref * ((Rf + Rc) / Rc))"
'    .Axes(xlValue, xlPrimary).HasTitle = True
'    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Chart Data 2"
  End With
End Sub
Private Sub CommandButton2_Click()
  ClearChart
End Sub
Sub ClearChart()
  Dim i As Integer
  
  Worksheets("Sheet1").Columns(2).Clear  'Delete
  Worksheets("Sheet1").Columns(1).Clear  'Delete
  
  For i = 1 To Charts.Count
    If Charts(i).Name = "Chart Data" Then
      Application.DisplayAlerts = False
'      Charts(i).ChartArea.Clear
      Charts(i).Delete
      Application.DisplayAlerts = True
    End If
  Next
End Sub

Bits · 2012-02-24 19:09

Now this is amazing. I never would have thought about doing it this way. Thanks for opening up my eyes.

excel help

Comments