Need help with gradually diminishing amount

[leopardhawk]

Hello forum friends, I have a formula that needs adjustment so that it will show a gradually diminishing dollar amount based on data in an adjacent cell. The formula as it stands is shown below.

Excel 2016 (Windows) 32 bit
	E	F
62	OAS	$ 687.00
income analysis

Cell Formulas
Range	Formula
F62	='cpp & oas'!D24

What I need is for the formula in F62 to return an amount that is also reduced by 15 cents for every dollar that the amount in F71 exceeds a $75,910 threshold. If the amount in F71 exceeds $122,843 , the amount returned in F62 is completely reduced to zero.

Thank you!

 

[Snakehips]

Try...

=IF(F71>122843,0,IF(F71>75910,'cpp & oas'!D24-(0.15*(F71-75910)),'cpp & oas'!D24))

 

[leopardhawk]

Thanks Tony, however, I'm now getting a 'circular reference' error with the new formula for reasons that remain unknown to me. I will tell you that since my original post, I added a few lines above on the sheet, but that should not make any difference at all. I have double-checked and tested all my other formulas and they are working fine. Below, I have pasted what I am now dealing with.

Excel 2016 (Windows) 32 bit
	E	F
69	OAS	$ -
70	Average Gross Monthly Payroll (if applicable)	$ -
71	Rental (other) Income	$ -
72	Total Monthly	$ -
73	Yearly	$ -
74	% Income	#DIV/0!
75
76	Annual Withdrawals	$ -
77	Monthly	$ -
78	Yearly	$ -
79	% Income	#DIV/0!
80
81	Taxable Income	$ -
income analysis

Cell Formulas
Range	Formula
F69	=IF(F81>122843,0,IF(F81>75910,'cpp & oas'!D24-(0.15*(F81-75910)),'cpp & oas'!D24))
F72	=SUM(F67:F71)
F73	=SUM(F72)*12
F74	=SUM(F73/F32)
F76	=SUM(D76)*12
F77	=SUM(F72+D76)
F78	=SUM(F77*12)
F79	=SUM(F78/F32)
F81	=SUM(F78)

Thanks!

 

[Snakehips]

I'm afraid only fully understand your data and how you are wanting to manipulate it.
I have tried to follow your verbal description.

Originally you wanted a formula in F62 calculating on the values in F71 and D24 of cpp & oas sheet?
You appear to have added rows above that shift that formula to F69 a shift of +7?
Is the old F71 value also +7. ie F78 now OR F81 as you have above?
I'm assuming that D24 on other sheet is unchanged?

 

[Snakehips]

I think I am seeing the circular reference issue....

The formula in F81 is a function of F78 which is a function of F77 then F72 which is the Sum of F67:F71
The range F67:F71 includes F69 which is a function of F81 as you have it above.

 

[leopardhawk]

Is there a workaround?

 

[Snakehips]

I'm afraid only you will know that in respect of your data set. Not if calculating a cell value requires using that value to calculate itself.

 

[leopardhawk]

okay, thanks anyway Tony.

 

[leopardhawk]

Okay, I may have found a workaround, at least for this computer and this copy of Excel 2016. I could use some advice as to whether this will work for other people I may want to share the workbook with, considering that they are using a different computer and copy of Excel.

I went to File/Options/Formulas and enabled 'Iterative Calculations' and set the maximum to 45. Now the formula that you sent me is working without giving me the circular reference error.

Will other users have to do what I did in File/Options or is this now set with this workbook??

Thanks!