YouTubersLoveExcel#37: Formula Input Efficiency

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

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,

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

Hello again,

I am working on a project to build a standalone application for constructing flat files. I have it working, but it is very clunky and takes up a lot of space, about 2MB. All the output strings need to be delimited by a field separator "^". I was wondering if there is a way to add the field separator automatically. At the moment I am using the concatenation function to add it between dynamic fields and the "&" operator in static pieces of data. I have included some examples below.

Static use:
="T^900^"&TEXT(TODAY(),"yyyy-mm-dd")& "^"&
SUM(COUNTIF('Error Reduction'!C4:C3003,"PASS"),2)

Dynamic use:
=IF('Input File'!C3="VIN",
CONCATENATE(A3, B3 ,C3, B3 ,E3, B3 ,F3, B3 ,G3, B3 ,H3, B3 ,I3, B3 , B3 , B3 , B3 , B3 , B3 ,N3),
CONCATENATE(A3, B3 ,C3, B3 ,D3, B3 ,F3, B3 ,G3, B3 ,H3, B3 ,I3, B3 ,J3, B3 ,K3, B3 , B3 ,L3, B3 ,M3, B3 ,N3, B3 ,O3, B3 ,P3))

As you can see with the second example, some fields are not requires for each entry and field separators are grouped together. The field separators are in bold. Unfortunately, this is a formatting issue that is required and the byproduct of this mess is extremely long formulas. If I can simplify the formula it will be much easier to improve my validation formulas... (Yes, I will most likely post those as well when this issue is resolved).

Any assistance would be greatly appreciated.

Thanks in advance

Joe

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))) 

Is there a better way to write this array so it doesn't suck up so much power? I use it 500+ times in my spreadsheet and it is a hog.

{=SUM(IF('2010.10.22 Current Employees'!$H:$H=$C7,IF('2010.10.22 Current Employees'!$C:$C="P",'2010.10.22 Current Employees'!$R:$R)))}

Thanks

Morning Guys & Gals,

I've a large unwieldy spreadsheet i've concocted with an array of array formula's. Here's one i made earlier:

{=SUM(IF(ISNUMBER(MATCH(LEFT(INDIRECT($W$1),1),{"E"},0)),IF(INDIRECT($Y$1)=$C$3,IF(INDIRECT($Z$1)="Book",IF(INDIRECT($AA$1)="New",INDIRECT($X$1))))))}

What this does is to look up (via those indirect refs) a data sheet, based on criteria in 4 columns.

I suspect that sumproduct might be more efficient, but i'm unsure how to convert the thing :s

Also, will it be any faster?
Thanks!

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

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.