Saturday, September 17, 2005
Hide formula bar
Sometimes a formula becomes very long and it occupies lots of cells and it makes you crazy. So a better way would be
1. Record a macro, give a proper name, description and most importantly define a shortcut key, different from Excel's shortcut keys.
2. Don't record anything and stop the recording.
3. Press Alt + F11 to open the Visual Basic Editor , search for your macro inside "Modules" and insert this code in your macro.
That's it. Whenever you're done editing the formula just press the shortcut key to bring it back press it again.
If you want to do it manually, please follow these steps:
1. Click on Tools - on the menu
2. Click on Options
3. In the View Tab uncheck Formula Bar
4. Hit ok.
OR you can press these shortcut keys in Sequence>> Alt + T, O, ALt + F, Enter
1. Record a macro, give a proper name, description and most importantly define a shortcut key, different from Excel's shortcut keys.
2. Don't record anything and stop the recording.
3. Press Alt + F11 to open the Visual Basic Editor , search for your macro inside "Modules" and insert this code in your macro.
' hideformulabar Macro
' Macro recorded 9/17/2005 by Ashutosh
If Application.DisplayFormulaBar Then
Application.DisplayFormulaBar = False
Else
Application.DisplayFormulaBar = True
End If
' Macro recorded 9/17/2005 by Ashutosh
If Application.DisplayFormulaBar Then
Application.DisplayFormulaBar = False
Else
Application.DisplayFormulaBar = True
End If
That's it. Whenever you're done editing the formula just press the shortcut key to bring it back press it again.
If you want to do it manually, please follow these steps:
1. Click on Tools - on the menu
2. Click on Options
3. In the View Tab uncheck Formula Bar
4. Hit ok.
OR you can press these shortcut keys in Sequence>> Alt + T, O, ALt + F, Enter
Long formulas??
Are you facing problems with long formulas? We are. So here are some articles, which are quite useful.
-Index and refering
MrExcel1
-ISerror and IsNA and a long formula
MrExcel2
-Exceed 7 Ifs
MrExcel3
ChipPearson1
-named ranges
ChipPearson1
Also, I created this function, which will find if any of the given values exists in the given range. Normally, we would use this
But, when the list and formula gets longer it becomes unreadable and takes time to calculate.
Instead you can use this function. Copy and paste the code in a module in the workbook you're working on. Use this function in any cell where you want to check if multiple values are present or not.
For eg. If you want to check if cell A1 has any one of these values Chair, Interim Chair, Prof.
Enter this formula = Isvalpresent(A1,"Chair","Interim Chair", "Prof"), it will return either True or False
It can take 9 values to check and can be easily increased by changing the code. This function will be very useful when you have long formulas.
-Index and refering
MrExcel1
-ISerror and IsNA and a long formula
MrExcel2
-Exceed 7 Ifs
MrExcel3
ChipPearson1
-named ranges
ChipPearson1
Also, I created this function, which will find if any of the given values exists in the given range. Normally, we would use this
=IF(OR(A1="Chair",A1="Co-Chair",A1="Prof"))
But, when the list and formula gets longer it becomes unreadable and takes time to calculate.
Instead you can use this function. Copy and paste the code in a module in the workbook you're working on. Use this function in any cell where you want to check if multiple values are present or not.
For eg. If you want to check if cell A1 has any one of these values Chair, Interim Chair, Prof.
Enter this formula = Isvalpresent(A1,"Chair","Interim Chair", "Prof"), it will return either True or False
It can take 9 values to check and can be easily increased by changing the code. This function will be very useful when you have long formulas.
Public Function IsvalPresent(IPRange As Range, val1 As Variant, Optional val2 As Variant = "", Optional val3 As Variant = "", _
Optional val4 As Variant = "", Optional val5 As Variant = "", Optional val6 As Variant = "", Optional val7 As Variant = "", Optional val8 As Variant = "", Optional val9 As Variant = "") As Boolean
'
' IsvalPresent Macro
' Checks for a value if it is present in the given range. Use this function instead of IF and OR functions.
'
'
Dim c As Range, arr1 As Variant
arr1 = Array(val1, val2, val3, val4, val5, val6, val7, val8, val9)
For Each c In IPRange
For i = 0 To 8
If Not IsNothing(arr1(i)) Then
If c = arr1(i) Then
IsvalPresent = True
Exit Function
End If
End If
Next i
Next c
End Function
Public Function IsNothing(val As Variant) As Boolean
If val = "" Then IsNothing = True
End Function
Optional val4 As Variant = "", Optional val5 As Variant = "", Optional val6 As Variant = "", Optional val7 As Variant = "", Optional val8 As Variant = "", Optional val9 As Variant = "") As Boolean
'
' IsvalPresent Macro
' Checks for a value if it is present in the given range. Use this function instead of IF and OR functions.
'
'
Dim c As Range, arr1 As Variant
arr1 = Array(val1, val2, val3, val4, val5, val6, val7, val8, val9)
For Each c In IPRange
For i = 0 To 8
If Not IsNothing(arr1(i)) Then
If c = arr1(i) Then
IsvalPresent = True
Exit Function
End If
End If
Next i
Next c
End Function
Public Function IsNothing(val As Variant) As Boolean
If val = "" Then IsNothing = True
End Function
Thursday, September 15, 2005
Boxplots in Excel
Boxplots in Excel: "Boxplots in Excel
by Neville Hunt, Coventry University
Acknowledgements
An earlier version of this article was published in The Spreadsheet User Volume 3, Number 2, November 1996.
I am grateful to Rodney Carr for greatly simplifying my original method.
Introduction
A boxplot, or box and whisker diagram, provides a simple graphical summary of a set of data. It shows a measure of central location (the median), two measures of dispersion (the range and inter-quartile range), the skewness (from the orientation of the median relative to the quartiles) and potential outliers (marked individually). Boxplots are especially useful when comparing two or more sets of data. Regrettably, there is currently no boxplot facility in Microsoft Excel. For simplicity, many recent statistics textbooks (for example, Daly et al, 1995) omit the fences used to identify possible outliers. These simplified boxplots, displaying most of the important features, can be drawn quite easily in Excel. In the absence of any fences (see Devore and Peck (1990) for a definition), a simple rule is that a whisker which is longer than three times the length of the box probably indicates an outlier."
by Neville Hunt, Coventry University
Acknowledgements
An earlier version of this article was published in The Spreadsheet User Volume 3, Number 2, November 1996.
I am grateful to Rodney Carr for greatly simplifying my original method.
Introduction
A boxplot, or box and whisker diagram, provides a simple graphical summary of a set of data. It shows a measure of central location (the median), two measures of dispersion (the range and inter-quartile range), the skewness (from the orientation of the median relative to the quartiles) and potential outliers (marked individually). Boxplots are especially useful when comparing two or more sets of data. Regrettably, there is currently no boxplot facility in Microsoft Excel. For simplicity, many recent statistics textbooks (for example, Daly et al, 1995) omit the fences used to identify possible outliers. These simplified boxplots, displaying most of the important features, can be drawn quite easily in Excel. In the absence of any fences (see Devore and Peck (1990) for a definition), a simple rule is that a whisker which is longer than three times the length of the box probably indicates an outlier."
Monday, September 12, 2005
Excel Instructions - Exponential Distribution
Excel Instructions - Exponential Distribution: "Exponential
Excel cannot directly generate data from an exponential; however, the following procedure can be used to obtain random observations from an exponential distribution.
The first step is to create a set of uniform random numbers between 0 and 1, see Uniform for more information.
To obtain the exponential random numbers, we need to use the following formula:
exponential random number = - mean*log(1 - unif, 2.71828)
where
mean is the mean for the exponential distribution
unif is a uniform random number
Note: Don't forget the - in front of the mean.
Copy the formula down for all observations. See 'How do I edit my work?' for more information on copy and pasting. "
Excel cannot directly generate data from an exponential; however, the following procedure can be used to obtain random observations from an exponential distribution.
The first step is to create a set of uniform random numbers between 0 and 1, see Uniform for more information.
To obtain the exponential random numbers, we need to use the following formula:
exponential random number = - mean*log(1 - unif, 2.71828)
where
mean is the mean for the exponential distribution
unif is a uniform random number
Note: Don't forget the - in front of the mean.
Copy the formula down for all observations. See 'How do I edit my work?' for more information on copy and pasting. "
