TKB Regularly

Lunchtime learning - Excel

Return to lunchtime menu

Do make sure that any tips and suggestions work as you expect them to in your own particular circumstances.

Google

Range name implicit intersections

An audience question at a recent IT Faculty Excel roadshow inspired an article for AccountingWeb's Excel Zone on the use of the MOD() function to determine which month periodic payments should appear in:

The formula required needed to refer to the dates and month numbers at the top of the columns, the month numbers in column B as well as the start date in A, the frequency in C and the amount in D:

=IF(MOD((E$2-$B3),$C3)=0,$D3,0)*($A3<=E$1)

There are a couple of issues with this formula. Firstly, it's not that easy to work out what is going on given the number of cell references involved. Secondly, in order to be able to copy it to other rows and columns, you have to be very careful with the use of the dollar signs to make the right elements of each cell reference absolute or relative.

A possible solution would be to use range names to make the formula easier to understand and to copy. We've set up the following range names:

StartDate: A3:A7
StartMonth: B3:B7
Frequency: C3:C7
Amount: D3:D7

MonthEnd: E1:P1
CurrMonth: E2:P2

Rather than rewrite our formula, we could use the Insert, Name, Apply option (Excel 2007: Formulas tab, Defined Names group, Define Name drop down, Apply) to apply our full list of names to our formula:

This gives us:

=IF(MOD((CurrMonth-StartMonth),Frequency)=0,$D3,0)*(StartDate<=MonthEnd)

Note that the we still need to manually change the $D3 reference within the IF() function to refer to the 'Amount' name:

Hopefully, the use of names rather than references makes it a little easier to understand how our formula works. MOD() returns the remainder after dividing its first argument by its second - so for months that are multiples of our desired frequency, MOD() will return zero. We also need to check that the start date is before the relevant month end.

Although our names refer to blocks of cells, rather than individual cells, Excel uses the intersection of the current row and column with the name to choose the right cell. The formula can be copied to all the other rows and columns without having to worry about absolute and relative references. Also, the formula is easier to check as it will be the same for every cell. Here we've used the Control+'allen key' shortcut to display formulae rather than results:

 

Get lots more Microsoft Office hints and tips

 

 

© 1998 The Knowledge Base. Disclaimer   Contact info@tkb.co.uk
Design by Reading Room Ltd 1998