VBA examples : Adding an Excel conditional function

  This article deals with expandind the Excel family of conditional calculations functions. You probably already know COUNTIF et SUMIF and maybe already asked yourself if a function called, let's say, MAXIF existed ?

In a few words, these family of functions allow to perform a calculation within a worksheet range, but only using values that pass a given logical condition, for exemple are equal to a given value..

  You probably know the Excel conditional functions COUNTIF et SUMIF.

As a reminder, these functions allow performing simple computations -count or sum in these cases - on a set of worksheet cells that verify a logical condition

The logical condition can be a equality or inequality test to a number or a string.

  The first example is COUNTIF wich allows the user to compute a count of the rows in a selected range, checking a given condition : for example to compute a count of the cells of the range 'C1 :C5` having as text the word 'OK'

  The second example SUMIF allows this time the user to compute a sum based on values contained in a range, matching a given condition provided in another range of the same shape. For example, you can compute the sum of the values contained in range colonne 'B1:B10' accordingly to the cells of the range `C1 :C10` containing the value 'OK'.

  The second function SUMIF allows to compute a sum of values contained in a range of cells, verifying a given condition. As an example, the sum of the values in range "B1:B10" below for the cells of the range "C1:C10" where we can find a text value "OK".

-illustration_2.a-

	A	B	C	D	E
1		1	OK
2		4	OK
3		5	FALSE
4		7	OK
5		8	FALSE	=SUMIF(C1:C5;'OK',D1:D5)

-illustration_2.b-

	A	B	C	D	E
1		1	OK
2		4	OK
3		5	FALSE
4		7	OK
5		8	FALSE	12

  Functions allowing to compute for example a conditional min or maximum or average, ou even a Conditional Weighted Average (CWA) can be very usefull.

A conditional average could help us doing some simple data analysis. For example, you could calculate more easily an average score by score bucket. One practical application of such a function could be : for a group of students, compute the average score at an exam for the ones with more than x points.

  For sure with some row and column maniputlation, one can cope with the lack of this kind of function but it implies the following actions to compute a contditional average :

1 - Insert a column in the data area;
2 - Fill this column with the result of the test for each row;
3- Compute the result (using a pivot table ..).

All this means a lot of work (depending mainly on the quantity of data considered), not very reusable, compared to a function which could perform the tests and the calculations in a single cell. Quite logicaly, this function could handle 3 parameters the calculation data range, the (simple) condition to be tested, and test data range with no change in the data area.

   We assume that the range provided as arguments to these functions have the shape of a column. In other words, the function handle only data in single columns. Another development will aim to write functions that accept 2 dimension ranges as argument.

  Let's how to build this function with the help of Excel VBA. As we saw earlier 3 parameters will be necessary to compute a conditional maximum :

1-- Data range for the test (type Range);
2-- The test itself, a simple test like a specified value (type String);
3-- Data range for the calculation of the maximum (type Range).

  How to make the calculation :

1-- Test for errors (e.g. : control that the 2 parameter ranges are of the same shape );
2-- Initialize the variable dedicated to receive the result of the function;
3-- Test the cells from the criteria data area for a row verifiying the specified condition. Test if the value is higher than the actual value of this variable;
4--In this case update the result variable and then back to step 3.

' ***************************************************
' ***************************************************
' Function : fMaxif
' Object : compute the Maxif of a subset of column defined by a logical condition
' Condtional Max function, Max of range R for each row of range t verifying condition.
' Paramters : 
' T : Range on which the Max should be applied
' v : string defining a logical condition
' R : Range on which the logical condition should be applied
' version:0.01
'***************************************************	
Function fMaxif(T As Range, v As String, R As Range) As Variant

' Error process, initializations

On Error GoTo fMaxsi_err_exit

' sOut : result variable

sOut = "#N/A"

' bCverif : failed/ passed test indicator

bCverif = False

' i : a record counter

i = 1

' After the initialization and error management phase (not detailed here : but follows the rule of if(error) then goto fMaxsi_err_exit the error management code), we go on with the performing the test for each cell of the range given as argument

For Each cell In R

' Look for the first (if is there one) verifying the condition

If bCverif = False And ActiveSheet.Cells(cell.Row, t.Cells(1).Column).Value = v Then

	sOut = cell.Value

	bCverif = True

End If

' Look for another cell verifying the condition and asscoiated value is higher than the actual one (saved in the sOut variable) 

If bCverif=True And sOut < cell.Value And t.Cells(i, 1).Value = v Then

' If the associated value is higher then it is saved in sOut

	sOut = cell.Value

	End If

' We increment the record counter to test the following row;

	i = i + 1

	Next cell

'Copy of the result in the output variable

	fMaxif = sOut

	Exit Function

' Error Management : the value Error is set as result

	fMaxif_err_exit:

	fMaxif = "Erreur"

	End of the function

	End Function



' *********************END***************************

  Many functions can de derived from this one, for example : MinIf to compute a conditional least value, but also SumProductIf for example.