Select range / An easier way to implement formula over different ranges

This probably isn't possible but it would be great if it was, so please let me know.

You know how when you select a range of numerical cells, automatic calculations appear such as sum, average and count?

It would be really helpful if I could create a custom formula that did this.

For example, let's say we have a long range of daily stock returns. I can get the compound return from any two points by using this formula:

=product(index(A60:A100+1,0))-1

And I can get the average daily return using this formula:

=product(index(A60:A100+1,0))^(1/count(A60:A100))-1

It would be great to be able to quickly look at different returns between any two dates without the need to redo the formula every time.

Any solution?

Thank you

There are instructions at the top of the page explaining how to attach your sample workbook. Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Administrative Note:

Is you forum profile showing the version of Excel that you need this to work for?

Members will tailor the solutions they offer to the version of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent versions of Excel are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the release number in your profile (e.g. MS365 Version 2211). This is in the About Excel section further down the Account page.

Thanks.

I see no dates...

You said:

"And I can get the average daily return using this formula:

=product(index(A60:A100+1,0))^(1/count(A60:A100))-1

It would be great to be able to quickly look at different returns between any two dates without the need to redo the formula every time."

Since there were no dates in your sheet, knowing what you want isn't at all clear...

The problem may be that I gave too much information in my original post, but I was obligated to mention an example and include a worksheet.

It's really a yes or no question. I will try to rephrase.

Is there a way to create a custom formula that calculates automatically when you select a range, just like what happens already with sum, average and count at the bottom of any Excel sheet when multiple numerical cells are selected?

(Yes/No/Kind of?)

I suspect I'm way off the mark from what you want.

Only with VBA in a message box with the result of formula but i can't help you more with this.

I don't think you can customise the status bar at the bottom to the extent you are talking about but I took Glenn's spreadsheet above and added a macro.

Simply select the range of data you want in column A and press the button. If this works the message box can be tidied up later, I just wanted to check this is OK.

The arithmetic seems to work based on the formula's already provided.