The [attached] spreadsheet I have been updating still produces an error message when I attempt to change the value in Cell S23 from any other value than "2%" or "1.5%". I would like to allow a student to also use any value between 1.5-2.0%, but it throws up "#DIV/0!" in a number of columns; which can not be "undone"!)
It appears the trigger is in the formula in Cells J80:J84. (The "4700" [Insurance premiums] would apply to 1.5%, or any value below 2.0%.)
I have tried three [unsuccessful] configurations of a formula, namely:
1. =IF($S$23=2%,SUM(J79+H80+I80)-N80,0)+IF($S$23=1.5%,SUM((J79+H80+I80)-N80)-4700,0)
2. =IF($S$23=2%,SUM(J79+H80+I80)-N80,0)+IF($S$23=1.5%,SUM(((J79-4700)+H80+I80))-N80,0)
3. =IF($S$23>2%,SUM(((J79-4700)+H80+I80))-N80,SUM(J79+H80+I80)-N80)
When I read through each element, it seems to make sense to me, but obviously there is something that I am missing.
I am not an Excel expert and have developed the sheet through a fair degree of Help Files and subsequent trial and error. If someone can identify how the formula should be written to overcome this isssue, I would be most grateful.
Regards,
Preceptor
It appears the trigger is in the formula in Cells J80:J84. (The "4700" [Insurance premiums] would apply to 1.5%, or any value below 2.0%.)
I have tried three [unsuccessful] configurations of a formula, namely:
1. =IF($S$23=2%,SUM(J79+H80+I80)-N80,0)+IF($S$23=1.5%,SUM((J79+H80+I80)-N80)-4700,0)
2. =IF($S$23=2%,SUM(J79+H80+I80)-N80,0)+IF($S$23=1.5%,SUM(((J79-4700)+H80+I80))-N80,0)
3. =IF($S$23>2%,SUM(((J79-4700)+H80+I80))-N80,SUM(J79+H80+I80)-N80)
When I read through each element, it seems to make sense to me, but obviously there is something that I am missing.
I am not an Excel expert and have developed the sheet through a fair degree of Help Files and subsequent trial and error. If someone can identify how the formula should be written to overcome this isssue, I would be most grateful.
Regards,
Preceptor