help with MAX value please

[formratings]

hi could someone tell me how to add max value to be 5 on this formula please
=IF(M5="","",IFERROR(IF(OR(X5="",X5=" "),"",AE5*INDEX('Class Ratings'!$B$1:$B$9,MATCH(VALUE(X5),'Class Ratings'!$A$1:$A$9,0))),"")/6)

and in this formula which is average formula where any number can be blank,I don't want the blanks to count in the average
=AVERAGE(P44:P83,P89:P128,P134:P173,P179:P218,P224:P263,P269:P308,P314:P353,P359:P398,P404:P443,P449:P488,P494:P533,P539:P578,P584:P623,P629:P668,P674:P713,P719:P758,P764:P803,P809:P848,P854:P893,P899:P938,P944:P983,P989:P1028,P1034:P1073,P1079:P1118,P1124:P1163,P1169:P1208,P1214:P1253,P1259:P1298,P1304:P1343,P1349:P1388,P1394:P1433,P1439:P1478,P1484:P1523,P1529:P1568,P1574:P1613,P1619:P1658,P1664:P1703,P1709:P1748,P1754:P1793,P1799:P1838,P1844:P1883,P1889:P1928,P1934:P1973)

many many thanks in advance
cheers dave

 

[Fluff]

How about
=MIN(5,IF(M5="","",IFERROR(IF(OR(X5="",X5=" "),"",AE5*INDEX('Class Ratings'!$B$1:$B$9,MATCH(VALUE(X5),'Class Ratings'!$A$1:$A$9,0))),"")/6))

The average formula does not include blank cells.

 

[formratings]

How about
=MIN(5,IF(M5="","",IFERROR(IF(OR(X5="",X5=" "),"",AE5*INDEX('Class Ratings'!$B$1:$B$9,MATCH(VALUE(X5),'Class Ratings'!$A$1:$A$9,0))),"")/6))

The average formula does not include blank cells.

thanks but that didn't work, it just turned a green background cell white

and 2nd part about the average it just adds up to 0 all the time which I thought might be because of about 50% blank cells, not sure now why

 

[Fluff]

That should do exactly what you asked for, ie it should limit the your formula to return a maximum value of 5.
If that is not what you want the you had better explain.

 

[formratings]

08Aug.xlsb
	BM
1	Class x dsr
2
3	BP
4	1.40%
5	0.75%
6	0.75%
7	0.55%
8	1.25%
9	0.65%
10	0.70%
11
12	0.67%
13	0.67%
14	1.00%
15	0.27%
16	0.43%
17	0.47%
18	0.50%
19	0.57%
20	0.47%
21	0.47%
22
23	0.70%
24	0.47%
25	0.37%
26	0.47%
27	12.40%
28	0.47%
29	0.67%
30	0.43%
31	0.67%
32	0.47%
33
34	0.97%
35	0.97%
36	1.73%
37	0.93%
38	0.60%
39	0.93%
40	0.93%
41	1.40%
42	0.60%
43	0.47%
44	0.97%
45
46	0.50%
47	0.70%
48	0.43%
49	0.43%
50	0.70%
51	0.47%
52	0.43%
53	1.13%
54	0.37%
55	1.00%
56	0.80%
57	0.80%
58
59	1.50%
60	0.75%
61	16.10%
62	1.45%
63	0.90%
64	0.90%
65
66	0.93%
67	0.93%
68	0.33%
69	2.07%
70	0.93%
71	1.60%
72	1.20%
73	0.80%
74	0.93%
75	0.93%
76
77	0.20%
78	0.43%
79	0.67%
80	0.57%
81	1.00%
82	0.97%
83	1.63%
84	0.43%
85	0.17%
86	0.43%
87	0.43%
88	0.43%
89	0.50%
90
91	0.68%
92	0.28%
93	0.10%
94	0.48%
95	0.28%
96	0.87%
97	0.28%
98	0.82%
99	0.45%
100	0.28%
101	0.28%
102	0.33%
103	0.45%
104	0.28%
105
106	0.67%
107	0.67%
108	0.73%
109	0.23%
110	0.37%
111	0.23%
112	0.37%
113	0.40%
114	1.03%
115	0.40%
116	0.40%
117	0.57%
118	0.17%
119	0.43%
120	1.50%
121	1.40%
122	1.67%
123	0.27%
124	1.67%
125
126	0.37%
127	1.53%
128	0.37%
129	0.77%
130	0.53%
131	0.90%
132	1.63%
133	1.17%
134	0.97%
135	0.90%
136	8.67%
137	9.70%
138	0.47%
139	0.53%
140	1.47%
141	20.33%
142	11.67%
143	0.73%
144	0.73%
145	0.57%
146
147	0.90%
148	0.93%
149	11.07%
150	0.40%
151	9.80%
152	0.47%
153	0.90%
154	8.20%
155	1.93%
156	1.93%
157	0.47%
158	0.77%
159	1.47%
160	0.47%
161	0.17%
162	0.53%
163	8.73%
164	0.90%
165	0.73%
166	0.57%
167	0.57%
168
169	0.27%
170	0.23%
171	0.57%
172	0.23%
173	0.33%
174	4.90%
175	0.23%
176	0.35%
177	0.25%
178	0.58%
179	0.35%
180
181	3.33%
182	1.75%
183	1.75%
184	1.50%
185	2.67%
186	3.25%
187	0.92%
188	1.25%
189	2.42%
190
191	1.83%
192	1.83%
193	1.75%
194	2.67%
195	27.67%
196	2.17%
197	4.83%
198	1.83%
199	0.58%
200	1.83%
201
202	2.60%
203	1.55%
204	7.35%
205	0.70%
206	0.50%
207	0.75%
208	0.50%
209	1.05%
210	2.55%
211	0.60%
212	0.55%
213
214	0.22%
215	0.27%
216	0.22%
217	0.53%
218	4.90%
219	0.35%
220	0.37%
221	0.18%
222	0.73%
223
224	0.35%
225	0.85%
226	0.33%
227	0.33%
228	0.33%
229	0.47%
230	0.48%
231
232	0.70%
233	0.70%
234	1.50%
235	1.50%
236	0.65%
237	1.00%
238	1.05%
239	0.35%
240	0.55%
241	0.65%
242
243	1.20%
244	0.35%
245	0.95%
246	15.55%
247	0.65%
248	0.95%
249	1.05%
250	0.45%
251
252	3.13%
253	0.67%
254	0.93%
255	0.87%
256	1.13%
257	0.67%
258	0.47%
259	1.00%
260
261	1.83%
262	0.92%
263	0.00%
264	0.00%
265	0.33%
266	0.00%
267	1.50%
268	0.67%
269	3.08%
270	0.00%
271
272	1.40%
273	1.00%
274	0.40%
275	1.20%
276	2.65%
277	1.00%
278
279	0.30%
280	0.12%
281	0.30%
282	0.35%
283	0.35%
284	0.48%
285	0.30%
286	0.15%
287	0.30%
288
289	1.40%
290	13.27%
291	1.00%
292	0.50%
293	0.93%
294	0.23%
295	0.43%
296	0.93%
297
298	1.17%
299	0.93%
300	0.47%
301	0.60%
302	1.30%
303	0.43%
304	0.43%
305
306	0.70%
307	0.70%
308	1.17%
309	0.47%
310	0.50%
311
312	0.35%
313	1.40%
314	1.30%
315	9.15%
316	1.15%
317	1.10%
318
319	0.60%
320	1.20%
321	1.93%
322	1.33%
323	1.33%
324	13.00%
325	17.33%
326
327	7.97%
328	0.83%
329	0.83%
330	0.90%
331	1.10%
332	0.53%
333
334	1.00%
335	21.42%
336	1.67%
337	2.25%
338	0.83%
339	2.25%
340	0.75%
341	2.25%
342	2.08%
343
344	1.33%
345	2.58%
346	1.92%
347	2.08%
348	2.25%
349	1.92%
350	2.08%
351	2.25%
352
353	1.35%
354	0.90%
355	0.80%
356	1.15%
357	1.15%
358	0.75%
359	2.15%
360	0.80%
361
362	2.25%
363	1.92%
364	58.92%
365	1.33%
366	2.67%
367	1.92%
368	2.58%
369	1.33%
370	2.58%
371	1.67%
372	16.25%
373	2.08%
374	15.67%
375	1.33%
ignore

 

[formratings]

hope the above was correct, some of the % are above 5% which means the range is too big which is why I need to limit the range to Max 5

 

[Fluff]

You said 5 not 5%, it's a big difference
Also posting a column of data with absolutely no information doesn't help.
Try changing the 5 in my suggestion to .05, if that does not do what you want, then you will gave to give a full explanation of what you are trying to do.

 

[formratings]

sorry not sure what it is you want me to add
guess this is what you mean but still doesn't work
=MIN(0.5,IF(M5="","",IFERROR(IF(OR(X5="",X5=" "),"",AE5*INDEX('Class Ratings'!$B$1:$B$9,MATCH(VALUE(X5),'Class Ratings'!$A$1:$A$9,0))),"")/6))

 

[Fluff]

For 5% it should be 0.05 not 0.5.
If that doesn't work then you will need to explain exactly what you are trying to do & why it doesn't work.

 

[formratings]

I am trying to cap the max value at 5 so if a value is 12.6% I want it to show as 5 which is the max allowable value