Changing cell value produces "#DIV/0!" error message in columns

[Preceptor]

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

[JeffreyWeir]

Looks like the error is first introduced in T22, which divides T21 by J85. And as per your formulas above, J85 is going to be zero any time S23 isn't either 2% or 1.5%. So I suggest take a look at T22 and work out what you want to have happen in that cell. I've got no idea what this cell is for.

Also, S22 is problematic because of a #DIV/0, but it's not used for any major calculations on the sheet. Although it is used on the asset allocation sheet.

[Preceptor]

Looks like the error is first introduced in T22, which divides T21 by J85. And as per your formulas above, J85 is going to be zero any time S23 isn't either 2% or 1.5%. So I suggest take a look at T22 and work out what you want to have happen in that cell. I've got no idea what this cell is for.

Also, S22 is problematic because of a #DIV/0, but it's not used for any major calculations on the sheet. Although it is used on the asset allocation sheet.

Thank you very much for taking the time to examine the sheet and provide an insightful critique!

T22 is critical for the pension aspect of "Doug's" (Case Study) retirement pension. This will be his tax-free earnings return, less the Fund Management Fee (FMF) [T23], for when he starts his pension [H18/Column H]. His total yearly pension I20/Column I is underpinned by this return (plus Inflation).

The current situation is that a student must make a decision between recommending Super Fund 1 (2.0% FMF) or Super Fund 1 (1.5% FMF). Being cleverer than me, some students have thought-up "Plan C... none of the above", aka: combine elements of "both" Funds. This means that the sheet (Cell) should actually be flexible enough to input a value "between" 1.5% and 2%. (I could suggest to the "thinkers" that they come up with an "average" percentage.)

Cell S22 is not so much of a problem as it simply identifies the difference between the returns Doug would achieve (in that column) if he were to continue to work, as against the return he gets on a pension.

I "thought" I was on to a good thing with formula #3 =IF($S$23>2%,SUM(((J79-4700)+H80+I80))-N80,SUM(J79+H80+I80)-N80) as it seemed to provide the flexibility I was seeking, i.e., any value "between" 1.5% and 2%, without the need for a "bucket-load" of +IF statements. Unfortunately, it does not work.

Kind regards,
Preceptor

[JeffreyWeir]

Hi Preceptor. You can likely write a formula to handle a range. If I find some time I'll take a look. No promises...been procrastinating on some other stuff that I've got to do first ;-)

Cheers

Jeff

[Hercules1946]

Hi Preceptor
I hope Jeffrey doesn't mind me looking at this. Ive modified your formula calculations so that you don't get an error if a % value outside the range 1.5 to 2.0 is entered. If this happens, the user is asked to cancel or re-try. If the value is in the allowed range, any apart from 2.0% will include the deduction of $4700.

HTH

Hercules

[JeffreyWeir]

Lucky you did look, because I'd clean forgotten! Thanks Hercules, and sorry Preceptor.

[Preceptor]

Hi Preceptor
I hope Jeffrey doesn't mind me looking at this. Ive modified your formula calculations so that you don't get an error if a % value outside the range 1.5 to 2.0 is entered. If this happens, the user is asked to cancel or re-try. If the value is in the allowed range, any apart from 2.0% will include the deduction of $4700.

HTH

Hercules

Hi Hercules,

Let me start by stating "thank you" for taking an interest in my problem, and also state that I'm seriously impressed with the Dialog Box that pops up when the wrong value is typed-in.

Have written the above, I have two questions in regards to the amended formula (which I "almost" understand):

1. Why does the Dialog Box pop-up when I try "any" value between, and including, 1.5% and 2%? (Reading the formula, it seems to me the "OR..." bit makes sense.)
2. The reference to "...E1-7..." in the formula: what does it do?

Cheers,
Preceptor

p.s. Apology happily accepted Jeff — Your insight obviously triggered Hercules's curiosity!

[Hercules1946]

Hi Preceptor
I made a couple of errors - It was a bit late in the evening. Ive sorted the validation problem in the new attachment. The validation doesn't accept values as a percentage.
I meant to say 1E-7 (0.0000001) to give a tiny value instead of zero in an error situation. This gets round division by zero.