Wednesday, February 22, 2006
Average of n samllest or n biggest numbers from a list
To calculate the average of n biggest numbers from a list enter this formula with Ctrl + Shift + Enter:
To calculate the average of n smallest numbers from a list enter this formula with Ctrl + Shift + Enter:
For an example:
=AVERAGE(IF(RANK(yourrange,yourrange)<=n,yourrange,""))
To calculate the average of n smallest numbers from a list enter this formula with Ctrl + Shift + Enter:
=AVERAGE(IF(RANK(yourrange,yourrange)>COUNT(yourrange)-n,yourrange,""))
For an example:
Wednesday, February 08, 2006
Add Specific number of zeros to a value
This is one of the finest forumlae I've ever seen. Check this post at MrExcel.
If you want to have a value specific number of digits otherwise add zeros in front of it, this formula could be used:
=TEXT(A1,REPT("0",n))
where n is the number of digits you want.
So 4555 becomes 000004555 with formula =TEXT(4555,REPT("0",9))
If you want to have a value specific number of digits otherwise add zeros in front of it, this formula could be used:
=TEXT(A1,REPT("0",n))
where n is the number of digits you want.
So 4555 becomes 000004555 with formula =TEXT(4555,REPT("0",9))
MOD Function and Mod Operator Return Different Values
I didn't know this before today when an user at MrExcel wondered why he was not getting consistent answers with Excel and VBA. The key is negative numbers.
If you happen to have negative numbers (one of them) and you want to calculate MOD, you should use this code in VBA
Where n is your number and d is your divisor.
For more info, check:
MOD Function and Mod Operator Return Different Values
If you happen to have negative numbers (one of them) and you want to calculate MOD, you should use this code in VBA
yourmodval=n-d*INT(n/d)
Where n is your number and d is your divisor.
For more info, check:
MOD Function and Mod Operator Return Different Values
Wednesday, February 01, 2006
Change references
If you want to change references of a formula, you could toggle F4 key inside the formula to change the references from Absolute, Column Absolute Row Absolute or all relative.
Although, if you have to change references from a range, you can use these two options
1. Download ASAP utilities, because it's worth it, they have more than 300 utilities which are used many times.
2. Use this code.
You'll have to download the add-in to make this code run. You can edit the Addin just by opening it in VBA. Here is the Addin. If you install this add in it will create a button "Change References"
Although, if you have to change references from a range, you can use these two options
1. Download ASAP utilities, because it's worth it, they have more than 300 utilities which are used many times.
2. Use this code.
Sub Change_refs()
'This sub will change references of formula to different options shown by a userform
'This code will not run without the userform
On Error GoTo Change_refs_Error
Load frmRefType
frmRefType.Show
Dim IPFormula As String, OPFormula As String, c As Range
Dim RefStyle As XlReferenceStyle
With frmRefType
If .obAbs Then
RefStyle = xlAbsolute
ElseIf .obCAbs Then
RefStyle = xlRelRowAbsColumn
ElseIf .obRAbs Then
RefStyle = xlAbsRowRelColumn
ElseIf .obRel Then
RefStyle = xlRelative
Else
Exit Sub
End If
End With
Application.ScreenUpdating = False
For Each c In Selection
If IsEmpty(c) Then GoTo Nextc
IPFormula = c.Formula
OPFormula = Application.ConvertFormula( _
Formula:=IPFormula, _
fromReferenceStyle:=xlA1, _
toReferenceStyle:=xlA1, ToAbsolute:=RefStyle)
c.Formula = OPFormula
Nextc:
Next c
Application.ScreenUpdating = True
Unload frmRefType
On Error GoTo 0
Exit Sub
Change_refs_Error:
If Not Application.ScreenUpdating Then Application.ScreenUpdating = True
Unload frmRefType
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Change_refs"
End Sub
'This sub will change references of formula to different options shown by a userform
'This code will not run without the userform
On Error GoTo Change_refs_Error
Load frmRefType
frmRefType.Show
Dim IPFormula As String, OPFormula As String, c As Range
Dim RefStyle As XlReferenceStyle
With frmRefType
If .obAbs Then
RefStyle = xlAbsolute
ElseIf .obCAbs Then
RefStyle = xlRelRowAbsColumn
ElseIf .obRAbs Then
RefStyle = xlAbsRowRelColumn
ElseIf .obRel Then
RefStyle = xlRelative
Else
Exit Sub
End If
End With
Application.ScreenUpdating = False
For Each c In Selection
If IsEmpty(c) Then GoTo Nextc
IPFormula = c.Formula
OPFormula = Application.ConvertFormula( _
Formula:=IPFormula, _
fromReferenceStyle:=xlA1, _
toReferenceStyle:=xlA1, ToAbsolute:=RefStyle)
c.Formula = OPFormula
Nextc:
Next c
Application.ScreenUpdating = True
Unload frmRefType
On Error GoTo 0
Exit Sub
Change_refs_Error:
If Not Application.ScreenUpdating Then Application.ScreenUpdating = True
Unload frmRefType
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Change_refs"
End Sub
You'll have to download the add-in to make this code run. You can edit the Addin just by opening it in VBA. Here is the Addin. If you install this add in it will create a button "Change References"
