Why doesn't my If And formula work?

[DonnaJoh]

Good Morning:

I am trying to utilize the following If And formula but it doesn't seem to be working.

=IF(AND(O8>="95",<="100","25",IF(AND(08>="85",<="94","20",IF(AND(O8>="70",<="84","15",IF(AND(O8>="50",<="69","10",IF(AND(O8>="30",<="49","5",IF(O<="29","0","null")))))))))))

I am trying to display a set value of points for a score with a range.

Score Range		=		# of Pts
95to100% 85to94% 70to84% 50to69% 30to49% <=29%				25 20 15 10 5 0

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

How can I fix this?

 

[FormR]

Yes, that at least solves the Sum question.....thank you sooo much!!

Thinking more, wouldn't just =AVERAGE(Q8:AB8) suffice?

Now if I can only resolve the original equation issue

Did you try?

=5*(MATCH(O8,{0,30,50,70,85,95}/100)-1)

 

[DonnaJoh]

Yes, I tried both options posted and "#Name?" is displayed in both events. The value of O8 is 96% which is tabulated from the following formula:=SUM(Q8:AB8)/COUNT(Q8:AB8)

 

[FormR]

"#Name?" is displayed in both events.

If you are not using Excel with english language settings then you will need to make the necessary translation for the MATCH() function.

Excel 2013/2016
	O	P
8	96%	25
Sheet1

Cell Formulas
Range	Formula
P8	=5*(MATCH(O8,{0,30,50,70,85,95}/100)-1)

 

[DonnaJoh]

I just tried " =5*(MATCH(O8,{0,30,50,70,85,95}/100)-1)

" and it only displayed a 0 when O9 was 89%. This should have yielded an answer of 20, not 0. ​

 

[DonnaJoh]

I am using excel with English language settings

 

[FormR]

and it only displayed a 0 when O9 was 89%. This should have yielded an answer of 20, not 0

Hi, O8 or O9? - btw it returns 20 for me with 89% in O8.

Excel 2013/2016
	O	P
8	89%	20
Sheet1

Cell Formulas
Range	Formula
P8	=5*(MATCH(O8,{0,30,50,70,85,95}/100)-1)

I am using excel with English language settings

I'm guessing the #NAME ? error has resolved itself

 

[DonnaJoh]

Dahhh...that's it! I tried it in a different cell and did not change the formula to reflect the cell I was now in. Yes, it now works!!! Thank you so very much!!! You all made my day!!!!

 

[DonnaJoh]

Yes the

=5*(​

MATCH(O8,{0,30,50,70,85,95}/100)-1​

) formula resolved the #NAME ? error problem. Thank you very much!!!​

 

[markmzz]

Hi DonnaJoh,

Try the formulas below too:

In O8 and copy down

=IFERROR(AVERAGE(Q8:AB8),"")

In P8 and copy down

=IF(O8="","null",LOOKUP(O8,{0,0;0.3,5;0.5,10;0.7,15;0.85,20;0.95,25}))

	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
6
7		Average	Result	Data01	Data02	Data03	Data04	Data05	Data06	Data07	Data08	Data09	Data10	Data11	Data12
8		65%	10	46%	73%		69%	37%	74%	95%		95%	70%		22%
9			5	96%	46%		32%	34%	70%	32%		7%	51%		52%
10			null
11		39%	5	11%	58%		18%	56%	43%	36%		19%	66%		41%
12		58%	10	74%	8%		64%	12%	66%	89%		20%	96%		89%
13		47%	5	26%	21%		95%	100%	36%	0%		56%	60%		29%
14		64%	10	73%	70%		64%	11%	77%	84%		87%	32%		82%
15		36%	5	33%	63%		18%	21%	49%	1%		60%	65%		16%
16			null
17		51%	10	35%	73%		10%	52%	92%	20%		84%	96%		1%
18
***	**	*******	*******	******	******	******	******	******	******	******	******	******	******	******	******	**

<tbody>
</tbody>

Markmzz

 

[DonnaJoh]

I got it working with the =5*(

MATCH(O8,{0,30,50,70,85,95}/100)-1 formula. Thank you

​