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).
Unless this contains confidential info--in which case just create one with dummy data--please post a copy of the actual spreadsheet on OneDrive or GoogleDrive, with a link here granting access to it. (Images have limited utility.)
You started to write your formula, but somehow or other it ended up being incomplete in your post, leaving us nothing to work with.
The file can be found here, it's not particularly elegant
https://1drv.ms/x/s!AlAwFcyIJ3fVgedWduabFH6pOOkJ9g?e=yLm0xE
Appreciate your assistance
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).
- mathetes - perfect thank you so much! I had dabbled with IFS last night in a separate file with no luck. Your approach using SUM is much cleaner than my thinking also.
Thanks again, I hope I can contribute back to this community as you haveYour 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.