PDA

View Full Version : excel help

Bits
02-20-2012, 11:12 PM
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

Vin
2.5

Rf
15

Rc
15

Vref
2.5

Vout
2.5

With the equation

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

Martin_H
02-20-2012, 11:22 PM
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
02-21-2012, 03:24 AM
Something like this, or do I not understand your question?

Bits
02-21-2012, 01:39 PM
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
02-21-2012, 02:27 PM
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
02-21-2012, 02:47 PM
@bits,

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

http://forums.parallax.com/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

89760

Ron Czapala
02-21-2012, 03:17 PM
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
02-21-2012, 06:28 PM
I've expanded across a few more fields, you can vary any value, the result will update, and so will the graph

Duane Degn
02-21-2012, 11:04 PM
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.

http://forums.parallax.com/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
02-22-2012, 12:54 AM
@Duane,

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

http://forums.parallax.com/attachment.php?attachmentid=89777

89777

Duane Degn
02-22-2012, 02:46 AM
@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
02-22-2012, 02:09 PM
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
02-22-2012, 02:20 PM
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
Case "\$B\$1"
ScrollBar1.Value = Range("b1") * 10
Case "\$B\$3"
ScrollBar2.Value = Range("b3") * 10
End Select
End Sub

Ron Czapala
02-23-2012, 01:51 AM
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

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

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

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
02-23-2012, 02:33 AM
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
02-23-2012, 09:52 PM
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).

http://forums.parallax.com/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

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
' Charts(i).ChartArea.Clear
Charts(i).Delete