IF statement

[leopardhawk]

Hello forum friends, I am having an issue with a formula that I need to modify. I think I am missing an IF statement? Not sure. I know that this is going to be a fairly complex formula and I will be grateful for any help that I receive. The formula in question resides on the 'income analysis' tab of my workbook in cell F83. Below is a user-defined selection on the 'tax credits' tab. This is where the data resides that I am using in my formula.

To explain (without the tab names), the formula in F83 should return an amount based on the following criteria:

The minimum value should never be less than $0.00
As long as F80 is less than G8, the value returned is E8 + F8 ($1366.52).
If F80 is greater than G8, it should return (E8) – (F80-G8)*.15
PLUS… it should add the following to the result
If F80 is greater I8, it should return (F8) – (f80-I8)*.0506

Excel 2016 (Windows) 32 bit
	E	F	G	H	I	J
5	age amount (base)		income thresholds
6	$ 7,494.00	$ 4,791.00	federal		bc provincial
7	federal	provincial	base	maximum	base	maximum
8	$ 1,124.10	$ 242.42	$ 37,790.00	$ 87,750.00	$ 35,660.00	$ 67,600.00
tax credits

Below is the 'income analysis' tab and another user defined selection which contains the formula that I need help with.

Excel 2016 (Windows) 32 bit
	E	F
80	Taxable Income	$ 35,100.00
81	Gross Taxes	$ 7,041.06
82	Basic Personal Amount	$ 2,350.86
83	Age Amount	$ 1,366.52
income analysis

Cell Formulas
Range	Formula
F83	=(MAX(0,('tax credits'!E8+'tax credits'!F8)-MAX(0,MIN('tax credits'!H8,F80)-'tax credits'!G8)*0.15))

As always, I remain gratefully yours, leopardhawk

 

[Special-K99]

You havent said what happens if F80 EQUALS G8, only what happens if its less than or greater than.

Based on your description use this

IF(F80<G8,E8+F8,E8-(F80-G8)*.15)+(F80>I8)*(F8-(F80-I8)*.0506)

 

[leopardhawk]

You havent said what happens if F80 EQUALS G8, only what happens if its less than or greater than.

Based on your description use this

IF(F80<G8,E8+F8,E8-(F80-G8)*.15)+(F80>I8)*(F8-(F80-I8)*.0506)

You are right, I should have said equal to or greater than. I tried your formula, it returns an error. Can someone please help?

Thanks

 

[Special-K99]

Not sure what happened there

IF(F80 < G8, E8+F8,E8-(F80-G8)*.15)+((F80 > I8)*(F8-(F80-I8)*.506))