At a glance
In last month’s article we examined how to allocate values evenly across the year. Now I’d like to demonstrate a technique where you can allocate to specific months. The formulas and techniques can be applied to other areas of Excel.
The structure we will use is shown in Figure 1.
You specify how to allocate the amount; either monthly (column C) or annually (column D). You can’t enter in both columns.
In column E you can enter specific months to allocate amounts to. You enter the abbreviated month names separated by commas to allocate amounts to those months.
Columns F, G, H and I are what I call helper columns. They are used to perform part of the calculation so that the final calculation is shorter and simpler.
The formula in cell F2 determines how many months have been listed in column E. It counts the number of commas and adds one to it. Cells E9 and E10 have no months listed. The assumption for column E is that any blank or empty cells are evenly apportioned to each month.
The formula in cell F2 is:
=LEN(E2)-LEN(SUBSTITUTE(E2,",",""))+IF(E2="",12,1)
Excel doesn’t have a function that counts how many of a specific character is in a cell. In our case we need to count how many commas are in cell E2.The LEN function counts the number of characters in a cell.
The SUBSTITUTE function replaces one text string with another text string. Used together we can calculate how many commas are in cell E2.
=LEN(E2)-LEN(SUBSTITUTE(E2,",",""))
This part of the formula finds the number of characters in cell E2 and then deducts the length of the SUBSTITUTE function which takes the text from E2 and replaces all commas with a blank character. Using two quotation marks together represents a blank in Excel.This tells us how many commas are in the cell. The number of commas is one less than the number of months, so we must add one to the result to calculate how many months.
Blank cells in column E mean we will allocate to each month. A blank cell will obviously have zero commas, so we need to add 12 to the comma count formula if cell E2 is blank.
+IF(E2="",12,1)
The IF function on the end adds 12 if E2 is blank, or one if it isn’t. Column G is used to determine if the input value cells are valid. On any row there should only be one entry in columns C and D. You can’t enter both.
Column G ensures there is only one entry in columns C and D. The formula in cell G2 is:
=COUNTA(C2:D2)=1
COUNTA counts all the non-blank cells. This formula counts the number of entries in the two cells and compares the result to one. If it equals one TRUE will be displayed. If it doesn’t equal one it will display FALSE. Column G will be used in the main formula to zero the values if the entries are invalid. A row will be invalid if both cells are empty or both cells have entries. Note: the COUNT function only counts numbers or numeric values like dates. The COUNTA function counts everything, numbers and text. In most cases using COUNTA is preferable to COUNT.
Column H identifies if column E has an entry. The formula in cell H2 is
=E2<>""
This formula displays TRUE if cell E2 has an entry, or FALSE if it doesn’t.
Column I is used to calculate the monthly amount that will be allocated. The formula in cell I2 is:
=(C2+(D2/F2))*G2
The start of the formula is a straightforward calculation. We can add the values together because there should only be one entry between columns C and D. Why we need to multiply the result by G2 is explained below.
Logic multiplications
In Excel TRUE = 1 and FALSE = 0. When you multiply by TRUE you leave the value unchanged. When you multiply by FALSE you zero the value. Column G contains TRUE if the row entries are valid (only one value entry in columns C and D) or FALSE if invalid. If the row entries are valid the allocation calculation result is multiplied by one (TRUE) and is unchanged. On an invalid row the calculation result is multiplied by zero (FALSE) and zeroed.
Monthly allocation formula
The formula in cell J2, which has been copied down and across, is:
=IFERROR((SEARCH(J$1,$E2)>0)*$I2,($F2=12)*$I2)
The IFERROR function allows us to handle errors in Excel formulas. The function we need to identify if the month name in row 1 appears in column E is the SEARCH function. Unfortunately, the SEARCH function returns an error if it can’t find what it’s looking for. Because of that we have to use the IFERROR function to identify when the month name is not found.
(SEARCH(J$1,$E2)>0)*$I2
This part of the formula uses the SEARCH function to find if the month name from row 1 is in cell E2. The SEARCH function returns the character position of one text string within another text string, usually within a cell. By comparing the SEARCH result to zero, we can identify when the month name appears in cell E2. We have enclosed the logical test in parentheses to capture the TRUE or FALSE result. If the month name is in cell E2 a number will be returned that is greater than zero and so TRUE will be returned. This will be multiplied by the allocation amount from column I and that is the value that will be returned for that month.
If the month name is not in cell E2, then the SEARCH function generates an error. In that case the IFERROR function reverts to the second calculation as below.
($F2=12)*$I2
If the month name isn’t in column E then one of two things will occur. Either there is no allocation to be made, or we will be allocating evenly across all months because column E is blank.
If the value in column F is 12, then TRUE will be returned and the allocation amount will be returned. If the value isn’t 12, then the allocation amount will be zeroed and zero will be allocated.
Alternative IF function formula
This calculation could have been done using IF functions. Below is an alternate formula if you are more comfortable using IF functions instead of logic multiplications.
=IFERROR(IF(SEARCH(J$1,$E2)>0,$I2,0),IF($F2=12,$I2,0))
Allocation validation
Figure 2 shows the right-hand side of the report where we total and validate each row.
Column W is used to validate the total allocation. Column V sums all 12 months. The validation formula for cell W2 is:
=((C2*F2)+D2)=V2
This multiplies the monthly amount by the number of allocations and adds it to the annual amount. This formula works because there should only be one value between columns C and D. TRUE is displayed if the row’s allocation is correct and FALSE if it isn’t.
If you wanted to use an IF function to display a text message rather than TRUE or FALSE you could use:
=IF((C2*F2)+D2=V2,"OK","Error")
Differing amounts per month
Repeating the Department /Account combination on separate rows allows you to allocate varying amounts per month – see Figure 3.
The companion video and Excel file will go into more detail to demonstrate these techniques.
Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services to organisations around Australia. Questions can be sent to [email protected]
