Forum Discussion
Named table referencing visible values
Going by all the named ranges and sophisticated formulas, you clearly know your way around Excel. But perhaps you've not used the IFS function before. In general, it makes for slightly clearer logic when multiple conditions need to be tested.
Here's what I came up with in your column M. (I've attached a revised copy, as I downloaded your file to my computer to work directly on my own computer rather than in the cloud.)
=IFS([@[Payment Number]]=1,"Opening Balance",[@[End Balance]]>0,"",SUM(K18:K19)=0,"",[@[End Balance]]=0,"Closing Balance")Essentially, I added a different condition, that being two rows sequentially (the current and the prior) totaling $0.00, to leave the row blank. That way, only the first instance of "End Balance" being zero gets the result "Closing Balance"
You could combine the second and third conditions with an OR, if desired, since they both lead to the "" result, but that might make it harder to debug in the future.
FYI, in case you're not familiar with IFS, the formula "stops" once it reaches a condition that is met, so the sequence in which you list them is critical. That's why the sum of those two rows being zero precedes the one that elicits "Closing Balance" -- otherwise, every row with zero gets "Closing Balance" (as you discovered).
Going by all the named ranges and sophisticated formulas, you clearly know your way around Excel. But perhaps you've not used the IFS function before. In general, it makes for slightly clearer logic when multiple conditions need to be tested.
Here's what I came up with in your column M. (I've attached a revised copy, as I downloaded your file to my computer to work directly on my own computer rather than in the cloud.)
=IFS([@[Payment Number]]=1,"Opening Balance",[@[End
Balance]]>0,"",SUM(K18:K19)=0,"",[@[End
Balance]]=0,"Closing Balance")Essentially, I added a different condition, that being two rows sequentially (the current and the prior) totaling $0.00, to leave the row blank. That way, only the first instance of "End Balance" being zero gets the result "Closing Balance"
You could combine the second and third conditions with an OR, if desired, since they both lead to the "" result, but that might make it harder to debug in the future.
FYI, in case you're not familiar with IFS, the formula "stops" once it reaches a condition that is met, so the sequence in which you list them is critical. That's why the sum of those two rows being zero precedes the one that elicits "Closing Balance" -- otherwise, every row with zero gets "Closing Balance" (as you discovered).
Thanks again, I hope I can contribute back to this community as you have
Your approach using SUM is much cleaner than my thinking
Truth be told, that was about my fifth attempt at writing a condition that worked cleanly in your circumstance. I often find, especially with multiple conditions, that it takes some "playing" with alternative methods.
Thanks again, I hope I can contribute back to this community as you have
You're very welcome. I realized earlier today that it's been nearly three years since I stumbled on this place, posted a question, then hung around to answer some. With other regular folks who answer, I've discovered this can be habit forming. And a great learning experience too, working to solve problems such as the one you presented today.