Todays work on the V Planner consisted of building two smarter formulas. One that checked term dates, if they existed, if there was an end date for the current term and so forth. After getting that formula ironed out, I saw the need to start an Error Description sheet to define the error alerts that the user will see when something isn’t quite right. They are usually brought to your attention with the dreaded double exclamation marks. ( !! )

*Note: Compare this image with the last one.*

While working on the error sheet, I saw that Err2 could be eliminated with a smarter formula. If you want to read about how I went about writing the formula, continue reading.

To the V Planner I had added two optional places for weighted grades. These two additional places would *each* share a percentage of the final term grade. Either one or both are optional, a person only has to add a grade and a weight to one or both of the spaces and the grade will be calculated with the “main” term grade average.

This is fine, but the formula for the “main” term grade average was very simple and caused the term average to be incorrect if a weight was placed in the optional box before the grade was entered. I set up an alert to point that out, but that was not satisfactory because the homeschool parent might not feel like she was free to “plan ahead” with these optional percentages.

To fix it, the formula that calculated the difference of the percentages for the “main” term grade average needed to look for 3 conditions.

1. Are grades present in BOTH cells N55, N56?

2. Is a grade present in N55?

3. Is a grade present in N56?

Below are screen shots of how I worked this out.

Under one of the term calculators, I wrote a test formula in cell M58. I wrote it so that it would return “True” or “False.” Because it was checking three conditions, I used “True1” for the first condition, “True2” for the second condition, and “True3” for the third condition. I did this so I would be certain to place the real formulas in the right places.

1. Are grades present in BOTH cells N55, N56?

Yes – enter TRUE1. If False, go to next condition

2. Is a grade present in N55?

Yes – enter TRUE2. If False, go to next condition

3. Is a grade present in N56?

Yes – enter TRUE3. If False, go to next condition

There is no next condition, and the formula enters “FALSE” as typed in the formula.

### Next Write the Conditional Formula – Proper

This image shows the current formula. This puny formula subtracts the two weights from 1 (=1-P55-P56). In this case, the two other weights are 20% each and that makes the weight for the “main” term grade average count as 60% of the total term grade.

The remaining illustrations show the term average grader with the new conditional formula in place. The last picture shows the new formula.

This image shows that there are grades in both N55 and N56. The term grade weight is 60%, the other two are 20% each.

Compare this to the next two pictures.

This shows a grade in N55, but not in N56. Notice that the term grade weight is 80%.

This shows a grade in N56, but not in N55. Notice that the term grade weight is 80% as it should be. The next picture is the best one.

This picture illustrates best why the formula needed to check conditions. Even though a weight is added to the two optional term grade areas, the term grade weight is at 100%. As you saw in the three pictures above, when grades are added, the term grade weight changes appropriately.

Now I can remove the alerts for this particular formula. =)

Happy Homeschooling,

Applie says

Me likes. Me wants smarter formulas.

Donna Young says

LOL