Excel Formula Efficiency 12: Fastest Formula In Excel

I am importing data to Excel 2007. The normal number of rows is about 650,000.

In Column B I have the account numbers. Their range is 5000.00 thru 9900.00, these are sorted lowest to highest and accounts maybe repeated.

Based on a range of say "Account >=5000.00 and Account is

Hi,

If I have a formula in cell D1 and I need to copy the formula all the way to cell D50000, what would be the fastest way ?

Thanks.

Hi,

I'm using Office 2003 running on XP SP 2, .NET FW 3.5, Visual Studio 2010

I'm positive that I've found a bug in Excel or maybe in Interop.Excel.

Via my Excel objects I'm writing a simple formula ta cell:

for (int row = numberOfDataRows; row >= 2; row--)
{
xlRow = (Excel.Range)xlSheet.Rows[row];
Excel.Range thisCell = (Excel.Range)xlRow.Cells[1, 2];
thisCell.Formula = "=MONTH(a" + row.ToString() + ")";

When I look at the result in the Excel file there are #NAME? in all cells where I added the formula to BUT in the FORMULA bar I can see the correct formula.

Now, if I click inside the Formula bar followed by an ENTER -> voila, the value of the cell is evaluated according to the formula. I don't know how-to solve this what I think is a bug so please, anyone here, help needed ASAP!

Best Regards

Hi,
I am building a price modeling excel workbook that is going to use a lot of tables and decoding similar to a true database. I'm tryiing to figure out the best way to lookup data in other sheets and pull it into my calculation table.

I know I could probably use VLookup, SumIf and Index/match all with some success, but I'm trying to keep the processing time down to a minimum as there will be hundreds of thousands of records to go through. I've been going through the VLookup vs Index match threads but I'm not sure there was a definitive winner. Also a co-worker threw me a curve ball and said SumIF would be faster (there is only one lookup value in a given array as they are unique keys)


Below is an example of a typical lookup I need to do.

Terr Name Com 1 COM 2 1 Terr 1 $ 319 $ 319 2 Terr 2 $ 341 $ 341 3 Terr 3 $ 356 $ 357 4 Terr 4 $ 516 $ 516 9 Terr 9 $ 443 $ 443 10 Terr 10 $ 439 $ 439

In another table, I need to look up the Terr code in Cell A1 and return the amount for COM 1 if B1 indicates that it is COM 1 or the amount for Com2 if B1 says COM 2

So my other table looks like this
A B C 1 COM1 2 COM1 3 COM2 4 COM1


What's the fastest way to do that?



Additionally, is there a way to match based on a third variable that is overarching the company. For example,

Product1 Product 2 Terr Com1 Com2 Com1 Com2 1 $ 319 $ 319 $ 36 $ 36 2 $ 341 $ 341 $ 33 $ 33 3 $ 356 $ 357 $ 34 $ 34 4 $ 516 $ 516 $ 48 $ 48 9 $ 443 $ 443 $ 55 $ 55


I'm not averse to Code but I'm trying to make this workbook user friendly (and have the calculations transparent) for the average analyst who know Excel but not VBA.

Thanks for your help.

-Mike

Hi,

I have recently upgraded from XL07 to XL10. I have a workbook with lots of data and macros etc and everything seems to be working as normal with one extraordinary exception. I have a macro that displays or hides columns in a 100+ column Results sheet depending on user-configured entries in another "ResultsMask" sheet, so that only relevant result fields are displayed (for a selected course in this instance) when the macro is executed. I suspect the macro is not either the prettiest or the most efficient but it does the job in a matter of 2 seconds under XL07. However, under XL10, the macro is taking well over a minute to execute. This seems extraordinary to me given that XL10 is more or less equivalent to XL07 in terms of speed for all other operations as far as I can determine.

The question is whether anybody can see anything in the code below that might account for this exceptional execution delay in XL10 but not in XL07?

Thanks for looking.
Orson.

Code:

Sub MaskResults()
'
' MaskResults Macro
'
Dim columntotal As Integer
Dim columncount As Integer
Dim currentcolumn As Integer
Dim courseident As String
Dim courserownumber As Integer

Application.ScreenUpdating = False

' Check to see whether on results sheet, exit if not

If ActiveSheet.Name <> "Results" Then

MsgBox "You must be on the Results sheet to use this function"

Exit Sub

End If

' Unprotect the results sheets and display all columns

ActiveSheet.Unprotect
Columns("A:hz").Hidden = False


' Identify CourseID for current focus and determine row no. in Courselist

If Range("F" & (ActiveCell.Row)).Value = "" Then

courseident = "None"
Worksheets("Config").Range("$e$5") = courseident

Exit Sub

End If

courseident = Range("F" & (ActiveCell.Row)).Value
Worksheets("Config").Range("$e$5") = courseident
    
    Sheets("Courses").Select
    Range("A2:A52").Select
    Selection.Find(What:=courseident, After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
        
courserownumber = ActiveCell.Row

' Set total no. of columns to check in ResultsMask

columntotal = Range("ResultsMask!a1").Value

' Hide columns in Results that correspond to columns marked "x" in ResultsMask
' for chosen course

currentcolumn = 1

For columncount = 1 To columntotal

    If Worksheets("ResultsMask").Cells(courserownumber, currentcolumn).Value = "x" Then Worksheets("Results").Columns(currentcolumn).Hidden = True

currentcolumn = currentcolumn + 1

Next columncount

' Re-activate and -protect worksheet

Worksheets("Results").Activate

    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowFiltering:=True, AllowFormattingCells:=True

End Sub

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

Hi all, if there is one simple thing I have trouble getting right it's a long IF formula.

I have one that I think is a lot longer than it needs to be, although it does what it needs to, more than anything I'm curious as to how other users would go about this (no macros allowed)

The criteria, numeric values entered in A1 and A2 to 2 decimal places

the formula needs to take the highest value of the 2 entries and return true as this value rounded up to the nearest even number (2.00 returns 2, 2.01 returns 4, etc).

Any value of 0 or greater than 6.00 should return false.

This was what I came up with but it seems longwinded

PHP Code:

=IF(AND(MAX(B2,C2)<=2,MIN(B2,C2)>0),2,IF(AND(MAX(B2,C2)>2,MAX(B2,C2)<=4),4,IF(AND(MAX(B2,C2)>4,MAX(B2,C2)<=6),6,FALSE)))