I’m working on a billing spreadsheet where I track customer payments and need to show overpayments with a plus sign. When customers pay more than they owe, I want the excess amount to display as +15 instead of just 15.
I have a payment tracking sheet where the Amount Due column sometimes shows negative values when people overpay their bills. The Overpayment column needs to show these amounts with a plus sign prefix.
I attempted using a formula with CONCATENATE but it’s giving me weird results like ±15 instead of what I want.
=IF(D3<0,CONCATENATE("+",D3),"")
This approach isn’t working correctly because it’s treating the negative sign as part of the number. I need a way to convert the negative value to positive and then add the plus sign in front of it. Has anyone dealt with this kind of formatting issue before?
This happens because you’re trying to concatenate a plus sign with a negative number directly. Skip CONCATENATE or ABS - custom number formatting is way cleaner. Select your cells, go to Format > Number > More Formats > Custom number format, then enter: [<0]"+"0;General. This makes negative numbers show up with a plus sign and flips them to positive automatically. Your original negative values stay intact for calculations, but they display as positive with the plus sign. I’ve used this method tons - it beats formula approaches since it won’t mess with other calculations referencing these cells.
Your formula’s adding a plus sign to a negative number, so you get ±15 instead of +15. Use the ABS function to flip the negative to positive first. Try this: =IF(D3<0,CONCATENATE("+",ABS(D3)),""). ABS removes the negative sign, then you can add the plus and get +15. Or use this shorter version: =IF(D3<0,"+"&ABS(D3),"") - does the same thing. I use this trick in my financial spreadsheets to show overpayments properly.
yup, u got it! ur formula’s pulling negative vals but you want positive with a plus sign. just try this: =IF(D3<0,"+"&(D3*-1),"") – it flips the negative to positive and adds the plus sign. been using it for tracking payments, super handy!