Excel Noob Stuck On a Problem

[darrow34]

Hello everyone! So I'm really new to Excel formulas so I'm sure I'm doing everything the hardest possible way. Below is my mini sheet and here's what I'm trying to do:

In Column O "Salary Actual" I'm attempting to populate salaries by the number of years in Column I. I've used IF functions to set a scale that returns the salary in Column J if the year is 9 or less, Column K if the year is between 10 and 19, and Column L if the year is 20 or more. What I haven't been able to figure out is how to get the MA (masters) in Column M to properly reflect in my formula. I'm sure there's a better way to do it but in the meantime I have set up Column N to reflect "Y" for yes if the person has a masters (MA) but I can't figure out how to make it translate into my "Salary Actual" column. I think it will show in my mini sheet that the IF formula is returning results in Column O correctly up until I have a "Y" in Column N. So, essentially, I need to find a way to calculate the MA column into my Salary Actual column when there is a Y in column N. Any help anyone can provide would be most appreciated. This is a real world scenario and I really need these numbers to add up correctly. I also need it to be dynamic because I will be adding more "Y"'s in column N here in the next week.

Thank you for your time and attention in this matter.

TEACHER LIST BY SENIORITY_with Numbers.xlsm
	I	J	K	L	M	N	O
1	Years	BA	BA+10	BA+20	MA	Masters	Salary Actual
2	9	$51,893.14	$53,835.68	$55,852.80	$57,940.75		$51,893.14
3	7	$48,216.85	$50,021.44	$51,893.14	$53,835.68		$48,216.85
4	3	$41,628.60	$43,187.11	$44,801.55	$46,479.37		$41,628.60
5	26	$67,112.84	$69,610.93	$72,220.87	$77,366.19		$72,220.87
6	15	$60,110.73	$62,359.01	$64,693.05	$67,112.84		$62,359.01
7	9	$51,893.14	$53,835.68	$55,852.80	$57,940.75	Y	$51,893.14
8	17	$62,359.01	$64,693.05	$67,112.84	$69,610.93		$64,693.05
9	3	$41,628.60	$43,187.11	$44,801.55	$46,479.37		$41,628.60
10	20	$62,359.01	$64,693.05	$67,112.84	$69,610.93		$67,112.84
11	12	$57,940.75	$60,110.73	$62,359.01	$64,693.05		$60,110.73
12	19	$62,359.01	$64,693.05	$67,112.84	$69,610.93	Y	$64,693.05
24.25

Cell Formulas
Range	Formula
I2:I12	I2	=DATEDIF(C2,TODAY(),"y")
J2:J12	J2	=VLOOKUP($I2,'24_25 Salary'!$A$2:$E$42,2)
K2:K12	K2	=VLOOKUP($I2,'24_25 Salary'!$A$2:$E$42,3)
L2:L12	L2	=VLOOKUP($I2,'24_25 Salary'!$A$2:$E$42,4)
M2:M12	M2	=VLOOKUP($I2,'24_25 Salary'!$A$2:$E$42,5)
O2:O12	O2	=IF([@[Years ]]<=9,[@BA],IF([@[Years ]]<=19,[@[BA+10]],IF([@[Years ]]>=20,[@[BA+20]],IF(AND([@[Years ]]>=0,SEARCH("Y",[@Masters])),[@MA]))))

 

[dermie_72]

With the if statements, it stops once there is a true, so you need to check Masters first if that's the most important check. Try this formula (Adjust it for the names in your table).

=IF(F2="Y",E2,IF(A2<10,B2,IF(A2<20,C2,D2)))

 

[JMan1984]

Please try:

=IF([@Masters]="Y",[@MA],IF([@Years]<10,[@BA],IF([@Years]<20,[@[BA+10]],[@[BA+20]])))

 

[JMan1984]

Hi dermie_72,

It looks like you beat me to the punch. How do you put the excel formula in a box like you did if you don't mind me asking? I am new here. Thanks.

 

[dermie_72]

just above where you enter your text click on the one that has fx XLS this will put the code that enables the formatting.

=Add your formula here

Otherwise you can manually add the code like the attached.

 

[darrow34]

Thanks for your help folks. The solution you provided worked! My colleagues and I thank you.