Multiple IFs/AND scenario

[mleahm77]

I need help with creating 2 formulas for the sheet pictured (I typed the info that I want momentarily, however, I need a formula to generate it in the future).

First, I need a formula that will look at G26 & K26 and place text in U30 based on the value in G26 & K26. I know what I want to do, but I do not know how to write the formula. I need the text "Average" to generate IF both G26 & K26 are less than an 8.0. IF G26 is an 8.0 and K26 is less than 8.0 I need the word to change to "Math Only". IF G26 is less than 8.0 and K26 is 8.0 I need the word to change to "Reading Only".

Second, I need a formula for Y30 to add 1.0 to the Average of G26 & K26 (already calculated in N26) if Both G26 & K26 are less than 8.0. IF G26 is an 8.0 then I only need to add 1.0 to K26, IF K26 is an 8.0 then I only need to add 1.0 to G26.

I hope this makes sense. Any and all help would be greatly appreciated.

 

[Joe4]

Welcome to the Board!

You can do what you want with nested IF statements and the AND function.
So your first formula should look something like this:
=IF(AND(G26<8,K26<8),"Average",IF(AND(G26=8,K26<8),"Math Only",IF(AND(G26<8,K26=8),"Reading Only","No Conditions Met")))

See here for more details on nested IF statements: IF function – nested formulas and avoiding pitfalls - Microsoft Support

Using this example and knowledge, you should be able to do the same for your second function.
Try it out and see how you do, it will be a good test!
If you run into any issues, post back and show us the formula you tried.

 

[Sergius]

Hello!

Book1.xlsm
	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
26	5				8
27
28
29
30															Reading Only				9
Sheet9

Cell Formulas
Range	Formula
U30	U30	=IF(AND($G$26<8,$K$26<8),"Average",IF(AND($G$26=8,$K$26<8),"Math Only",IF(AND($G$26<8,$K$26=8),"Reading Only","")))
Y30	Y30	=IF(AND($G$26<8,$K$26<8),AVERAGE($G$26,$K$26)+1,IF(AND($G$26=8,$K$26<8),$G$26+1,IF(AND($G$26<8,$K$26=8),$K$26+1,"")))

 

[mleahm77]

Welcome to the Board!

You can do what you want with nested IF statements and the AND function.
So your first formula should look something like this:
=IF(AND(G26<8,K26<8),"Average",IF(AND(G26=8,K26<8),"Math Only",IF(AND(G26<8,K26=8),"Reading Only","No Conditions Met")))

See here for more details on nested IF statements: IF function – nested formulas and avoiding pitfalls - Microsoft Support

Using this example and knowledge, you should be able to do the same for your second function.
Try it out and see how you do, it will be a good test!
If you run into any issues, post back and show us the formula you tried.

Thank you!!!! I got it to work!

 

[Joe4]

Excellent!
You are welcome.