Excel Formula Efficiency 2: Time Your Formulas For Speed

Hello,

My question is about the If-Else Construct.

I often write If-Else statements that require an action be taken only if something is true. If that something is false, no action is to be taken.

My question is, how do you code "no action".

The following is what i usually code:

Code:

If Variable < 2 Then
    Variable = Variable + 1
Else
    Variable = Variable

As you see above, when false, I usually just assign the variable the value it already has. That's because I want nothing to happen if the result is false.

But isn't that a waste of time for my program? Am i not just giving my processor one more thing to do for no reason. Slowing down my program with unnecessary assignments?

If so, what are my alternatives? Is there a common programming strategy that all programmers adhere to in this situation? I mean, do I just put nothing after the Else Construct (except for a maybe a comment noting the nothingness was intentional)? Hmmm...

Thanks for any help! Trying to be efficient here

Hi,

The attached workbook has a sample (the actual data is very, very large) of data. Basically I have some VBA that is concatenating all of the unique values that match for every unique VisitorID. The calculations are taking way too long and need to find a better, more efficient way of getting to the same data.

The calculations are in columns I:M and the raw data is in columns A:F. I need to dynamically (this data is changing all the time) perform this calculation.

When you open the file, you will notice that the calculations do not take very long. I have cut the data down from about 100,000 rows to a much smaller sample for you.

Thanks so much for any help you can provide me!
John

Hi guys,

I'm very keen on getting the most efficient solution always for my spreadsheet problems. Often I have large spreadsheets with many calculations. It's quite frustrating having to wait a few seconds for a spreadsheet to recalculate as I'm sure you appreciate.

Does anyone know which spreadsheet forumlae to avoid and which are preferable in terms of speed? I find that array formulas are much slower than the usual formulas. How about VLOOKUPS, are they the first choice if possible? I found that SUMIFs were quite slow too, SUM(OFFSET()) is what I've replaced it with on my current spreadsheet and it calculated much quicker.

Let's discuss!

Hi guys,

I was wondering if someone could help me merge two formulas together?

Basically, I want the formula to read:

If I5="winner" then,

="=MIN"&"(query"&RIGHT(H5,8)&"!$D$7, "&"query"&RIGHT(H5,8)&"!$D$10, "&"query"&RIGHT(H5,8)&"!$D$13, "&"query"&RIGHT(H5,8)&"!$D$16)"

else,

=VLOOKUP(TRIM(LEFT(I5,FIND(":",I5)-1)),INDIRECT("'query"&RIGHT(H5,8)&"'!A2:D100"),4,0)


Also, once we have a fully complete formula, is there anyway to improve its efficiency?

My values are updating alot and someone told me that VLookup and Indirect aren't always the most efficient to use?

Anyway making this more efficient?


thanks guys

Hi,

I have the following code that matches the entries in column A against those in column C and prints "match" in the adjacent cell in column B when the entry in A is present somewhere in column C.

Code:

Sub macro1()


Dim i As Integer
Dim j As Integer

i = 0

Do

i = i + 1

Range("B1") = i

j = 1

    Do
    
    j = j + 1
    
    If Cells(i, 1) = Cells(j, 3) Then Cells(i, 2) = "Match"
    
    Loop Until Cells(j, 3) = ""


Loop Until Cells((i + 1), 1) = ""


End Sub

what i'm wondering is, is there a way of making this run much faster (other than stopping screen updating) as when there are several thousand entries in each column it can take a fair while to run. I'm thinking maybe there's an inbuilt function in excel that I am not aware of that could be used.
One possibility I just thought of is getting the 2nd loop to stop when a match is found rather than run through the entire list in column C as it currently does.

This is not a basic "how do I eliminate the zeros in an average"

A car spends a certain percentage of time at a number of different speeds. How do I solve for the Average Velocity of the vehicle?

Speeds a 0, 5, 10...100% speed

Time at each speed can be 0, 5, 10,...100% of available time

Total of all times cannot exceed 100%

I can get close, but nothing I have come up with will work in all cases.

I would love some help on this

Hi,

I've wondered this for a while.

Say cell A1 is Speed, B1 is Distance, C1 is Time.

I want to write a formula in each cell that is dependant on the value of the other two but I don't want the value I enter to overwrite the formula.

For eg. I want to calculate time so I enter values in the distance and speed cells. I then want to calculate speed so I delete what I previously entered in the speed cell and overwrite the answer it just gave me for time with my new value.

Is there a simple inbuilt function to do this?

Thanks

Rob

Does anybody have any ideas for a formula that would measure efficiency for employees. I currently count the amount of work done by my staff and graph to a bar chart but I would like to also put a % of efficiency to this amount of work done in a normal work day?

Thanks

Sorry .. If this is wrong place to post this question then please let me know . . .
All i know is that any help would be greatly appreciated.

Ok ...
On my spreadsheet i have a bunch of variables:

A1 = Arrival time at Checkpoint 1 - 5:53:08
A2 = Time to destination from Checkpoint 1 - 1:10:18

B1 = Arrival time at Checkpoint 2 - 6:00:56
B2 = Time to destination from Checkpoint - 1:02:30

C1 = Total distance from Start - 2.83
C2 = Arrival Time at Destination - =SUM(A1,A2)

D1 = Speed determined from the other variables
D1 is SUPPOSED to equal 25.00
It also is where I am stumped!!
Not only can I not figure out the math but also how to format the function to get the right answer.

ps: Arrival Time at Destination is only included in case it would be useful to find the answer!