Excel question

[bh24524]

Hello, I have a formula set up to subtract an amount of a couple columns, which are payment receipts and refunds, from another column which is the Total column. The last column is of course the remaining balance which uses accounting as the number display. I have one conditional format set up in the remaining balance column so that if there is any balance due above $0, it will display the total in blue. My question is why do some columns that have a zero balance display differently? Some display as "0.00"; some display as "-"; and some display as "(0.00)". I would have thought that dragging the formula concerning those columns to the rows below would have kept a consistent format of how the zeros are displayed. The weird thing is that the ones that display the zeros as "0.00" are colored blue, but the other two formats are black text(which is the color I want for when it shows zero). I'd like to show the zeros all as dashes in the accounting number display. Is there a way to have it consistent?

 

[gnrboyd]

I believe what you are seeing is that your formula results go beyond 2 digits to the right of the decimal. I can't really explain the hows/whys myself, but sometimes the way Excel calculates you will have a result like 4.99999999 or 5.000000001 when you can't see a valid reason for it. (I'm sure if you search the board, you can find more about this phenomenon.) Anyway, your accounting format shows 2 digits to the right of the decimal but the actual cell value doesn't change. In the example above, both would show 5.00 with the accounting format but neither are actually equal to 5.00.

In your situation, the ones that show up "0.00" are actually greater than zero. The ones that show up "(0.00)" are less than zero. The ones that show up as "-" are exactly zero. (You can change the format and increase the decimal places by several places to see the actual value for yourself.)

To counter this effect, what I usually do is to add the ROUND formula to force the result to two digits to the right of the decimal.

So =B1-A1 would be changed to =ROUND(B1-A1,2)

Hope this helps.

 

[bh24524]

Thank you very much man! I tried that and that solved the problem. I will have to remember that formula for the future as it may come in handy.

 

[gnrboyd]

No problem... Glad it worked for you.

 

[bh24524]

Now that I think about it, do you know a lot about mail merges?

 

[gnrboyd]

I occasionally use mail merge in Microsoft Word but not on a regular basis. Many years ago, I made a simple one in Excel using the VLOOKUP formula and a macro.

 

[bh24524]

Hmmm well I don't know if you'll know or not but I have a spreadsheet that keeps track of donations made to an organization. For the sake of simplicity I'll just say I have their name, address, and what they donated(even though there are more columns of information, this is most relevant). There are some people who have donated more than one thing. This is where the mail-merge comes in. I want to send letters to the people for tax purposes acknowledging their donations, but for the ones that have donated multiple times, it is producing several letters for the same person, each listing the separate donation. I want to get it so it gives one person one letter regardless of how many times they've donated and everything they've donated would be listed on that one letter. I know there's a way to do it besides putting the donations in multiple columns in the same row for the same person, but it's beyond me. You wouldn't happen to know a way?

 

[gnrboyd]

I have done something similar in a mail merge when a list of multiple phone numbers (variable quantity) needed to be listed but I used extra columns like you mentioned. Off the top of my head, I really don't know how to do it when you have multiple rows for the same person and need to include all items. I'm sure this can and has been done before. You might try and find a web board for Word (My assumption is you are using Word) and see if you can get some info from someone with more mail merge experience. Good Luck.