I am fairly sure that Bill Gates hates me.
OK, I have put all of the contributions for the Dave Simmons fund into an Excel Spreadsheet so I can keep track.
I have three columns that are giving me fits.
First column is total contributed
Second column is net contribution (after PayPal fees)
Third column is PayPal fees
At row 40 I have all the columns total using the formula =sum(E2:E39) [F and G for the other two)
Now as a check to make sure I entered the net and fee amounts correctly I want to total F40 and and G40 into H40 which should equal E40 if I entered all the amounts correctly.
When I set up the sheet last night, I used =F40+G40 and got the correct answer. Today I added one new contribution and I get ##### as the answer. (cells E, F, G 30 if it matters)
I have tried =sum(F40+G40), =sum(F40,G40), =sum(F40:G40) all seem to result in the same ##### result.
all the cells in question are formated the same as currency, 2 decimal points, and -1234.56 for negative numbers.
What am I doing wrong?
Try adjusting the width of the column where you are placing your formula. That bunch of pound signs probably means that the cell is not wide enough to display a formatted number.
Rick, do you need to widen your column? That sounds like what I see when I’ve set the column widths and then enter something too wide.
Select the column, then right-click & Column Width, or select then format/column/autofit.
ETA: clarification, plus: darn you, Kizarvexius 
if the ‘#####’ is showing up in E, F,& G30 only something is screwy with the data in those cells - that would definitely cause a ###### result in your totals
You guys are geniuses. the cell wasn’t wide enough. the last contribution took the value over $1,000.00
:smack:
Thanks for the help.
I attended an Excel training class years ago wherein the instructor told us he had been called in as a consultant by a company reporting an Excel problem. He walked in, spotted the cell full of pound signs, double-clicked on the column header to expand the width, and watched the company president go into fits of apoplexy. It seems their entire business had bottleneck for ten days because nobody knew how to correct the problem. So the guys gets a full day’s consulting fee (he wouldn’t tell us how much) for a simple double-click.
Needless to say, when I started teaching Excel classes as part of my own job, I told that story every time.
I just tried this, and it doesn’t work in Excel 2000. Is it a more recent feature?
Not the column header but the line between column headers.
Tip: If you click once on the blank block that is to the left of the “A” Column Heading, it will select the entire worksheet.
Then, while everything is selected, go up to “Format”, then “Column”, then “Autofit Selection”. This will change the width of every column to whatever the widest cell in that column has in it.
This could give you weird results if you’re allowing cells to have wrapped text, but in most cases it works pretty well. You can use this Selection thing to do anything to the entire worksheet’s format - autofit row heights, change fonts, etc…
Good luck.
I knew both of those things, but that’s not what Kizarvexius said. He clearly said the consultant “…double-clicked on the column header to expand the width.” Since he’s an Excel expert, I would have expected him to say something else if he just meant widen the column using the usual methods.
Double-clicking on the column head sounds like an interesting and useful feature that doesn’t seem to work in my old version of Excel. Does anyone know if it works in a version later than 2000, or was Kizarvexius using inaccurate shorthand, or just wrong?
Kizarvexius just misspoke. You can adjust the column width by dragging the line between column headers. If you double-click the same line, this will perform an “Autofit Selection” on the column to the left as if you had selected it via the menu. Indeed, you can select multiple columns, then double-click one of the lines between column headers in the selected columns to perform an autofit on the multiple selected columns. I suspect the latter is what Kizarvexius’s consultant did, and “double-click on the column headers” is a just a convenient shorthand.
Indeed, just a convenient shorthand. Sorry for any confusion, but it’s been a long time since I taught a lesson without the immediate use of visual aids. Feel free to deduct a couple of points from the survey that will be distributed at the end of class.
No deduction, you rock. I figured it out.
Thanks for the help.
:smack: Double-click on the line! I didn’t try that. It works!
“To my extreme mortification, I grow wiser every day.” – Lady Mary Wortley Montagu
Just FYI, note that there are certain types of data conditions that will cause you to show ##### in the field regardless of how wide you make the column. If you make the column wider, and still just keep seeing more and more #/pound signs/number signs/octothorpes, you are experiencing a problem with the data. I have only seen this once, so I can’t remember what caused it. If I come up with it I’ll post.