How Many Formulas In Your File?

Posted on January 23rd, 2011 in Excel by Ken Puls

The other day I was working on a financial model. During a break I saw on Twitter that @JesseH77 posted the following:

My budget files have 180,850 and 160,191 formulas and 11 lines of code to count the formulas.

This got me curious about my own model, so I knocked up some quick and dirty code, just to see:

Sub CountFormulas()

Dim cl As Range, ws As Worksheet, fcount As Long, ftcount As Long

 

For Each ws In ThisWorkbook.Worksheets

For Each cl In ws.UsedRange

If cl.HasFormula Then fcount = fcount + 1

Next cl

Debug.Print ws.Name & “:” & fcount

ftcount = ftcount + fcount

fcount = 0

Next ws

 

Debug.Print “Total Formulas in Model: ” & ftcount

End Sub

The results for the model I just built were:

0.0 TOC:37

1.0 Neighbourhood Plan Map:3

1.1 Unit Mix Calculations:389

1.2 Unit Mix Summary:355

1.3 Historical Projects:398

1.4 Site Quality:1457

1.5 Lot Cost & Revenue:2216

1.6 Revenue Timing:6278

2.0 Vertical Construction:0

2.1 Building Mix:806

2.2 Building Costs:598

2.3 Development Charges:34

3.0 Land & Infrastructure:836

3.1 Infrastructure Notes:14

3.2 Global Factors:16

3.3 Infrastructure Timing:24230

4.0 P&L – Standard:676

4.1 P&L – Contribution:1306

5.0 Cashflow-Consol By Sector:2079

5.1 Cashflow-Consol By Product:2079

5.2 Cashflow-Detail By Sector:2537

5.3 Cashflow-Detail By Product:2681

6.0 Notes:10

Total Formulas in Model: 49035

Doesn’t really mean anything, except that it’s a big workbook, but I still found it to be an interesting exercise.

11 Comments