OpenOffice.org Calc Project: Child Support Calculation Worksheet (Or Anything Else With Lots of Totals and Variables)

I was training some pretty advanced students this past week in Frankfort, Kentucky, and they do legal-oriented things, as well. So I put together an exercise where they created a child support calculation worksheet. Now, I know this is an emotional issue for a lot of people ;> but I want to emphasize that the features that I pulled together here can be used to calculate other things, as well. (You might also want to check out Scenarios.)

Here are  the features I combined.

Referencing a total in another sheet -- You can have one sheet to add up things like the components of the parents' income: W2 income, royalties, etc. You could have another sheet to add up things like expenses: rent, dog care, etc. Then copy the total cell, and choose Edit > Paste Special, Link, to take each of those totals to a third sheet and subtract expenses from income.

Whenever the components of the total in the first and second sheets are updated, then the totals in those sheets and in the third sheet will be updated.

Dropdown lists -- A common specification you'll need to make is who the custodial parent is. You can use a dropdown list under Tools > Validity to create a list that has two values, Mother and Father.

Choose Tools > Data Validity, and in the Criteria  tab, select List. Type the values you want, and don't allow empty values.

Then in the Error Alert tab, be sure the error checkbox is selected. Click OK.

Here's what the list looks like.

Using help under Data > Validity -- Sometimes it's hard to figure out what to enter.  In this case you can provide help. Choose Data > Validity, select the Input Help tab, choose to show input help, type what you want the user to read, and click OK.

The help then appears when the field is selected.

Using the IF function -- You create a condition, like IF B4 = "Mother".   Which means, if the custodial parent is the mother. You then create a statement for what you want displayed if that's true, and what you want displayed if that's false.

The following statement, in the field that displays what the father owes the mother per month, says that if the custodial parent is the mother and that if the father's net is more than the mother's, the father owes the mother the difference in their incomes, divided by 10, multiplied bythe number of children. Othewise, he owes her nothing. Obviously, the calculation for the amount owed should match the law rather than whatever I make up here on the fly.

=IF(B1="Mother"  AND  (C11>B11);(((C11-B11)/10)*B2);0)

Protecting cells -- If you're letting people download this spreadsheet to do their calculations, you don't want them changing any of the cells that contain calculations. You'd protect all the totals cells, the referenced cells, and the calculation for child support containing the IF statement, of course.

Select all the cells in a sheet and choose Format > Cells. In Cell Protection, turn OFF protection. (Bear with me.) Click OK.

Then just select the cells you want to protect (select - Ctrl to select multiple noncontiguous cells). Choose Format > Cells again, and turn ON cell protection. Finally, choose Tools > Protect Document > Sheet and enter a password to protect all protected cells in that sheet.

Here's the spreadsheet; it's just a hack so of course not legally binding but you can get the impression of the power of combining these features. The password is password if you'd like to take that off and play with the spreadsheet.

---

Comments

[guest]

Hi this was very helpful!

just one remark: validity is under data not under tools.

kind gregards