mikey Posted February 17, 2009 Share Posted February 17, 2009 Every time I try to total a column I get a message #VALUE! I can sum a few rows but not a lot of rows can't figure it out Quote Link to comment
Pat Stanford Posted February 17, 2009 Share Posted February 17, 2009 Tell us more. How many is a few rows? How many is a lot of rows? exaclty what formula are you using? What kind of data is in the cells? Are these Database Rows or Spreadsheet Rows? I just SUMmed 120+ rows and it worked perfectly. Quote Link to comment
mikey Posted February 17, 2009 Author Share Posted February 17, 2009 I can sum 5 or 6 rows ok When I do 65 rows I get the message I am using =SUM(B3,B65) Just numbers in the cells and spreadsheet rows Quote Link to comment
Pat Stanford Posted February 18, 2009 Share Posted February 18, 2009 I doubt this is part of the problem, but B3,B65 will only get you the values of cells B3 and B65, B3..B65 should get you the sum of all the cells between B3 and B65 inclusive. Are you sure they are all just numbers? The only way I get a #VALUE! error is when I try to do a mathematical calculation on a text item. If you have a text field accidentally in the column, it should not cause the error, but it will stop summing at the text field, ignoring any data lower in the list. Can you add an extra column with a formula of =B3*1, Copy that, select the cells equivalent to your data area and paste. See if you get the Value error in any of the new cells. If so, that will give you a place to start looking for your problem. What version of VW are you using? Quote Link to comment
mikey Posted February 18, 2009 Author Share Posted February 18, 2009 I tried B3..B65 and I get a value of 0 They are all numbers no text I tried =B3*1 and get a value of 0 I am using VW 2008 I pasted everything into MS Excel and it all worked fine Go figure Quote Link to comment
Pat Stanford Posted February 18, 2009 Share Posted February 18, 2009 If you email me a copy of the file to the address in my signature, I will take a look and see what I can find. Quote Link to comment
mikey Posted February 18, 2009 Author Share Posted February 18, 2009 Where is your signature? Quote Link to comment
Pat Stanford Posted February 18, 2009 Share Posted February 18, 2009 At the bottom of every post I make. lavectorworks@coviana.com Quote Link to comment
Pat Stanford Posted February 18, 2009 Share Posted February 18, 2009 OK, I figured it out. When you entered the numbers, you entered them as (i.e.) 5,000 and included the comma. This means that they were stored as text strings rather than numbers. What you need to do is first format the cells (using the Format Cell... menu item from the triangle pull down menu above the row headers) and tell them that you want them to be Decimal and check the Use Commas box. Then enter the data as just the number without the commas (i.e. 5000). The worksheet will then reformat the display to show the commas and the sum will work correctly. Remember, that it will only sum as far down as the first cell that has a text string instead of a number. As I tried to explain in an earlier post, one fairly easy way to check for this is to include a column that does a mathematical operation on the original data. If it is not a number, you will get a #VALUE! error in the cell that has the bad data. In Excel, I would do this by putting =B3*1 in cell C3 and then using the Fill Down command to copy the formula to the rest of the cells in the column. VW does not have a fill down command. Instead is has a Fill on Paste function. Select a cell and copy it. Select a range of cells and paste. Each cell in the range will have a "relative" version of the data copied in it. That means that C4 will have =B4*1 amd C65 will have =B65*1. I hope this helps. Regards, Pat Quote Link to comment
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.