macro help

[mikesal57]

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

 

[buxtongt]

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.

 

[jim may]

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?

 

[mikesal57]

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

 

[buxtongt]

Not a problem, glad I could help.

 

[mikesal57]

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?

 

[Rick Rothstein]

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}))))

 

[mikesal57]

Rick...

Thank you all gentlemen

 

[buxtongt]

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