Discussion Re: Help with IF function in Excel
https://techcommunity.microsoft.com/t5/excel/help-with-if-function/m-p/2994815#M123324
Thank you! That helped with all the lines except for this one:<BR />76.00 72.00 4.00 53.00<BR />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?Mon, 22 Nov 2021 20:47:14 GMTdavebussiere2021-11-22T20:47:14ZHelp with IF function
https://techcommunity.microsoft.com/t5/excel/help-with-if-function/m-p/2994659#M123314
<P>I need help with the Owed by 11/23 column.</P><P> </P><P>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. </P><P> </P><P>I'm struggling making the <EM>Owed by 11/23</EM> column display the amount due. My current formula gives me an incorrect amount if they have a zero in the <EM>Money Owed</EM> 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.</P><P> </P><P>Here is my current formula:</P><P><STRONG><SPAN class="">=</SPAN><SPAN class="">IF</SPAN><SPAN class="">(</SPAN>'Popcorn Sales 2021'!$AF2<SPAN class=""><</SPAN><SPAN class="">125</SPAN><SPAN class="">,</SPAN><SPAN class="">125</SPAN><SPAN class="">-</SPAN>'Popcorn Sales 2021'!$AF2<SPAN class="">,</SPAN><SPAN class="">0</SPAN><SPAN class="">)</SPAN></STRONG></P><P> </P><TABLE border="1" cellspacing="0" cellpadding="0"><TBODY><TR><TD>Dues</TD><TD>Money Raised</TD><TD>Money Owed</TD><TD>Owed by 11/23</TD></TR><TR><TD><FONT color="#0000FF">0.00</FONT></TD><TD><FONT color="#0000FF">0.00</FONT></TD><TD><FONT color="#0000FF">0.00</FONT></TD><TD><FONT color="#0000FF">125.00</FONT></TD></TR><TR><TD>290.00</TD><TD>1188.50</TD><TD>-898.50</TD><TD>0.00</TD></TR><TR><TD>0.00</TD><TD>258.50</TD><TD>-258.50</TD><TD>0.00</TD></TR><TR><TD>190.00</TD><TD>116.50</TD><TD>73.50</TD><TD>8.50</TD></TR><TR><TD>190.00</TD><TD>134.50</TD><TD>55.50</TD><TD>0.00</TD></TR><TR><TD>290.00</TD><TD>707.07</TD><TD>-417.07</TD><TD>0.00</TD></TR><TR><TD><FONT color="#0000FF">122.25</FONT></TD><TD><FONT color="#0000FF">122.25</FONT></TD><TD><FONT color="#0000FF">0.00</FONT></TD><TD><FONT color="#0000FF">2.75</FONT></TD></TR><TR><TD>190.00</TD><TD>122.25</TD><TD>67.75</TD><TD>2.75</TD></TR><TR><TD>173.00</TD><TD>174.30</TD><TD>-1.30</TD><TD>0.00</TD></TR><TR><TD>173.00</TD><TD>245.70</TD><TD>-72.70</TD><TD>0.00</TD></TR></TBODY></TABLE><P> </P><P>Thank you so much for your help and assistance!</P><P> </P><P>Dave</P>Mon, 22 Nov 2021 20:04:13 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-if-function/m-p/2994659#M123314davebussiere2021-11-22T20:04:13ZRe: Help with IF function
https://techcommunity.microsoft.com/t5/excel/help-with-if-function/m-p/2994752#M123322
You could add on another IF to check if Money Owed = 0 and return 0, something like:<BR />=IF('Popcorn Sales 2021'!$AG2=0,0,IF('Popcorn Sales 2021'!$AF2<125,125-'Popcorn Sales 2021'!$AF2,0))Mon, 22 Nov 2021 20:35:32 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-if-function/m-p/2994752#M123322DKoontz2021-11-22T20:35:32ZRe: Help with IF function
https://techcommunity.microsoft.com/t5/excel/help-with-if-function/m-p/2994815#M123324
Thank you! That helped with all the lines except for this one:<BR />76.00 72.00 4.00 53.00<BR />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?Mon, 22 Nov 2021 20:47:14 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-if-function/m-p/2994815#M123324davebussiere2021-11-22T20:47:14ZRe: Help with IF function
https://techcommunity.microsoft.com/t5/excel/help-with-if-function/m-p/2995161#M123335
<P><LI-USER uid="1226619"></LI-USER> </P><P>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.</P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="DKoontz_0-1637620238873.png" style="width: 400px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/328785i95761273F736422D/image-size/medium?v=v2&px=400" role="button" title="DKoontz_0-1637620238873.png" alt="DKoontz_0-1637620238873.png" /></span></P><P> </P>Mon, 22 Nov 2021 22:31:29 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-if-function/m-p/2995161#M123335DKoontz2021-11-22T22:31:29Z