Forum Discussion
Problem with Logic
I have a situation where there is, both logically and in reality, circular logic. Here is a hypothetical example:
1. Total all the uses of resource "x". Call this "Column T" because the total is for each day.
2. Test each result of using resource "x" to make sure the result stays within an upper and lower limit.
3. If a result occurs outside the limit, calculate by how much the result missed the target and save that result for future reference.
4. To get future uses of resource "x" more accurate, determine ratios of preapplication"x" : resource"x"used : postapplication"x"differencefromtarget.
OK, that is difficult enough, but this is a biological process where "preapplication'x'" and "postapplication'x'" are pre-activity and post-activity measurements and "resource'x'" is the amount of biological "stuff" required to accomplish the activity. Because it is biological, there are many factors, thousands in fact, that are (a) unknown, like stress; (b) too interdependent to isolate and measure, like temperature and moisture and time between applications of resource"x" and time required to complete the activity all affecting other contributions to the activity thereby indirectly affecting the effectiveness of resource"x". For this reason, there are tables of "guesstimates" for resource"x" and the formulas for those guesstimates depend on column T.
The circular logic is: calculating totals to record in column T affects the guesstimates, and calculating new guestimates affects the totals to record in column T.
The only way I can think of to break the circle is to store the calculated totals real numbers rather than calculated results. Does Excel have the ability to look at just a formula's result and not at the formula as a reference to the result? In other words, is there a way to retrieve data from column T without that retrieval looking at the cells elsewhere in the workbook that contributed to column T's data?
3 Replies
- Hello, its possible for sure. Can you please provide some sample data so we can provide a solution for you?
Thank you for responding. My data spreads like ivy over seven worksheets and there are several dozens more to the formulas than what I'm giving you in this spreadsheet (like how weight factors in, other health issues, recording meal nutrient and calorie numbers, exercise, sleep, and so much more). Out of all of that, I created a simplified Workbook consisting of just one Worksheet with just one week of data.
The effort was tedious and lengthy, but worth it! By bringing the various variables together onto a single sheet, I discovered two things. First, I uncovered some errors, which, when corrected, made the data make a lot more sense to me. Second, I discovered the cause of the circular logic. It was indeed circular -- in my head, spinning around and around! I hadn't taken into account the fact that "Day 1" of tracking is very different logically than days 2, 3, 4..., 3658, etc.
So, I solved my own dilemma, but I wanted you to see the data as you requested. I've tried to include as much documentation (or explanation, however you want to think about my comments) as possible.Enjoy! (If such a verb can be applied to spreadsheets.)
Bruce Younggreen
- Juliano-Petrukio, all that said, my underlying question remains. Does Excel have a function or process or syntax that strips a calculated result from the formula that calculated it, thereby treating the data as naked data instead of calculated data?
