Search the Community
Showing results for tags 'Self referential forumla :)'.
Found 1 result
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!