Apply part of invoice to different object codes
Hello, I am trying to write a formula to give me the amount of an invoice I need to apply to 2 different object codes. GL code 6022 needs to be used for payments from $100,000-$125,000 and the other GL code 6021 is used for everything else. I have a current invoice column and a historical invoice column. So for example:
Current invoice is: $120,000
Historically invoiced: $80,000
So I need to pay out $40k, but the first $20k needs to be applied to the GL code 6021 and the second $20k needs to be applied to the GL code 6022.
I'd like to create 2 new columns, 1 for the 6022 GL and one for the 6021 GL and have a formula calculating the amount of the invoice to apply to each. TIA for your help with this!
Answers
-
Hey @Haley Gehris
Try this
GL code 6022
=IF(AND([Current invoice]@row >= 100000, [Current invoice]@row <= 125000), IF([Current invoice]@row > [Historically invoiced]@row, IF([Current invoice]@row - [Historically invoiced]@row > [Current invoice]@row - 100000, ([Current invoice]@row - [Historically invoiced]@row) - ([Current invoice]@row - 100000), [Current invoice]@row - [Historically invoiced]@row)))
GL code 6021
=IF(AND([Current invoice]@row >= 100000, [Current invoice]@row <= 125000, [Current invoice]@row > [Historically invoiced]@row), ([Current invoice]@row - [Historically invoiced]@row) - [GL code 6021]@row, IF(AND([Current invoice]@row > [Historically invoiced]@row, OR([Current invoice]@row < 100000, [Current invoice]@row > 125000)), [Current invoice]@row - [Historically invoiced]@row))
I wasn't quite sure what to do if the Historically invoiced amount was larger than the Current Invoice. You will see within the IF criteria I check for that, and don't allow a value to show up in either 6021 or 6022.
Will this work for you? If the logic isn't right, shout out to me.
Kelly
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!