Free Microsoft Excel 2013 Quick Reference

Excel boxplot

How do I make an Excel boxplot of a data sample?


Hey. Got a problem on making a boxplot in excel 2007.
I used to make excel 2003 to make the boxplot, but now I have the 2007
edition.
I learned from my teacher that I need to use the "High-Low-lines" and the
"Up/down-bars" to make the boxplot.
The problem is that when I made the "Up/down-bars" it will cover the
data-points so I can't see median ect.
Is there anyway to change the order of the chart-figures like to show the
data-point at the top, the up/down-bars in the middle and the high-low-lines
at the bottom just like when u change the order of several objectives?

Sorry my english! Want to show u guys what I mean but don't know a place
where I can place pics of the screenshot

Is it possible to make a boxplot in excel, I think I remember doing it
once... a long time ago...

I'm using 2007 excel and have an assignment from a very anti-excel stats grad
professor who wants us to do a boxplot - and later multiple regression
analysis. Are these 2 functions possible in excel and if so, how?

Thanks
Leann

how to creat boxplot graph by Excel?

Hi everyone,

I am trying to create percentile markers next to each group on a scatter
graph. The percentile markers are 5th, 25th, 50th, 75th and 95th. The markers
should be in the shape of a boxplot. There are 4 groups and the markers
should be next to each group. I tried using the drawing tool (in microsoft
word) to do this but it was difficult to get the lines in the correct
position.

I'm using Microsoft excel 2003.

Thanks in advance,
Saroj

My students are learning statisics and they have to make a boxplot - can I
make that in Excel. The calculater we have can make such a plot but it would
be nice if Excel had the same feature.

I want to generate boxplots (box plots) for statistical analyses. The Excel Stock Chart is a form of box plot (actually you can replace the closing price with the series mean/average and it is even closer) so I am wondering whether anyone has modified it or found other ways of working with it to make it more of a boxplot/whiskers diagram.

Hi,

I would have an excel spreadsheet of say 100 columns X 250 rows of data, each column being a different parameter. A boxplot is required to be drawn for each parameter if the data meets a certain criteria. This boxplot would ideally show different colour boxes for certain boxes, and I would need to draw 3 dotted lines on the chart as well, to show the target, the lower and upper limits. (I've attached a small example of what would be required)

I know excel can be 'tricked' into drawing a boxplot, but i can't trick it into drawing the line graphs on there as well.

This would have to be automated in a macro. I think i'd have to export it into another program which would be controlled using vba, plot the required graphs, then copy and paste those graphs either back into excel, or save as image files, or paste them into a word document. The original excel file would periodically be updated with more data.

I don't know which software would be better to use. Definately something where lots of books have been written about it, or lots of postings here. The ones available currently at our facility are JMP version 7, cornerstone version 3.5, i think statgraphics is also available. But am open to other software as long as it does the job (my budget is small).

Can anyone help?

Rivercup

Has anyone any insight on changes to the charting options in Excel 2007?

What I have found emphasizes integrating the excel charting with word
and powerpoint and fancy chart themes.

But there's nothing on any new chart types, like boxplots
or things like scatterplot data labels.

I know we can download the beta, but I'm afraid of what it might do to
my existing version of office..

Hi,

I'm doing this assignment for Maths and I need to do parallel boxplots. I
know how to do them on Excel, but one of the boxplots has outliers and I MUST
plot them in, but I have no idea how to do that. I was hoping that someone
would be able to tell or show me how to do that.

I'd appreciate it.
Thanks.

Hello every body

Please , i need it necessary , please please please ..

I want Add-ins for Excel 2000 , i need to do the BoxPlot

Can any one give me it ?

Thanks all

& good bye

How can I generate boxplots in Excel?
--
Regards

Philip

How do you graph a boxplot(for stats) in excel? I'm using excel 2003.

Hi, I am writing a macro to import excel files from a folder into new sheet that I want to be named automatically after the name of the excel file that was imported.

How do I do that? I know I can use sheet.add; however, how do I name it after the imported file???

Please, let me know how I can do that.

Thank you,

Erzhena

Hi geniuses,

I have little experience using VBA with PPT. What I want to do is relatively simple.

I have two excel sheets with 5 charts, each chart has a specific name.

I have a powerpoint presentation with 10-15 slides, all with different content but five of those slides (slide numbers vary every month) hold the excel charts. Those five slides also have other content like textboxes, etc.

I want to:
1) open the PPT presentation from my hard drive
2) paste the 5 charts into their "places" in the PPT.

Is this possible? How do I "identify" in PPT where each chart goes? Is there a placeholder I can use in PPT or would the excel VBA code include "Top" and "Left" data for each target slide?

If someone could put some sample code I would appreciate it.

Thanks a million,

Onetimesten

First of all, thank you all for your help! Here is the scenario:

I have a primary excel spreadsheet that I work from. The architecture is as follows:

Sheet 1: Called "Information"
Column A: Name
Column B: Date of Birth
Column C through Z: Various bits of information.

Sheet 2: Called "Master"
Cell B1: Contains the date and time of last update from the VBA I am asking for below.

On a weekly basis I get sent a "Update" spreadsheet that is constructed the same way as my primary. This is what I would like to do with some VBA:

From my primary sheet I run the VBA and it opens a pop up that allows me to select the updated worksheet. Next it cycles through both worksheets (Primary and Update). It compares Column A and B, if it finds a match it updates columns C through Z from the "Update". In order to get a match cell A1 and B1 of the primary worksheet has to match Cell A1 and B1 of the update sheet exactly.

The second thing I would like it to do is if the update sheet contains a new entry...in other words the update sheet has a row that does not match the primary it copies the row from the Update sheet to the Primary. In this way, the Primary sheet is always growing with new information and updating any old information it matches.

The last thing I would like to do is copy the current date and time after the comparison is complete to the Primary workbook to sheet "Master" in cell B1. This way I always know the time and date of the latest update. I hope this makes sense. I am sure it is easy for you guys. Thank you again for any help! Brian

First off great website, great resource, im about half way through the free VBA lessons.

I am an engine design analysis engineer and i am trying to (using office 2003):
1st organize my data
2nd organize the data in such a way it can be used for analysis

The data is currently a mess. A test is performed, the results are then placed in a template (which has varied over time) and is placed on any of multiple network harddrives to float around for eternity. We now have the resources(kind of) to create a more efficient system, i am sort of doing this as a side project. My plan of attack is to go through all of my data an apply it to an excel template, each test will be a separate file (say 50-150 total); this will create a "standard" for future use. All the files will then be located on a (probably) shared drive.

I then plan to create an excel "database" file for analysis/organization (filenames are extensive and wont suffice, opening each file is overwhelming given the data). I have many ideas for the analysis side but I'll start with what will be the organization sheet.

After doing some googling and reading here are some ideas I have:
Create a List in the database excel file (which needs to remain functional, i.e. can still sort using List options)
Paste the file directory into a cell within "add data" row of the list (or filename if there is a search directory option)
Write macros that will look-up the file (not sure of the best look-up method yet, I have only found direct windows path look-ups not relative or floating look-ups, i.e. the folder the "database.xls" file is located) and generate the the row within the list (and in the future generate data in other analysis sheets). I did read over this thread http://www.ozgrid.com/forum/showthread.php?t=42979 and would prefer the method of not opening the file when looking up data given their size.

Using this method anyone could apply the test data to the template, add it to the master directory, open the database file, paste the name and be done. I'm not sure the order of operations is consistent with how VBA works (just started working with it today) but I would love to hear some ideas/insight. I do have a contact with someone that writes excel VBA code for a living but im hoping this forum might have a little more patience than my friend.

Future ideas:
Multiple plotting utilities on different sheets, check boxes for what tests you would like to see etc.
I'd like to stay excel based for now but if this goes well it might expand into something access/web based and include data for all of my group's testing (we are a mid-sized company so we have quite a bit of data)

I appreciate any help, ideas, or comments!

I've decided to take up a VBA course and I'm deciding between learning Excel VBA on Excel 2003 platform as well as Excel VBA on Excel 2007 platform. The cost and duration for both courses are the same. Which course would you recommend I take?

-connor

Hi there,

I've recently been writing an excel spreadsheet and have encountered a weird problem.
i have a workbook where buttons open userforms which add and remove worksheets.

Clicking a button opens a userform called DelSysForm which asks weather you want to delete the sheet or not.
the code for the form is


	VB:
	
 Userform_Initialize() 
    Label1.Caption = "Are you sure you wish to delete the system?" & vbNewLine & "This process is not reversible" 
    Noshts = Sheets("Info").Range("TotSysRng").Value 
    NoDel = Range("PgNo").Value 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
NoDel and Noshts are private integers

the coding for the userform button is this


	VB:
	
 
Private Sub Yes_Click() 
    Call DelSheets(NoDel, Noshts) 
    Unload DelSysForm 
End Sub 

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


	VB:
	
 DelSheets(ShtNo, TotShts) 
    Dim i As Long, StrNu As String 
    For i = 1 To TotShts 
        StrNu = CStr(i) 
        If i < ShtNo Then 
             'Do Nothing
        ElseIf i = ShtNo Then 
             'delete sheet
            Sheets("System " & StrNu).Delete 
             
             
        ElseIf i > ShtNo Then 
             'rename sheets
        End If 
         
         
    Next i 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Now what happens next is really weird.
When I try to run the userform from the worksheet the code runs smoothly but just after the process is completed excel crashes and i have to restart.
I tried debugging through the whole process and found that it is all working perfectly. the sheet to be deleted is removed etc but then at the end of the process once again excel crashes.
So i tried a few other things. I made a new userform and rewrote the code but once again crashed
I then tried not deleting the worksheet i.e. made the line "Sheets("System " & StrNu).Delete" into comments and presto didn't crash.
I also tried running the userform from vb editor and presto didn't crash
I should also point out that it only crashes when ShtNo is greater than one.

i am completely baffeled. I've been trying for a few hours to figure out what is causing the error to no avail
If anyone has any suggestions please help.
I dont mind changing the code completely if that creates a solution but right now this seems the easiest and best way to do it

Cheers

and by the way where ive put rename sheets is purely because i havent written that part yet

Every day i want to send email to remind about the team activity , i have all the information filled in excel sheet. Macro need to look in to the date column and triger the mail with the subject mentioned in the other column , to the address mentioned in the other column.

I tried Many of the sample code but i dont find a solution out of it.Appreciate if any one can do it.

sample file has been attached for reference.

Hi,

Sometimes we convert database file to Excel file but the structure is not what we want. See db.xls, B2:D20. I want to rearrange the cell range B2:D20 to the format F2:R6. Put the corresponding numbers or letter (D3:D20) in the range G3:R6. If there is no sale level in certain month, put 0.

Is there a formula that can be put in the cell range G3:R6 to achieve this?

Thanks

hi all,
i have a very simple code for hiding/showing fields. i actually have no problam writing it for every tab, but excel crashes so i guess i overflowed it.

my current code is

	VB:
	
 Range 
     
     
    Set myRng = Me.Range("a15:af15,a16:af16,a17:af17,a18:af18") 
     
     
    myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden) 
End Sub 
 
 
 
 
Private Sub CommandButton11_Click() 
     
     
    Dim myRng As Range 
     
     
    Set myRng = Me.Range("a5:af5,a6:af6,a7:af7,a8:af8") 
     
     
    myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden) 
End Sub 
 
 
Private Sub CommandButton12_Click() 
    Dim myRng As Range 
     
     
    Set myRng = Me.Range("a10:af10,a11:af11,a12:af12,a13:af13") 
     
     
    myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden) 
End Sub 
 
 
Private Sub CommandButton2_Click() 
    Dim myRng As Range 
     
     
    Set myRng = Me.Range("a20:af20,a21:af21,a22:af22,a23:af23") 
     
     
    myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden) 
End Sub 
 
 
Private Sub CommandButton3_Click() 
    Dim myRng As Range 
     
     
    Set myRng = Me.Range("a25:af25,a26:af26,a27:af27,a28:af28") 
     
     
    myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden) 
End Sub 
 
 
Private Sub CommandButton4_Click() 
    Dim myRng As Range 
     
     
    Set myRng = Me.Range("a30:af30,a31:af31,a32:af32,a33:af33") 
     
     
    myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden) 
End Sub 
 
 
Private Sub CommandButton5_Click() 
    Dim myRng As Range 
     
     
    Set myRng = Me.Range("a35:af35,a36:af36,a37:af37,a38:af38") 
     
     
    myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden) 
End Sub 
 
 
Private Sub CommandButton6_Click() 
    Dim myRng As Range 
     
     
    Set myRng = Me.Range("a40:af40,a41:af41,a42:af42,a43:af43") 
     
     
    myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden) 
End Sub 
 
 
Private Sub CommandButton7_Click() 
    Dim myRng As Range 
     
     
    Set myRng = Me.Range("a45:af45,a46:af46,a47:af47,a48:af48") 
     
     
    myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden) 
End Sub 
 
 
Private Sub CommandButton8_Click() 
    Dim myRng As Range 
     
     
    Set myRng = Me.Range("a50:af50,a51:af51,a52:af52,a53:af53") 
     
     
    myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden) 
End Sub 
 
 
Private Sub CommandButton9_Click() 
    Dim myRng As Range 
     
     
    Set myRng = Me.Range("a55:af55,a56:af56,a57:af57,a58:af58") 
     
     
    myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden) 
End Sub 
 
 
Private Sub CommandButton10_Click() 
    Dim myRng As Range 
     
     
    Set myRng = Me.Range("a60:af60,a61:af61,a62:af62,a63:af63") 
     
     
    myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden) 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
i need to use the exact same code for all 400 tabs(sheets)

tried to find an answer but failed to. so if u can help it would be much appreciated.

BR,
Tal

Hi All,

i have some vba code to remove share mode for all excels of folder, but i need code to skip excel file which is password protected. Please help me to solve this.


	VB:
	
path = GetDirectory 
FileName = Dir(path & "*.xls", vbNormal) 
Do Until FileName = "" 
    If FileName  ThisWB Then 
        Set Wkb = Workbooks.Open(FileName:=path & "" & FileName) 
         
        If ActiveWorkbook.MultiUserEditing Then 
            ActiveWorkbook.ExclusiveAccess 
        End If 
         
        Wkb.Save 
        Wkb.Close False 
    End If 
    FileName = Dir() 
Loop 

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


I'm fairly new to vb, and i've been writing small scripts to interface between Excel and SAP r/3 in order to automate batch entry functions. I'm a bit hung up on a need to increment and loop a series of entries that are based upon a session id from within SAP. See current entry below:

PHP Code:
Dim session
Function CallSAPTransaction()
Dim Application
Dim material, plant As String
Dim sbarmessage
If Not IsObject(Application) Then
   Set SapGuiAuto = GetObject("SAPGUI")
   Set Application = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
   Set Connection = Application.Children(0)
End If
If Not IsObject(session) Then
   Set session = Connection.Children(0)
End If
If IsObject(WScript) Then
   WScript.ConnectObject session, "on"
   WScript.ConnectObject Application, "on"
End If
InputRow = InputBox("Enter the Starting Row", "Starting Row", 4)
Row = CInt(InputRow)
p_Item = Worksheets("BOMUpdate").Cells(Row, 1)
While p_Item  ""
    p_Item = Worksheets("BOMUpdate").Cells(Row, 1)
    p_Pn = Worksheets("BOMUpdate").Cells(Row, 2)
    p_Qty = Worksheets("BOMUpdate").Cells(Row, 3)
    
    session.findById("wnd[0]").resizeWorkingPane 97, 44, False
    session.findById("wnd[0]/usr/tabsTS_ITOV/tabpTCMA/ssubSUBPAGE:SAPLCSDI:0152/tblSAPLCSDITCMAT/txtRC29P-POSNR[0,0]").Text = p_Item
    session.findById("wnd[0]/usr/tabsTS_ITOV/tabpTCMA/ssubSUBPAGE:SAPLCSDI:0152/tblSAPLCSDITCMAT/ctxtRC29P-IDNRK[2,0]").Text = p_Pn
    session.findById("wnd[0]/usr/tabsTS_ITOV/tabpTCMA/ssubSUBPAGE:SAPLCSDI:0152/tblSAPLCSDITCMAT/txtRC29P-MENGE[4,0]").Text = p_Qty
     
    Row = Row + 1
    p_Item = Worksheets("BOMUpdate").Cells(Row, 1)

Wend
session.findById("wnd[0]").sendVKey 0
End Function 
The 3 field names are constant, but their location identification changes. [0,0], [2,0], and [4,0] represent row column and row, row being the initial at 0. So, for the next row, the location values would change to [0,1], [2,1], and [4,1], then [0,2], [2,2], [4,2], and on.

I can figure out how to do a standard loop on the line, but i cant figure out how to loop AND increment. Any ideas would be appreciated.