-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
BlueHornet
Active Member
- Joined
- Apr 13, 2012
- Messages
- 341
Welcome to the board.
The arguments for the function are ( array, percent), so you don't have a lot of leeway built into the function to adjust that array. If you can figure a way to make a single argument for "array" that already discounts the two values you don't want, then that would do the trick, but that's going to be messy and likely hard to explain (or recall later) when you try to audit or duplicate the process. So my suggestion is to arrange a 'helper' array of values (because you'll still want to have an array consisting of four values), that has already removed the two cells you want to ignore, and operate on that array. Having that 'helper' array constructed on the worksheet somewhere, even if it's later hidden for cosmetic reasons, will make the thing easier to explain or do again in the future sometime.
The arguments for the function are ( array, percent), so you don't have a lot of leeway built into the function to adjust that array. If you can figure a way to make a single argument for "array" that already discounts the two values you don't want, then that would do the trick, but that's going to be messy and likely hard to explain (or recall later) when you try to audit or duplicate the process. So my suggestion is to arrange a 'helper' array of values (because you'll still want to have an array consisting of four values), that has already removed the two cells you want to ignore, and operate on that array. Having that 'helper' array constructed on the worksheet somewhere, even if it's later hidden for cosmetic reasons, will make the thing easier to explain or do again in the future sometime.
Upvote
0
Peter_SSs
MrExcel MVP, Moderator
- Joined
- May 28, 2005
- Messages
- 63,880
- Office Version
- 365
- Platform
- Windows
Welcome to the MrExcel board!
Could you use something like this? (formula to be confirmed with Ctrl+Shift+Enter, not just Enter before dragging across)
Could you use something like this? (formula to be confirmed with Ctrl+Shift+Enter, not just Enter before dragging across)
| 22 04 14.xlsm | |||||||
|---|---|---|---|---|---|---|---|
| A | B | C | D | E | |||
| 1 | 12 | 56 | 82 | 22 | |||
| 2 | 99 | 95 | 83 | 22 | |||
| 3 | 2 | 35 | 56 | 22 | |||
| 4 | 54 | 7 | 79 | 22 | |||
| 5 | 75 | 95 | 61 | 22 | |||
| 6 | 15 | 57 | 37 | 22 | |||
| 7 | |||||||
| 8 | Mean of middle 4: | 39 | 60.75 | 69.5 | 22 | ||
Mean | |||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B8:E8 | B8 | =AVERAGE(LARGE(B1:B6,{2,3,4,5})) |
| Press CTRL+SHIFT+ENTER to enter array formulas. | ||
Upvote
0
