•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Calculate 5 cell values, drop highest and lowest value, and provide an average of the remaining 3 values
Our take
If you're looking to calculate the average of five cell values while dropping the highest and lowest scores, it’s essential to ensure your formula accurately captures all five inputs. The current formula seems to be limiting the selection to four cells, which may be due to the recent update in MS365 (2024). To achieve the desired outcome, consider revising your formula to include all five scores. This adjustment will enable you to calculate the average of the middle three values effectively.
The formula I currently have only seems to get 4 cells rather than the intended 5. It was working as-is, pre-upgrade to MS365 (2024). I feel like I'm missing something basic!
The formula should take 5 cells of the most recently input scores, drop the highest and lowest, then average the remaining 3 scores.
The current formula:
=AVERAGE(TAKE(FILTER(J9:AQ9, (J9:AQ9<>0)*(J9:AQ9<>" ")), 1, -4))
Editing to add a screenshot for context:
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Tagged with
#AI formula generation techniques#formula generator#Excel alternatives for data analysis#rows.com#natural language processing for spreadsheets#generative AI for data analysis#Excel compatibility#Excel alternatives#Excel#average#formula#scores#cells#highest#lowest#recently input#FILTER#MS365#TAKE#drop