Jump to content
Forum upgrade is live! Read more... ×
Sign in to follow this  
g__day

Best Excel Trick

Recommended Posts

Excel is a rather powerful tool if you can figure out how to structure your data and analysis. Complex nested forumale, conditional formatting, macros, pivot tables and graphs, internet imports, conditional mathematical operations on tables based on their contents, indirect addressing of tables, complex string manipulations, complex table look ups.

 

Just pondering what is your favourite trick you have found with excel?

 

I just found a new one - have a cell only change its value if a formulae is true, else have it store whatever it has stored. Better still MS Excel help said it couldn't be done without an external macro or at best is indeterminate!

 

Basically I had table with days of the week Sun to Sat and real time web data pours into cells depending on which day of the week it is and what the hour is. Think of a 24 * 7 array, where only if its that day of the week and that hour I want the cells content to update from the web, else stay as it is. Under Options - allow recursive (cyclic, self- referential functions) with 1 iteration, then in say Cell G14 have its value be =if(day and time means this cell is selected, web based call(), G14). This means the cell will have an unusual value (normally zero) until the condition is met, then its value will be set to function call. And once time moves on and the condition is no longer true its value will be its value! So it makes it work like bubble memory.

 

Took me ages to find this out - that if() has three parameters (condition, value if true, value if false), how to make a cell self referential with no errors, and confirm that once the real time (time()) function is satisfied the cell is initiated and then stays that way, until the condition is meet again same time next week!

Share this post


Link to post
Share on other sites

My favourite trick came from the use of INDIRECT() to make references that "stick", so even if you drag the referenced cell somewhere, formulas referencing it aren't updated.

 

The conventional wisdom is to use absolute references, like $A$1, but if you drag-move the contents of A1 somewhere else, the absolute reference will follow the contents to the new location, and update formulae referencing $A$1.

 

Whereas if you use =INDIRECT("A1"), then the formula will always reference A1.

 

Where I got stuck was trying to do a combination of things. I wanted to use the OFFSET() function, which requires a cell reference to "start from", but I also wanted to be able to autofill the formula without breaking it, AND for the whole range to be transportable, AND for the formula to remain unchanged if I deleted/inserted/moved the referenced cells.

 

So I worked out a formula that will ALWAYS return reference to its own cell.

 

=INDIRECT(ADDRESS(ROW(),COLUMN()))

 

And hence a formula that will always return the value to the left of the cell it's in.

 

=OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1)

 

This formula won't break if either the cell containing the formula is moved, OR if the cells it's referencing are moved. Bulletproof.

Edited by SquallStrife

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×