Welcome to NexusFi: the best trading community on the planet, with over 150,000 members Sign Up Now for Free
Genuine reviews from real traders, not fake reviews from stealth vendors
Quality education from leading professional traders
We are a friendly, helpful, and positive community
We do not tolerate rude behavior, trolling, or vendors advertising in posts
We are here to help, just let us know what you need
You'll need to register in order to view the content of the threads and start contributing to our community. It's free for basic access, or support us by becoming an Elite Member -- see if you qualify for a discount below.
-- Big Mike, Site Administrator
(If you already have an account, login at the top of the page)
Trading: Primarily Energy but also a little Equities, Fixed Income, Metals and Crypto.
Frequency: Many times daily
Duration: Never
Posts: 5,057 since Dec 2013
Thanks Given: 4,409
Thanks Received: 10,225
I use a lot of user defined functions, saves rewriting long functions and makes reading my own spreadsheets a lot easier.
One word of caution, VBA functions actually compute significantly slower than raw excel formula's. It's unlikely anybody is connecting these spreadsheets to a latency dependent trading application, but if you are you probably want to stay away from functions.
I guessed as much. To do away with the complexity I tend to create extra columns, so instead of having one big formula I have a series of smaller formulae and then I hide the columns I don't need.
How would I write a function where the cell containing the narrowest range of the last four days turns red or something else to signal it in another way?
The spreadsheed attached does not contain dates, so I am going to assume that the 'last four sessions' are the 4 top ones.
What you are looking for is the type of Conditional Formatting highlighted in this screenshot (Top/Bottom rules --> Bottom 10 Items...)
Next, in the dialog box you change the Bottom 10 into Bottom 1 as follows
By applying this to the top 4 cells in column BA (Range) you will have the narrowest range highlighted in the color of your choice.
Notes:
I have an older Excel version but the advice above should work anyway
with this method every time you add a row you will need to readjust the conditional formatting, otherwise it may either expand to count more than 4 rows or it may start shifting down which you don't want
If you want something more automated you may need to look into VBA