Finding 2nd Largest Item in a List that meets more than 1 Criteria [Excel Array Formulas]

={LARGE(IF(Sheet2!$D:$D=Sheet1!$B$3,IF(Sheet2!$K:$K="",Sheet2!$H:$H,""),""),2)}

You add the brackets by pressing CTRL+SHIFT+ENTER.

Let me explain: Your are looking for the 2nd Largest number with the criteria of having column D = Range(B3) and where Column K = nothing. If you only wanted 1 criteria you would use:

={LARGE(IF(Sheet2!$D:$D=Sheet1!$B$3,Sheet2!$H:$H,""),2)}

How to find the nth word in a excel cell

=IFERROR(IF($R$1=1,MID(O2,1,FIND(" ",O2,1)),MID(MID(MID(SUBSTITUTE(O2," ","^",$R$1),1,256),FIND("^",SUBSTITUTE(O2," ","^",$R$1)),256),2,FIND(" ",MID(MID(SUBSTITUTE(O2," ","^",$R$1),1,256),FIND("^",SUBSTITUTE(O2," ","^",$R$1)),256))-2)),RIGHT(O2,LEN(O2)-FIND("*",SUBSTITUTE(O2," ","*",LEN(O2)-LEN(SUBSTITUTE(O2," ",""))))) )

This will give you what ever word you want in the cell range("O2"). You can tell excel what word placement you want returned in cell range("R1"). The one catch is that if you want to return the 3rd word in a cell you will need range("R1") to be 2. This is because excel starts counting with 0. So to find the 3rd word you would have to have "word 0" then "word 1" then "word 2". See how "word 2" is the 3rd word in the cell.

Good luck.

How to return the number of words in a cell?

Answer: the following formula will return the number of words in an excel cell.

 =If(LEN(TRIM(B1))=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(B1," ",""))+1)

Possible situation where you would want to use this formula: I download students data because I'm a DBA at xyc district. One column of data gives me the grades of each school (k 1 2 3 4 5). The next column gives me total enrollment. I want to find the average enrollment in each grade at each school. I would use the above formula to find the number of grades in each school so I could divide enrollment by number of grades at each school.

Sum data found in a range or specific data

This function lets someone take the sum of data in a row that matches a range of data so you don't have to do a sumif() function over and over for the sum of the data you want.

=sumproduct((m4:zz4>=(I8-120))*(m4:zz4<=(I8)),m8:zz8) =sumproduct((row of dates>=(date-120 days"past date"))*(row of dates<=(date"future date")),row to be added)

This function allows you to count multiple data types instead of using a sumif function.

=sumproduct((m4:zz4>=(I8-120))*(m4:zz4<=(I8)),m8:zz8) =sumproduct((row of dates>=(date-120 days"past date"))*(row of dates<=(date"future date"))*(Other Data Typer Row = Unique datatype),row to be added)

Count the Number of Colored Cells in Excel

First open the workbook in which you wish to count or sum cells by a fill color. Now go into Visual Basic. Insert new module:

Code:

Funtion ColorCount(rColor As Range, rRange as Range, Optional Sum As Boolean)
Dim rCell as Range
Dim lCol as Range
Dim vResult


1Col = rColor.Interior.ColorIndex

If Sum = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.Sum(rCell, vResult)
End If
Next rCell

Else

For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell

End If

COlorFunction = vResult

End Function

--------------------------------------------------------

You can use the function that was just created by inputing this code into a cell

=ColorFunction($C$1,$A$1:$A$12,TRUE) to sum the values in the A:A column Range that have the same fill color as cell $C$1. The reason it will sum in because the code will have True as the last argument for the custom function.

VBA Code for Random Generation of Stats

CoinToss()

Dim x As Integer
Dim Row as Integer
Dim NumberOfFunds As Integer

erOfFunds = Sheets("Sheet1").Range("NumberOfPeople")

Worksheets("Sheet1").Range("ClearNumber").Select
Selection.ClearContents----------------------------Clears data from cells

For x = 1 To NumberOfFunds-------------Starts loop

Application.ScreenUpdating = False--------------Stops flickering of screen

Calculate-------------------------------This calculates the random generation function
Range("Future1").Select
Selection.copy
Range("Option1").Offset(x, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkpBlanks _
:=False, Transpose:=False

Range("Future2").Select
Selection.copy
Range("Option2").Offset(x, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Next

Range("M10:N109").Select----------------------This formats the numbers that are generated
Application.CutCopyMode = False
Selection.NumberFormat = "0.0000"
Selection.NumberFormat = "0.000"
Selection.NumberFormat = "0.00"
Selection.NumberFormat = "0.0"
Selection.NumberFormat = "0"

Range("A1").Select------------------ends macro in cell a1

Sub

Find Function in VBA

----------This Search Feature allows you to add a variable such as strMyString------------
----------strMyString can be any value you want to search for in a worksheet-------------

Dim strMyString as String----------Sets the variable for use

Cells.Find(What:=strMyString, After:=Activecell, Lookin:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate