Excel formula for changing inputs by referencing self cell
I remember learning about an excel formula that you should use when you are changing an input(almost like doing a sensitivity analysis) and you need to reference the output cell itself so that it doesn’t change.
Apologies not sure how to best phrase it but if anyone knows what I am referring to please help with how the formula should be set up!
Goalseek?
Assume this cell is written in A1:
=IF(input condition=true, [the value you are looking for], A1)
So basically cell A1 will always reflect a single output value even if you change the input cell.
Based on the most helpful WSO content, it seems you're referring to a scenario where you want to perform sensitivity analysis or iterative calculations while referencing the same cell without causing circular reference errors. A common approach for such cases is to use the INDIRECT() function.
The INDIRECT() function allows you to reference a cell dynamically without directly linking to it, which can help avoid issues when inputs or outputs are being adjusted.
Example Setup:
You can use the formula:
=INDIRECT("A1")This will reference Cell A1 indirectly, ensuring that changes to the input/output don't disrupt the formula.
If you're performing sensitivity analysis, you can combine INDIRECT() with other functions like CHOOSE() or OFFSET() to dynamically adjust inputs and outputs.
Let me know if you need further clarification or examples!
Sources: EXCEL cheats megathread, EXCEL cheats megathread, Excel Formulas you use constantly on the job?, Waterfall template attached, Year 1 in consulting - tips, tricks, advice, and unspoken rules.
That's a not a good explanation of what you're looking for.
Yeah bro I thought the same thing. I can't comprehend what he is asking and am pretty good at Excel.
It’s what non profit posted above. Circular way to populate a sensitivity / data table. Not a great practice but works in a pinch.
Looks abusive
CHOOSE?
Est ipsam deserunt non saepe. Recusandae libero fuga quos. Reprehenderit rerum quibusdam excepturi id dolore.
Laborum et ut non dolorem ipsam. Ducimus totam inventore occaecati sunt. Molestiae consequatur sit saepe doloribus iste culpa et. Ut amet inventore aut ut. Sunt quia ex qui quos nam consequatur. Omnis necessitatibus voluptas eveniet natus.
Itaque et autem ipsum aspernatur consequatur rerum. Omnis asperiores magni nostrum totam.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...