Excel Formula Efficiency 6: Concatenated Field Speeds Up Formula

hi,

(file attached).

im trying to find a way to automate or at the very least speeden the entry of data that you can see in the 'data input' tab. The reason why the data is laid out in this way is so i can create a pivot chart from it showing number of orders split by branch/order type over time.

at the moment im using
=SUMPRODUCT(('w.e. 29th june'!$M$10:$M$105="st albans")*('w.e. 29th june'!$N$10:$N$105="E"),('w.e. 29th june'!$D$10:$D$105))

but i have to change it for every single cell for it to work properly, and thats just for 1 tab, i'd have to keep editing it everytime i added a new tab with a new weeks worth of information on it.

There MUST be a way to speed this up, or put in a better formula which can pull info from a data in a better way, but i dont know how else to go about it.

would really appreciate some help on this - even willing to pay coz im desperate!

I know there are many posts on this subject, but this is not quite the same.

I found a solution for a problem I had, but would like to know if a more elegant way existed.

For a large table, I concatenated multiple cells to form a formula in about 2000 variations. I know now how to use relative and absolute references to do my work witout concatenating but here goes:

=CONCATENATE("=";Q2;R2;")") had this result: =AVERAGE(F2:F2), the goal was to have it growing to =AVERAGE(F2:F2000) at every line.

This would not calculate.

I copied and pasted the value so the content of the cell was now =AVERAGE(F2:F2), but still did not calculate.

These would not calculate until I doubleclicked in the formula and pressed enter. My auto calc is on, cell is defines as number or general, I tried CLEAN() and TRIM(), etc.

I also did not want to doubleclick and enter in every 2000 cells.

So I tried Replace All ''='' with ''='' and the calculation was performed!

There is a better way, I hope!

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,

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

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

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!