Jump to content


Photo

Best Excel Trick

Self referential forumla :)

  • Please log in to reply
1 reply to this topic

#1 g__day

g__day

    Champion

  • Hero
  • 7,885 posts

Posted 05 July 2015 - 05:05 PM

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!


Talent + Integrity = Atomic!

#2 SquallStrife

SquallStrife

    Really knows where his towel is

  • Atomican
  • 17,939 posts

Posted 07 July 2015 - 09:28 AM

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, 07 July 2015 - 09:30 AM.

SyDjDDk.png [retro swim] | AzpUvwG.png @retroswimau | q5O6HgO.png +RetroSwim
四時半を待っています!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users