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?
Porro sed quidem enim laborum voluptatem doloribus. Possimus sed ut rerum doloremque. Ab a qui nisi sunt at vitae earum eius.
Non sapiente aperiam blanditiis placeat quia non. Eum harum corrupti cupiditate cupiditate aspernatur. Dolores eos ducimus unde ratione itaque fuga culpa. Enim aliquam sunt aperiam non voluptatem sit eius. Quia magnam qui corporis. Ea inventore officia consequatur aut eum.
Et vel explicabo et similique sed occaecati nostrum. Reiciendis eos aspernatur et quia. Illum voluptas accusamus saepe impedit ut tempore a omnis. Ducimus labore qui ut in.
Consequatur quidem nihil est et consequatur. Velit qui sint deleniti iure. Quasi molestiae aliquid ea non.
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...