I need help with the Owed by 11/23 column.
I'm a scout leader and we have a spreadsheet to track popcorn sales, dues owed, etc. Scouts are requested to sell a certain amount of popcorn to cover their dues and we prorate their dues according to what they've earned in sales. We have some families who choose to forgo sales and just write a check. For those that owe money, we ask that a combination of sales or payments totaling $125 be made by November 23rd. The balance will be paid NLT February. Some dues were less than 125 based on financial needs, etc.
I'm struggling making the Owed by 11/23 column display the amount due. My current formula gives me an incorrect amount if they have a zero in the Money Owed column and have dues less than $125. The table below has two examples of my problem. The first row received a full scholarship yet it shows him owing 125. The seventh row's dues were only 125.25 yet it shows $2.75 being owed. All other rows are calculating correctly. Basically, I need to show that if the Money Owed column is zero then the Owed by column should reflect zero.
Here is my current formula:
=IF('Popcorn Sales 2021'!$AF2<125,125-'Popcorn Sales 2021'!$AF2,0)
| Dues | Money Raised | Money Owed | Owed by 11/23 |
| 0.00 | 0.00 | 0.00 | 125.00 |
| 290.00 | 1188.50 | -898.50 | 0.00 |
| 0.00 | 258.50 | -258.50 | 0.00 |
| 190.00 | 116.50 | 73.50 | 8.50 |
| 190.00 | 134.50 | 55.50 | 0.00 |
| 290.00 | 707.07 | -417.07 | 0.00 |
| 122.25 | 122.25 | 0.00 | 2.75 |
| 190.00 | 122.25 | 67.75 | 2.75 |
| 173.00 | 174.30 | -1.30 | 0.00 |
| 173.00 | 245.70 | -72.70 | 0.00 |
Thank you so much for your help and assistance!
Dave
