Drop down menu with totals

Hi all,

I would like to create a "drop down menu" of functions (sum, average, count, max, min, etc) that could be used to calculate a column in my spreadsheet. See below picture -- how do I do this? So for example, when you click "average" on the drop down menu, it calculates the average of all the numbers in the AP column. When you click "sum", it calculates the sum of all the values in the column AP.

dropdown menu.png

Let me know if this is unclear.

Thank you!

Originally Posted by AlKey

I think this can be done with creating a Validation list, CHOOSE function, and INDEX/MATCH formula

1. Validation list in G2
2. Formula in E2
3. Range: C4:C8

Attachment 439268

Formula in E2

Formula:

=CHOOSE(INDEX({1,2,3,4,5},MATCH(G2,{"Count","Sum","Average","Max","Min"},0)),COUNT(C4:C8),SUM(C4:C8),AVERAGE(C4:C8),MAX(C4:C8),MIN(C4:C8))

Attachment 439269

Thanks, except the way your solution has it requires the "result" to be in a different cell than the drop down menu. IN my example, the drop down menu is the same cell as the result. I will attach the workbook so you can see exactly what I am looking for. I would like to repeat it exactly how it is done in the attached. Check out row 2355 in the "BK Data Tape" worksheet:

Excel Test 2.xlsx


Any ideas?


Thank you all for your amazing help!!!

Originally Posted by AlKey

This was meant as a proof of concept. You can change cell references to whatever you like.

Thanks! Your method works fine except I get a "circular reference" warning whenever I try to make the drop down menu the same cell as the calculated result... trying to figure out to fix this circular reference issue. Any ideas?

Originally Posted by AlKey

Can't help without seeing the file. BTW, the file you uploaded earlier is password protected.

Sorry. Ophrys is the password. It is made-up information.