Excel Formula Efficiency 8: Custom VBA Function Can Be Fast

in my spreadsheet, column A is No of pallets and Column B is Tuck Tonnage. column A will be filled with numbers while i want column B to be filled using formulas with the following criteria:
[*]i want 30 TON to show in column B if column A is between 22 to 24 pallets[*]i want 20 TON to show if column A is between 13 to 14 pallets[*]i want COMBINE to show if column A is greater than 14 but less than 22 and also if column A is less than 12.
i tried this formula butit didnt work; if(or(A1=22,A1=23,A1=24), "30 ton", if(or(A1=14,A1=13),"20 TON",if(OR(A1=15,A1=16,A1=17,A1=18,A1=19,A1=20,A1=21), "COMBINE",IF(A1=0,""))).

I'm using Excel to track a large set (~200 entries) of independent variables. I'd like to be able to increment any given variable by 50 as quickly as possible.

I'm assuming the fastest way will be to create push buttons next to each variable, then create a macro to increment the nearby variable when the button is pressed.

Right now, the only way I can see to do that is to create a new macro for each independent variable. I'm not willing to do that. Is there a way to create a general macro that would, say, increment the cell just to the right of the activating button by 50?

The sticking point I'm having is that when I click on a button, even if the button is lined up over a cell, the active cell does not change to the button's cell. If anyone could show me how to do that, I could get the rest.

Also, if there's a way to have the button label be formula-driven instead of plain text, I'd like that too.

Thanks in advance. I'm using Excel 2010 and Vista.

Bill

I have a equation that looks something like:

x= K * sqrt((Fun1*fun2)+1)*(1-(1/(7*x^2))^2.5))

(it's likely I goobered up the location of the parentheses, so don't take this formula as precise...just looking for ideas)

I need to solve for x. Note that X is in the denominator under radical. (also note that I have two imbedded custom functions also in the equation)

My issue is that while I can possibly write a routine to iterate on X until the result equals the input, I need to be able to do this for several dozen cells, and I'm concerned about recalc speed.

ideas?

I use the subtotal function a lot, but it seems to be limited to sum, product, average, sdev, etc.... Is there anyway to create a custom formula in the subtotal function??

Thanks in advance.

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,

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

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

Hi all,

I recently set up some functions based on Chip Pearson's tutorial for referencing worksheets from Formulas. (http://www.cpearson.com/excel/sheetref.htm)

The problem I'm now having is that I can't use VBA to set these functions in place; it returns a syntax error.

For example, I want I37 on most sheets to have the same formula. So I have the following:

Code:

For Each Worksheet In Worksheets

Select Case Worksheet.Index

Case 1, 2, 3
'Do Nothing

Case Else
'The below returns a syntax error (highlighted in red)
Worksheets(Worksheet.Index).Range("I37").Formula = "=RefOnPrevSheet("I39"))"

End Select

Next Worksheet

When I type the formula directly in the cell, and debug.print it, it shows exactly what I've put in quotes. How can I put this formula into my cells without returning a syntax error?

Cheers,

Bob

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