excel gives a formula instead of a sum

August 22, 2011 at 02:30:24
Specs: Windows 7
I've recently bought the 2010 excel I can't change the into currency or enter a date for : 22/08/11. I have to type in 22 august 2011. I want to total a column instead I get a formula.

See More: excel gives a formula instead of a sum

Report •

#1
August 22, 2011 at 06:35:09
You should ask this in the Office forum.
You can change number, date, and currency type for a cell or column by highlighting, right clicking and selecting format cell.
You can add up a column by putting a formula in a cell and adding the cell range such as:
In B14 you type: =SUM(B2:B13) which will add up all numbers in cells B2 through B13 and show the sum in B14. These can be regular numbers or currency, but I doubt that dates will give you any meaningful results.

You have to be a little bit crazy to keep you from going insane.


Report •

#2
August 22, 2011 at 07:47:36
Fingers, all you have to do is click the orange triangle in the upper right of the OP and scroll down to incorrect forum and confirm it...then the moderators will put it in whichever forum it should belong to.
Windows 7 forum is a forum in general so people ask their questions in there ;-)

Some HELP in posting on Computing.net plus free progs and instructions 7 Medals


Report •

#3
August 22, 2011 at 15:36:39
Alternative for date issue:

Put an apostrophe in front of 22/08/11, like this '22/08/11

It will then see it as text and not change the date format.

Always pop back and let us know the outcome - thanks


Report •

Related Solutions

#4
August 23, 2011 at 05:11:59
Thanks for the tip XPuser4real
Derek: that will work but you could also just just select text for the column format if you wanted it treated as a text then, but just choosing the date format is better since if you enter the date in any std date format then Excel will just convert it to the same format.

You have to be a little bit crazy to keep you from going insane.


Report •

#5
August 23, 2011 at 08:21:53
Yep, sure thing Fingers.

Always pop back and let us know the outcome - thanks


Report •

#6
August 23, 2011 at 11:17:06
re: "I want to total a column instead I get a formula."

What do you mean when you say "instead I get a formula"?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
August 24, 2011 at 03:36:12
I'm entering Dollar amounts in cells eg: 2.10 , 4.25 when I auto sum I end up with a formula as a total eg =sum(b2:b13) this isn't what I want. I would like a currancy total.

Report •

#8
August 24, 2011 at 04:22:39
re: "I end up with a formula as a total"

Are you say that the cell displays the formula and not the sum?

You need the formula in order to SUM the values.

How those numbers are displayed is based on how the cell is formatted. You can format it as Currency if that's what you want.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
August 26, 2011 at 05:51:29
I've tried to format as currency but keeps changing back to a formula. frustrating

Report •

#10
August 26, 2011 at 08:02:34
I'd really like a Yes or No answer to these questions:

Are you saying that the cell displays the formula and not the sum?

In other words, are you actually seeing =SUM(B2:B13) in the cell even when that cell is not selected?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#11
August 29, 2011 at 03:16:33
Yes
I see a formula when I'm not in the cell.
Yes I am seeing =sum(B12:B13)
If I auto sum I still end up with a formula even if I have tried to chaBnge the columns into a currency.

Report •

#12
August 29, 2011 at 04:32:58
My guess is that the cell have been formatted as text and will not let go of the format.

Try this:
1 - Delete the formula and temporarily put a number in the cell.
2 - Put a 1 in any other empty cell
3 - Select the cell with the 1 and do a Ctrl-C to copy it
4 - Select you list of "currency cells", including the fell where you want your formula.
5 - Right-click anywhere in the selected cells and choose PasteSpecial
6 - In the Operation section, chose Multiply, click OK.

Hopefully this will force a mathematical operation on the cells and convert them to numbers.

7 - Try your formula again.

If that doesn't work, it's possible that your options are set to Show Formula, but I doubt that that is the case.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question