Quick Links ▼

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Worksheetfunction nesting max if

	98.1 % recommend us
	More live stats...

The UK's most regular
instructor-led training courses.

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions

Worksheetfunction nesting max if

Resolved · Medium Priority · Version 2007

Reply Tue 31 May 2011, 21:36Delegate Tony said...

Tony has attended:
Excel VBA Intro Intermediate course
Excel VBA Advanced course
Access Intermediate course
Access Advanced course

Worksheetfunction nesting max if

I am try to create array formula in Excel VBA. In excel, I would use the formula

{max(if(name1=a2,name2))}

name1 and name 2 are named ranges

i am happy using worksheetfunction for max or if but do not know how to do a nested function to get max if

For upcoming training course dates see: Pricing & availability

Reply Wed 8 Jun 2011, 09:26Trainer Stephen said...

RE: Worksheetfunction nesting max if

Hi Tony

Thanks for the question

Could you clarify for me whether you wish to create a customised function procedure, or if you wish to use the above as the formula property of a cell object?

Thanks

Stephen

Excel articles

· Copy And Paste In Excel
· How To Create A Real Line Graph In Excel 2010 Rather Than A Line Chart
· Interesting Lifestyle Applications for Microsoft Excel
· Charting Your Success With Excel 2007
· Microsoft Excel's Best Kept Secret

Please browse our web site to find out more about
advanced excel course + london and other Microsoft training courses.

Excel tip:

Sum Up All the Values in A Column

If you want to quickly calculate the Summed values of all cells in a column in Excel 2003 normally you would use the SUM formula. (eg if you wanted to calculate the values in Column C rows 10 to 25) the formula would be:

=SUM(C10:C25)

However, if you keep adding values to column C you would keep having to modify the above SUM formula which can get quite annoying.

To get around this you can sum all the values in a column using the following formula:

=SUM(COLUMN:COLUMN)

Which, in our example, would be:

=SUM(C:C)

NOTE You cannot place this formula in column C, or else Excel 2003 will show a circular reference error.

The formula must be placed in any other column, EXCEPT the one being calculated.

View all Excel hints and tips