Excel Tips - Display function arguments

Is the limit of 30 (thirty) arguments to the MAX() function only applicable to literal arguments? E.g. MAX( 1, 2, 3, ... ). I've read that it doesn't apply to a range, e.g. MAX( A1:A100 ), but what I'm wondering about is this:

Code:

MAX( IF( ISBLANK( D1:D10000 ), ROW( D1:D10000 ), 0 ) )

I needed a formula to find the last blank cell in a column, up to a certain row number. After a lot of fruitless mucking around with lookup functions like LOOKUP() and MATCH(), I finally found the solution I needed he
http://www.pcmag.com/article2/0,2817,1601616,00.asp

I've tested the code above on a range with more than 30 values and it worked (Excel 97), I just want to make sure this is a sound approach that I can count on to work with an arbitrarily high number of rows.

I am a tipped worker, and have created a spreadsheet to track my daily, weekly, and yearly earnings.

I have a sub-total row for each week, and a cumulative total row at the bottom of the spreadsheet, tracking my hours, target earnings, actual earnings, etc. Here is one of the formulas from the Cumulative Totals row:

=SUM(D32, D39, D47, D54, D61:D68, D75, D82, D89, D96, D103, D110, D117, D125, D132, D139, D146, D153, D160, D167, D174, D181, D188, D195, D202, D209, D216, D223, D231)

Every couple of weeks, I go into the cumulative totals row and enter the last few weeks weekly total cells. Tonight, I tried to enter D238, D245, and d252 into the formula. When i did, I got an error message saying:

"You've entered too many arguments for this function.

To get help with entering arguments for this function, click OK to close this message. Then, on the formula bar, click the equal sign button (located to the left of the equal sign in your formula)."

The thing is, there is no equal sign on the formula bar that i can see. the button located to the left of the equal sign in my formula is the fx button, and pressing it offers me no help.

Obviously, this formula is going to be much longer by the end of the year, there are 52 weeks in the year. What is the fix for this?

Also, what I do now is keep a blank week's worth of rows and columns at the bottom of the workbook (above the Cumulative Totals row). Each week, I copy that "module" and insert it below the last week, filling in the actual dates I work that week. Every month, or so, I then go into the formulas on the Cumulative Totals row and add in the weekly sub-total cells. Is there a better way to do this?

Is there a way to return to VBA each argument from a SUMIF worksheet function? For example, I use in VBA:

Code:

String1 = Range("F8").Formula

This returns String1 = "SUMIF(A1:A10,F1,B1:B10)"

I would like to be able to easily define a variables Arg1, Arg2, and Arg3, repectively, as "A1:A10", "F1", and "B1:B10".

I know how to search for the "(" and the "," and take the characters between them, but I prefer to avoid that.

Thanks!

I need to display the first, second and third in an array of percentages. I
tried using the 'Statistical Function' 'Large', first by using 'Insert
Function', selecting 'Large' then entering the Array [F11:F100], then the
order-value K [3]. The Formula-Bar displayed =LARGE(F11:F100 3)and when I
pressed Enter it returned an error-window, stating 'too few arguments' were
entered. I then tried entering the Formula directly [using the syntax
suggested in 'Help' (by copy and paste)]. The Formula-Bar displayed
=LARGE(F11:F100,3)and when I pressed Enter it again returned an error-window,
stating 'too few arguments' were entered. Neither of these [very similar]
syntaxes worked! Please help!

I have 4 user defined functions that if i type "=dso()" in a cell and
click the Insert Function button it brings up my argument window. I
have a toolbar with shortcuts to each of these 4 functions. When the
user clicks on the button, I would like the specified function added to
the activecell and then the argument window to appear. I currently
have the following code:

Sub InsertDSO()

ActiveCell = "=DSO()"
Selection.FunctionWizard

End Sub

which is called by the toolbar button. It adds the correct formula to
the cell, however when the function wizard is called, it brings up the
regular Insert Function dialog box and changes the cell to "==DSO()".
Once I exit out of the dialog box, the formula is changed back to the
correct one with only one =.

Is there anyway to programmatically call the Function Arguments window
that one can usually access by clicking on the Fx button?

Thanks so much!!!

Hello,

I have created a user defined function that requires four inputs. The function works perfectly.

The only thing I would like to add is help tips for each input variable.

So like with built in excel functions in the functions box.


eg under hyperlink function:

there is Link_location and Friendly_name when you go into that field the text below changes to explain what that variable is.

I have created VBA function and want behavior as Excel's built-in functions have when I start typing in a cell: a pop up with description, arguments and hint. Is there a way to add that same behavior?

I am looking for a way to take the weight input in cell "G2" and use it to calulate the below information:

Less than 150 pounds = 20 Points
150 to 174 pounds = 22 Points
175 to 199 pounds = 24 Points
200 to 224 pounds = 26 Points
225 to 250 pounds = 28 Points
250 to 275 pounds = 30 Points
275 to 300 pounds = 31 Points
300 to 325 pounds = 32 Points
325 to 350 pounds = 33 Points
Over 350 pounds = 34 Points

And place it in cell "D20".

I have been looking and I see that Excel only allows 7 arguments in an if statement. How would I go about putting all this into a function.

To clarify:

D20=G2<150 I need it to put 20 in the cell and so on for all of the above. Should this be an if statement at all?

I have a document that has programming and IF statements.

When a product is choosen in the PROD field depending on the DATES OWED range will tell how many deliveries there are. The credit due can't not go pass one month.

when I try to add more to the IF statement I get Too many arguments for this function.

Is there a simple macro I could use, or a way to do this. I still have to add three more products, I would like to be able to add unlimited products.

Good morning!

Background:

I have a spreadsheet that individuals enter information on the Account Info worksheet: Name, Company, Date, State etc. Pending the type of data entered, a data sheet becomes visible for the state selected. Within the sheet that becomes visible (with the state data) there are several columns with different categories. Each category pulls information from different tabs relative to the State selected and date.

For example:
Stewie Griffith opens the workbook (Account Info worksheet) and enters the basic information in the spreadsheet (seperate cells): Stewie Griffith, Quahog City Hall, 12/1/2010, RI. Stewie submits the information and a state data worksheet is visible. The spreadsheet lists the headers with categories and the state selected occupies the first row of the table. There are four data tabs (Quahog Data, Quahog Data New, Drunken Clam Data, Drunken Clam Data New) that feed this worksheet (through formulas in individual cells). Because Stewie selected Quahog City Hall, the date 12/1/2010 and RI, the worksheet receives the data from Quahog Data tab. If Stewie would have picked 1/2/2011, the data would be received from Quahog Data New.

Because there are two entities:Quahog and Drunken Clam. Stewie also has the option to enter information for the Drunken Clam too. Stewie starts a new work order entering information: Stewie Griffith, Drunken Clam, 12/1/2010, RI. Stewie submits the information and the same state data worksheet is visible (from the earlier scenario). Because Stewie selected Drunken Clam, the date 12/1/2010 and RI, the worksheet receives the data from Drunken Clam Data worksheet. If Stewie would have picked 1/2/2011, the data would be received from Drunken Claim Data New.

The issue:
Each category has cell references with IF statements to attempt to support the decision process. Unfortunately, I keep getting errors from the IF statement that I have written "Too many arguments".

Here is what I wrote:
Quote:

=IF($B56=""," ",IF($B56="N/A","N/A",IF($E$13="Family Guy",VLOOKUP(CONCATENATE($A56,$C$21), IF('Account Info'!$D$8