Excel help

[one cool dog]

I am doing my best to learn Excel more thoroughly and got myself a book to learn with. I did all the addition sums okay when I manually added numbers, I've learnt about sorting and filtering data and tried to move on.

However, I downloaded one of my Betfair account's data to mess about with and I have come across my first annoying problem. As you will see from my screenshot, there are little green arrows at the top of the cells with the profits/losses. I thought i would add up all of the cells using the various ways of addition.

Whatever way I try, I get an answer of zero. Are those green arrows the problem, and if they are how do I get rid of the sods?

Cheers

[dermag]

Hi ocd,

I'm new to the forum but have been using the BA now for a few weeks. Great program in itself but it's taken to a new level by the excel interface.

Right, your problem is/could be two fold. The green flags in the upper left are excel's auto error checking things. They aren't the problem but can be turned off.

Go to TOOLS>>OPTION>>ERROR CHECKING(tab) and check out the tick box 'Enable background error checking' I have mine off, but it's personal preferences.

The other problem could be that betfair sends it's data down via .csv and usually the figures are not formatted as numbers, so autosum doesn't recognise them.

An easy way to solve it is to put this formula in the cell to the right of the first value (cell D3 in your case) and drag the formulas all the way down.

=C3*1

It's that simple. Any time you come across incorrectly formatted numbers just do this and then copy the whole column of new 'numbers' and paste over the top of the old column, selecting paste special, values & number formats only.

Hope this helps.

[Ian]

That's correct - Excel doesn't recognize the values as numbers.

Another way to change them is to select the range of cells and at the left of the top most cell there will be a drop down box where you can select 'convert to number'. Every cell in the range will be converted to a number.

[one cool dog]

dermag



Joined: 18 Feb 2007
Posts: 1


Excels error checking.
Hi ocd,

I'm new to the forum but have been using the BA now for a few weeks. Great program in itself but it's taken to a new level by the excel interface.

Right, your problem is/could be two fold. The green flags in the upper left are excel's auto error checking things. They aren't the problem but can be turned off.

Go to TOOLS>>OPTION>>ERROR CHECKING(tab) and check out the tick box 'Enable background error checking' I have mine off, but it's personal preferences.

The other problem could be that betfair sends it's data down via .csv and usually the figures are not formatted as numbers, so autosum doesn't recognise them.

An easy way to solve it is to put this formula in the cell to the right of the first value (cell D3 in your case) and drag the formulas all the way down.

=C3*1 (I did this and it worked a treat)

It's that simple. Any time you come across incorrectly formatted numbers just do this and then copy the whole column of new 'numbers' and paste over the top of the old column, selecting paste special, values & number formats only.

(I did not quite understand this bit. Do I have to keep a worksheet with that in and copy and paste the numbers over that column. Sorry for being gimp.)

Hope this helps.


Sun Feb 18, 2007 2:47 pm

Ian



Joined: 19 Nov 2005
Posts: 94
Location: Birmingham


That's correct - Excel doesn't recognize the values as numbers.

Another way to change them is to select the range of cells and at the left of the top most cell there will be a drop down box where you can select 'convert to number'. Every cell in the range will be converted to a number.

(Great advice again and I think I have got it.)

[dermag]

Hi ocd,

No you don't have to keep a worksheet with those formulas in. What I meant was that you can 'paste special' any values generated by formulas and when you 'paste special' you can select different ways to paste the data.

It may be easier if I use your example as an example. You have data in column C which isn't formatted correctly, so you insert those formulas in column D and you then get the correct format in column D, but all these cells in column D are formulas and you may want them as just plain values. This is how to do it.

Select all the values in column D, right click your mouse and select 'copy' from the pop out menu. Now, if the cells in column D have become unhighlighted then highlight/select them again, and right click again, this time select 'Paste Special' and you will get a pop up box. Here you can select different types of "pastes", in your case to get just the number values in colmn D instead of formulas, you would select the 'Values and Number Formats' option and click ok.

This would then transform your formulas in column D to just plain values.