# macro help

#### mikesal57

##### Board Regular
Hi ..

Is there a macro that can help me with getting the average of the best 3 out of 5 in a range of numbers....if the range doesnt have 5 then adjust with what it has

ex..

a....65 66 54 33 72
b....57 57 42 70
c....55 45 22 65 80
d....78 34
e....66 66 54 23 56
f....55 66 77

thxs
Mike

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
There has to be a more elegant way, but this should do the trick at least:

=IF(COUNT(A1:E1)<3,AVERAGEA(A1:E1),AVERAGE(IF(ISERROR(LARGE(A1:E1,1))=TRUE,"",LARGE(A1:E1,1)),IF(ISERROR(LARGE(A1:E1,2))=TRUE,"",LARGE(A1:E1,2)),IF(ISERROR(LARGE(A1:E1,3))=TRUE,"",LARGE(A1:E1,3))))

Hope this helps.

Its sometimes (if not always) best to provide your expected results, so we can eliminate any confusion. Can you so provide us the expected results (manually) from the given data?

There has to be a more elegant way, but this should do the trick at least:

=IF(COUNT(A1:E1)<3,AVERAGEA(A1:E1),AVERAGE(IF(ISERROR(LARGE(A1:E1,1))=TRUE,"",LARGE(A1:E1,1)),IF(ISERROR(LARGE(A1:E1,2))=TRUE,"",LARGE(A1:E1,2)),IF(ISERROR(LARGE(A1:E1,3))=TRUE,"",LARGE(A1:E1,3))))

Hope this helps.

thank you ..works like a charm..

you all are amazing...

Not a problem, glad I could help.

I get an error if there is only one entry

<table border="0" cellpadding="0" cellspacing="0" width="448"><col style="width:48pt" span="6" width="64"> <col style="width:48pt" width="64"> <tbody><tr style="height:12.75pt" height="17"> <td style="height:12.75pt;width:48pt" height="17" width="64">
</td> <td style="width:48pt" align="right" width="64">90</td> <td style="width:48pt" align="right" width="64">87</td> <td style="width:48pt" align="right" width="64">89</td> <td style="width:48pt" align="right" width="64">88</td> <td style="width:48pt" width="64">
</td> <td class="xl75" style="width:48pt" align="right" width="64">89</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">81</td> <td align="right">77</td> <td align="right">76</td> <td align="right">85</td> <td align="right">82</td> <td>
</td> <td class="xl75" align="right">83</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">82</td> <td align="right">85</td> <td align="right">69</td> <td align="right">93</td> <td align="right">89</td> <td>
</td> <td class="xl75" align="right">89</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">85</td> <td align="right">87</td> <td align="right">86</td> <td align="right">75</td> <td align="right">69</td> <td>
</td> <td class="xl75" align="right">86</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">72</td> <td align="right">76</td> <td align="right">78</td> <td align="right">75</td> <td>
</td> <td>
</td> <td class="xl75" align="right">76</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">88</td> <td align="right">80</td> <td align="right">88</td> <td align="right">73</td> <td align="right">60</td> <td>
</td> <td class="xl75" align="right">85</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">62</td> <td align="right">67</td> <td align="right">78</td> <td align="right">66</td> <td align="right">62</td> <td>
</td> <td class="xl75" align="right">70</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">81</td> <td align="right">77</td> <td align="right">81</td> <td align="right">83</td> <td align="right">81</td> <td>
</td> <td class="xl75" align="right">82</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">
</td> <td>
</td> <td align="right">92</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl75" align="right">18</td> </tr> </tbody></table>
last line shows avg of 18?

There has to be a more elegant way, but this should do the trick at least:

=IF(COUNT(A1:E1)<3,AVERAGEA(A1:E1),AVERAGE(IF(ISERROR(LARGE(A1:E1,1))=TRUE,"",LARGE(A1:E1,1)),IF(ISERROR(LARGE(A1:E1,2))=TRUE,"",LARGE(A1:E1,2)),IF(ISERROR(LARGE(A1:E1,3))=TRUE,"",LARGE(A1:E1,3))))
This would probably be the more "elegant" way buxtongt was referring to (and it works if there is only one number, or no numbers for that matter)...

=IF(COUNT(A1:E1)=0,"",IF(COUNT(A1:E1)<4,AVERAGE(A1:E1),AVERAGE(LARGE(A1:E1,{1,2,3}))))

Last edited:
Rick...

Thank you all gentlemen

I knew it! It's those pesky curvy brackets that throw me every time. Thanks Rick

Replies
5
Views
340
Replies
4
Views
267
Replies
5
Views
487
Replies
3
Views
246
Replies
6
Views
751

1,203,760
Messages
6,057,204
Members
444,914
Latest member
Mamun12345

### 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.

### Which adblocker are you using?

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

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