SOLVED

Help with IF function

New Contributor

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)

 

DuesMoney RaisedMoney OwedOwed by 11/23
0.000.000.00125.00
290.001188.50-898.500.00
0.00258.50-258.500.00
190.00116.5073.508.50
190.00134.5055.500.00
290.00707.07-417.070.00
122.25122.250.002.75
190.00122.2567.752.75
173.00174.30-1.300.00
173.00245.70-72.700.00

 

Thank you so much for your help and assistance!

 

Dave

3 Replies
You could add on another IF to check if Money Owed = 0 and return 0, something like:
=IF('Popcorn Sales 2021'!$AG2=0,0,IF('Popcorn Sales 2021'!$AF2<125,125-'Popcorn Sales 2021'!$AF2,0))
Thank you! That helped with all the lines except for this one:
76.00 72.00 4.00 53.00
It should show $4 in the column with the 53. Any thoughts? I can manually correct this (and a couple others) but curious what the correct fix would be?
best response confirmed by davebussiere (New Contributor)
Solution

@davebussiere 

Would this work? If dues are less than 125, it uses the difference of that number instead of 125. The Max just makes it so if they owe a negative amount it rounds up to 0.

 

DKoontz_0-1637620238873.png