This formula will check whether cell NS contains the item open. If so, it will return the debt age group (in days) of the corresponding apes (unpaid) sales invoice. (Remember that in column N, we will state open if the invoice is still unpaid). The debt age is expected to be available in column O. The age groups are: 0-30, 31-60, 61-90, 91-120 and 120. If cell NS does not contain open the formula will return NR (for Not Relevant) to the active cell (P5)
Later on, when you add data into cells in rows below it just copy the formula down.
In cell R5, write this formula:
=IF(Q5="open", CutOffDate B5. "MR")
This formula computes the age (in days) of unpaid suppliers invoice. (In cell Q5, we will state open if the supplier invoice is still unpaid). The Supplier invoice date is supposed to be available in cell B5 (Cutoffdate is a name we set for our cut-off date. Refer to the Note above on how to set Cutoffdate) So, if cell Q5 contains open, the formula will subtract the cut-off-date from the invoice date. This results in the number of days unpaid. If cell Q5 does not contain open, the formula will return (for Net Relevant) to the active cell (R5).
Later on, when you add more data into cells in rows below it just copy the formula down.
In cell S5, write this formula:
=IF(Q5="open", IF (R5<30 60.="" 90="" if="">120")))), "NR").
This formula computes the group for the credit age (in days) of unpaid suppliers invoice. In cell Q5, we will state open if the invoice is still unpaid. The credit age is supposed to be available in cell R5. The age groups are: 0-30, 31-60, 61-90, 91-120 and 120. If cell Q5 does not contain open, the formula will return NR (for Not Relevant) to the active cell.
Later on, when you add data into cells in rows below it, just copy the formula down.
12
In cell V5, write this formula:
=IF(ISNUMBER(U5), B5+U5, "NR").
This formula computes the due date for payment to supplier/by customer. The formula will add the term (in days, in cell U5) to the invoice date in cell B5. This will produce the due date. If cell U5 does not contain a number type, the formula will write NR to the active cell (V5).
Later on, when you add more data into cells in rows below it, just copy the formula down.
Freeze row 5:
You may want to freeze row 5 so that you can still see the data titles in row 4 when you scroll down.
Row 30000
This is the expected last row for the data area. This row number will appear in many formulas. Why 30000? Because the author expects that rows up to this number is sufficient to hold data for one year. Of course, if you find this row number insufficient, you can increase the number in every formula. Similarly, if your find that row 10000 is enough for your needs, you can change the 30000 in each affected formula to 10000.
To easily find and change every number or character (30000 in this case) in a formula, just use menu Edit/Replace (Refer to topic 27 on 'Shortcuts And Techniques For Working Better And Faster').
Data area
Data area is a rectangular area (bounded by a blank row on top, a blank column on the right, a blank row at the bottom and a filled column in the left) which is expected to contain the data. In this book, the data area
13
starts at cell A5 and ends at cell AC30000. Why 300000? Because the author expects that rows up to this number is sufficient to hold data for one year. Of course, if you find this number insufficient, you can increase the number in every formula. To easily change every number or character in a formula, just use the Edit Replace function. Similarly, if your find that row 10000 is enough for you, you can change every formula to 10000.
Formatting
Certain columns need to be formatted with appropriate format to accommodate proper types of data. Usually, data can be a 'text' type, 'date' type or 'number' type. Columns to accommodate 'date' type must be formatted with 'date' formatting. Columns to accommodate 'number' type must be formatted with 'number' formatting. Columns to accommodate text 'type' must be formatted with 'text' formatting.
This book does not show you how to format a cell since the author expects that you already know how to perform this basic task.
No comments:
Post a Comment