Forum Discussion
Macro without hard coding data rows
This might be a basic question... I want to record a macro using Excel on mac ver 16.64. The calculations are simple. E.g.: the average of column C. The challenge is that on some data files i hav...
Thanks Hans.
Average function was a bad example. I use countifs and other functions too. Basic problem is that the range can be different in different files. How best to handle that?
Average function was a bad example. I use countifs and other functions too. Basic problem is that the range can be different in different files. How best to handle that?
Please provide some details.
See attached sample file. Rows 1 has variable names, rows 2-84 have data.
I begin analysis in cell BC100..you can see the formula in it. Then move down to cells BC101, BC 103 and so on and enter the formulae. I copy the formulas in BC100-BC106...to adjacent columns till col DV. Then i transpose the results matrix and sort (as you will see in the sheet).
I can record a macro to do all the above but i want the macro to adjust so that it does the calculations with all rows of data. In this case data is in rows 2-84. In the next case, data may be in rows 2-156. How do i make the macro adjust and use all available rows of data?
Thank you for your time
The attached version contains a formula solution.
- Thank you for your response. I opened the file and see #DIV/0! in all cells in the sheet you added. Is something wrong with the formulae you created?