This formula that I was given by my coordinator does not distinguish between,,,

[laurabach]

=(G22-I22-J22-L22-K22)-H22+N21

This credit shows either a balance owing or a credit balance. It only shows it as black and does not distinguish which one it is. It should be in brackets or change color to distinguish whether the balance is an owed amount or a credit amount. Can someone please tell me how to make it show?

[judgeh59]

are talking formatting?....because you can right click in the cell(s) and go to format currency and choose the amount of decimals and if you want negatives colored and in parathesis...

[laurabach]

Hi Ernest,
I tried that, but it doesn't change the number at all. It is still black and a positive number, when it should be a credit

[judgeh59]

Hi Laura,
Can you attach an excel file with some non-confidential info using that formula so I can see what is going on?....have you used evaluate formula under the Formula tab....it's in 2010 and I think in 2007 also....it will show you exactly how excel is evaluating the formula....very cool for walking through formulas....HTH

[laurabach]

Okay, I have attached it. Please tell me how to fix it so that the $38.00 shows as a credit.
Thank you

[judgeh59]

so I am seeing a $38 in the Column "N" that is not a negative number and a -$82 in the cell about that....I can right click and format to 2 decimals and set to -$82 to ($82.00) and the numbers are red....Is that what you are looking for?....and I'm assuming we are talking about column "N"...I'm almost gone for the day and I'll be in class tomorrow....so, I may not get back to this until Thursday....

[laurabach]

I finally understand. So in other words, when they owe money, it comes up as a negative and turns red. When they have a positive balance, it stays black. Is this correct?

[laurabach]

I'm hoping that I can ask another question. The formula that I got off here on the I cells is correct, the only problem is, if they don't have a late fee, I have to put a 0 in the cell to carry it over to the balance or M and N cells. When I put the 0 in, the formula goes away and therefore when the sheet is full, I have to re-enter the formula. I hope this makes sense. If they do not owe any late fee and pay before the 4th, is there a way to tweak the formula so that we don't have to enter anything in the I cell and the formula stays intact?

[arlu1201]

laurabach,

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[judgeh59]

Yes, Laura that seems to be correct....AND you can format any of the currency columns to either go RED on negative or go both RED and put () around the number indicating negative...IE: standard accounting notation....HTH....

[Hercules1946]

Hello Laura
Im thinking that your putting the 0 in col I to prevent the #VALUE! error in M and N, which loses the formula in Col I ?
If instead, you insert the IFERROR function in M and N as Ive done, then it will not show any error in M and N even when I is blank.
Also, Ive altered the format in col N to show negatives in red, two decimals and a $ sign.
I also noticed that the formula in M was repeated in N with 2 extra cells so I simplified to (e.g) N21= M21 -H21 + N20.

Hope that helps
Herc

Hello again
Ive made some further amendments because just not showing the error wasn't enough. The problem is that I needs a numeric value at some stage
if the totalling is to work. However I wouldn't want $0.00 and part totals all down my sheet.
So..... in Mark II my column I has a zero built into the formula (if thats how the calc comes out). However, it doesn't show (and neither do the totals)
if theres no value in Col H. (You can change to another rule if you like.

[arlu1201]

Ernest & Hercules,

Your posts do not comply with Rule 7 of our Forum RULES. Please do not ignore requests by Administrators, Moderators and senior forum members regarding forum rules.

If you are unclear about the request or instruction then send a private message to them asking for clarification. Do not post a reply in a thread where a moderator has requested an action that has not yet been complied with e.g Title change or Code tags...etc