Excel Formula Efficiency 3: Database D Functions Are Fast

I can accomplish the same task using a database function (i.e. dsum), a
math function (i.e. sumif), and an array formula. If my goal is to
make the spreadsheet run as efficiently (fast) as possible, which
should I choose? Let's assume this is a very large set of data, and if
pertinent, the equation will be used frequently.

Also, I've heard array formulas in large volumes can slow down a
spreadsheet considerably. Why is this the case? What is the best
source for finding out information like this?

Thanks for your help.

Hello,

I have the following code which I modified from a larger one at VBA Corner. As my spreadsheet is very large & complex, and has multiple external data feeds updating it, I wish to improve the efficiency of code where possible.
I am going to place the code into ThisWorksheet object under Workbook_Open().

Can the following code be made more efficient by stripping one of the functions? And does making each function private, as I've done, make it more effiicient?

Code:

'reads the value for the registry key i_RegKey
'if the key cannot be found, the return value is ""
Private Function RegKeyRead(i_RegKey As String) As String
    Dim myWS As Object
    On Error Resume Next
    Set myWS = CreateObject("WScript.Shell")
    RegKeyRead = myWS.RegRead(i_RegKey)
End Function
 
'returns True if the registry key i_RegKey was found
'and False if not
Private Function RegKeyExists(i_RegKey As String) As Boolean
    Dim myWS As Object
    On Error GoTo ErrorHandler
    Set myWS = CreateObject("WScript.Shell")
    myWS.RegRead i_RegKey
    RegKeyExists = True
  Exit Function
ErrorHandler:
  RegKeyExists = False
End Function
 
Sub Registry()
    Dim myRegKey As String
    myRegKey = Worksheets("Sheet1").Range("a1")
    If RegKeyExists(myRegKey) = True Then
        If RegKeyRead(myRegKey) = Worksheets("Sheet1").Range("b1") Then
        MsgBox "Program will now start"
    Else
        MsgBox "Failure!"
        End If
    End If
End Sub

Hi again

I have the following formulas in columns H:L

=IF(ISERROR(MATCH(D5;$D6:D$65536;0));"";D5)

=COUNTIF($D:$D;$D5)

=IF(COUNTIF(D:D;D5)>1;"DUPLICATE";"")

=IF($J5="DUPLICATE";SUMIF($D:$D;$D5;F:F);"")

=IF($J5="DUPLICATE";SUMIF($D:$D;$D5;G:G);"")


They are for spotting duplicates and adding numbers as you see

Well, How do I add these results in the respective D,E columns???

It is very painful for me to find one by one and put them in these D, E

Thanks

I'm trying to use DCOUNTA where I want to use an AND condition.

Here's the formula:

=DCOUNTA(Database,E2,X3:Y4)

Here's the criteria:

UNIT START DATE SERVICE OFFICE
" " Charlotte

The database matches the headers.
My count should only return 1.

But I get 10 as I have 10 service offices equal to Charlotte.

What am I doing wrong?

Hi All...

I have a workbook file containing several sheets, is it possible to pull data from another sheet or workbook with multiple column criteria.

For example :

i want to filled a cell in Sheet B from Luas_Tanam column in sheet A, i'm thinking a database query like this :

--select from sheet "A" where column "estate" = "BSRE" or column "Divisi" = "Div 01" or column "No_Block" = "J17" or column "Tahun_Tanam" = "2008"--

It goes like =DGET() functions, but i can't get it work, maybe any other solutions??

i've attached example of the workbook, sory for the bad "ENGLISH" question, hope you know what i mean.

Regards,
Nabihan

Scenario: A budget table with some of the following fields; Company, Project,
ExpenseType, and Amount to keep things simple.

Is their a way from within the excel's database statistical functions
formula to define the criteria by means of referring to the names of the data
fields themselves to form a dynamic and complex set of criteria instead of
defining hundreds of external criteria ranges that would be needed to define
every single possibility? Something like the following:
=DSUM(mytable,"Amount",+Company=a2,Project=b2,ExpenseType=c2) where this
formula could be copied down to refer to the company, project, and expense
type that was appropriate for the next row at row 3 vs. row 2 in the above
example.

Instead of having to do this: =DSUM(mytable,"Amount",CriteriaRange)
Thanks
Craig

Hi,

I have a question regarding the input of formulas through vba. Currently I am keeping a row of formulas in a ws on the top row and then copying them down with vba code to the end of my data set. I was wondering if this was faster than assigning vba r1c2 style formulas to each cell in each column of data? There are quite a few rows ~8000.

I am also currently manipulating another cell within the data by assigning formulas to it then I move to another sheet and try to assign cells in a new range a formula with formular1c1 and am getting an error. Does this property only work once in a sub? Do I need to clear the value being stored in it or something? I have changed the variable that it will be cycling through from a generic one "Cell" to PhysCell & FinCell on the two seperate sheets. The error I am getting is "Invalid use of property from the following code:

Range("CFinTag").Select
For Each FinCell In Selection
FinCell.FormulaR1C1 "=IF(ISNA(VLOOKUP(RC[2],PriorFinData,1,FALSE)),""New"",""Existing"")"
Next FinCell

I know it is two questions in one but I mostly am wondering which way is more efficient: writting formulas w/ vba or just copying the formulas down?

Thanks for the help

I need to know the Excel (in cell) formula for the following equation:

LOAD x .177
(2*3.14159) x (TORQUE)

Where the LOAD = A1
&
Where the TORQUE = B1

Thanks,
13blues

I have a formula that works but only 3 times before it becomes ineffiecient

=IF(ISNUMBER(SEARCH(M1,H66))=OR(ISNUMBER(SEARCH(M2,H66)))=OR(ISNUMBER(SEARCH(M3,H66))),"OK","Not OK")

The M range is "M1 to M24" but this doesn't work:

=IF(ISNUMBER(SEARCH(M1:M24,H66))),"OK","Not OK")

Can someone help me get the correct efficient formula?

Cheers,

Which do people think is the better/more efficient formula? and why?

{Sum(If(A1:A5=4,If(B1:B5=6,1,0),0))}

Sumproduct(--(A1:A5=4),--(B1:B5=6))

Countifs(A1:A5,4,B1:B5,6)


Much appreciated

Dan