Excel Functions | nandeshwar.info

Count Text in a Range

April 24th, 2009 No comments

Counting text in a range is easy. For example, if you would like count how many times “text” is in a specified range (named data), you can write something like this:
=COUNTIF(data,"*text*")

This formula will give you the count of all the cells where Excel found “text” at any place.

But let’s say you want to count wildcard characters in a range, then you can write something like this:
=SUM(IF(NOT(ISERR(FIND("*",data))),1,0))

This formula should be entered as an array formula with Ctrl + Shift + Enter. Usually, wildcard can be “escaped” using a tilde (~), but the FIND function doesn’t understand wildcard characters like the SEARCH function does.

Share

More »

Powered by Bookmarkify™

Categories: String Operations Tags: count, excel functions, string, wildcard

Add Specific number of zeros to a value

February 8th, 2006 No comments

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))

Share

More »

Powered by Bookmarkify™

Categories: String Operations Tags: excel functions

Random Numbers

January 21st, 2006 No comments

Recently, I had the need of generating different types of random numbers. Although, Excel’s Data Analysis Toolpak can generate good random numbers, I had to create these functions to meet my needs.

First: Unique Random Numbers
This function was modified from ozgrid’s RandLotto function, which returns string. This function will return an array of unqiue random integers in the given range. If you want to return more than one number then this function should be entered as an array formula with Ctrl + Shift + Enter.

Function UniqueRand(lngSmallest As Long, lngHighest As Long, HowManyNos As Long) As Long()
‘modified from http://www.ozgrid.com/VBA/RandomNumbers.htm
Dim iArr As Variant
Dim i As Integer
Dim r As Integer
Dim temp As Integer
Application.Volatile
ReDim iArr(lngSmallest To lngHighest)
For i = lngSmallest To lngHighest
iArr(i) = i
Next i
For i = lngHighest To lngSmallest + 1 Step -1
r = Int(Rnd() * (i – lngSmallest + 1)) + lngSmallest
temp = iArr(r)
iArr(r) = iArr(i)
iArr(i) = temp
Next i
Dim resultArr() As Long: ReDim resultArr(HowManyNos – 1, 0)
For i = lngSmallest To lngSmallest + HowManyNos – 1
resultArr(j, 0) = iArr(i)
j = j + 1
Next i
UniqueRand = resultArr
End Function

Second: Gaussian or Normal Random Numbers with specified mean and Standard deviation(SD). Now, this function is different than the Analysis toolpak utility because it generates random numbers on a fixed mean and Standard deviation. This function will allow the user to generate random numbers each with different mean and SD.

‘This function will generate Gaussian or Normal Random Numbers based on given mean and SD
Public Function GenGaussNoise(mean As Double, SD As Double, HowMany As Long) As Double()
Dim x As Double, i As Long, j As Long
itr = 50
Dim dblResArr() As Double: ReDim dblResArr(HowMany – 1, 0)
For j = 1 To HowMany
x = 0
For i = 1 To itr
Randomize
U = Rnd
x = x + U
Next i
‘ /* for uniform randoms in [0,1], mu = 0.5 and var = 1/12 */
‘ /* adjust X so mu = 0 and var = 1 */
x = x – itr / 2 ‘ /* set mean to 0 */
x = x * Sqr(12 / itr) ‘ /* adjust variance to 1 */
dblResArr(j – 1, 0) = mean + SD * x
Next j
GenGaussNoise = dblResArr
End Function

Third: Generate white noise or Guassian or Normal random numbers with mean = 0 and SD =1. Although, this could be achieved by the previous function, this function uses Box-Muller method. In this function too, you can use different mean and SD, although if not provided it would take mean =0 and SD=1

Public Function GenWhiteNoise(HowMany As Long, Optional mean As Double = 0, Optional variance As Double = 1) As Double()
‘Returns a normally distributed random variate with mean 0 and variance 1, a.k.a. “White Noise”.
‘ Uses the Box-Muller method
Dim Value1 As Single, Value2 As Single, Fac As Single, Rsq As Single, SD As Double
Dim dblResArr() As Double: ReDim dblResArr(HowMany – 1, 0)
SD = Sqr(variance)
For
pan> i = 1 To HowMany
Do
Randomize
Value1 = 2 * Rnd – 1
Value2 = 2 * Rnd – 1
Rsq = Value1 ^ 2 + Value2 ^ 2
Loop Until Rsq > 0 And Rsq < 1
Fac = (-2 * Log(Rsq) / Rsq) ^ 0.5
If Rnd < 0.5 Then
dblResArr(i – 1, 0) = mean + SD * Value1 * Fac
Else
dblResArr(i – 1, 0) = mean + SD * Value2 * Fac
End If
Next i
GenWhiteNoise = dblResArr
End Function

This is a screenshot of the Excel file. Also, the original Excel file can be downloaded here.