Can I create something like a macro in Numbers?
There is rich documentation and deep automation support for Numbers being automated with Automator and AppleScript. Here is a mid-tutorial link to where to save your scripts and shows the menu bar to trigger these scripts.

I could see you entering the formula or substituting / selecting the range for which to apply a function quite easily.


https://iworkautomation.com/numbers/script-menu.html


The nice thing about that site is that it has many tutorials as well to cover the scripting syntax / how to use the script editor and all the things you learn in Numbers are general to other apps that have scripting support on macOS - first party apps and third party apps.