help with MAX value please

formratings

Board Regular
Joined
Apr 5, 2015
Messages
88
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
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,139
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 5, 2015
Messages
88
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,139
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 5, 2015
Messages
88

ADVERTISEMENT

08Aug.xlsb
BM
1Class x dsr
2 
3BP
41.40%
50.75%
60.75%
70.55%
81.25%
90.65%
100.70%
11 
120.67%
130.67%
141.00%
150.27%
160.43%
170.47%
180.50%
190.57%
200.47%
210.47%
22 
230.70%
240.47%
250.37%
260.47%
2712.40%
280.47%
290.67%
300.43%
310.67%
320.47%
33 
340.97%
350.97%
361.73%
370.93%
380.60%
390.93%
400.93%
411.40%
420.60%
430.47%
440.97%
45 
460.50%
470.70%
480.43%
490.43%
500.70%
510.47%
520.43%
531.13%
540.37%
551.00%
560.80%
570.80%
58 
591.50%
600.75%
6116.10%
621.45%
630.90%
640.90%
65 
660.93%
670.93%
680.33%
692.07%
700.93%
711.60%
721.20%
730.80%
740.93%
750.93%
76 
770.20%
780.43%
790.67%
800.57%
811.00%
820.97%
831.63%
840.43%
850.17%
860.43%
870.43%
880.43%
890.50%
90 
910.68%
920.28%
930.10%
940.48%
950.28%
960.87%
970.28%
980.82%
990.45%
1000.28%
1010.28%
1020.33%
1030.45%
1040.28%
105 
1060.67%
1070.67%
1080.73%
1090.23%
1100.37%
1110.23%
1120.37%
1130.40%
1141.03%
1150.40%
1160.40%
1170.57%
1180.17%
1190.43%
1201.50%
1211.40%
1221.67%
1230.27%
1241.67%
125 
1260.37%
1271.53%
1280.37%
1290.77%
1300.53%
1310.90%
1321.63%
1331.17%
1340.97%
1350.90%
1368.67%
1379.70%
1380.47%
1390.53%
1401.47%
14120.33%
14211.67%
1430.73%
1440.73%
1450.57%
146 
1470.90%
1480.93%
14911.07%
1500.40%
1519.80%
1520.47%
1530.90%
1548.20%
1551.93%
1561.93%
1570.47%
1580.77%
1591.47%
1600.47%
1610.17%
1620.53%
1638.73%
1640.90%
1650.73%
1660.57%
1670.57%
168 
1690.27%
1700.23%
1710.57%
1720.23%
1730.33%
1744.90%
1750.23%
1760.35%
1770.25%
1780.58%
1790.35%
180 
1813.33%
1821.75%
1831.75%
1841.50%
1852.67%
1863.25%
1870.92%
1881.25%
1892.42%
190 
1911.83%
1921.83%
1931.75%
1942.67%
19527.67%
1962.17%
1974.83%
1981.83%
1990.58%
2001.83%
201 
2022.60%
2031.55%
2047.35%
2050.70%
2060.50%
2070.75%
2080.50%
2091.05%
2102.55%
2110.60%
2120.55%
213 
2140.22%
2150.27%
2160.22%
2170.53%
2184.90%
2190.35%
2200.37%
2210.18%
2220.73%
223 
2240.35%
2250.85%
2260.33%
2270.33%
2280.33%
2290.47%
2300.48%
231 
2320.70%
2330.70%
2341.50%
2351.50%
2360.65%
2371.00%
2381.05%
2390.35%
2400.55%
2410.65%
242 
2431.20%
2440.35%
2450.95%
24615.55%
2470.65%
2480.95%
2491.05%
2500.45%
251 
2523.13%
2530.67%
2540.93%
2550.87%
2561.13%
2570.67%
2580.47%
2591.00%
260 
2611.83%
2620.92%
2630.00%
2640.00%
2650.33%
2660.00%
2671.50%
2680.67%
2693.08%
2700.00%
271 
2721.40%
2731.00%
2740.40%
2751.20%
2762.65%
2771.00%
278 
2790.30%
2800.12%
2810.30%
2820.35%
2830.35%
2840.48%
2850.30%
2860.15%
2870.30%
288 
2891.40%
29013.27%
2911.00%
2920.50%
2930.93%
2940.23%
2950.43%
2960.93%
297 
2981.17%
2990.93%
3000.47%
3010.60%
3021.30%
3030.43%
3040.43%
305 
3060.70%
3070.70%
3081.17%
3090.47%
3100.50%
311 
3120.35%
3131.40%
3141.30%
3159.15%
3161.15%
3171.10%
318 
3190.60%
3201.20%
3211.93%
3221.33%
3231.33%
32413.00%
32517.33%
326 
3277.97%
3280.83%
3290.83%
3300.90%
3311.10%
3320.53%
333 
3341.00%
33521.42%
3361.67%
3372.25%
3380.83%
3392.25%
3400.75%
3412.25%
3422.08%
343 
3441.33%
3452.58%
3461.92%
3472.08%
3482.25%
3491.92%
3502.08%
3512.25%
352 
3531.35%
3540.90%
3550.80%
3561.15%
3571.15%
3580.75%
3592.15%
3600.80%
361 
3622.25%
3631.92%
36458.92%
3651.33%
3662.67%
3671.92%
3682.58%
3691.33%
3702.58%
3711.67%
37216.25%
3732.08%
37415.67%
3751.33%
ignore
 

formratings

Board Regular
Joined
Apr 5, 2015
Messages
88
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,139
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Apr 5, 2015
Messages
88
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,139
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 5, 2015
Messages
88
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,952
Messages
5,599,033
Members
414,275
Latest member
Pungie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top