Minus figures in excel

[wb]

I have a column (column F), which contains any losing bets.

At the moment, I simply type the figure into the cell (eg. -10.00).

In cell G1, I have all the figures in column F totalled together by using the formula =SUM(F:F)

This has worked fine, but I don't want to have to manually type anymore, and want to cut and paste from betfair. The problem is that betfair have their minus figures in brackets like this: (10.00)

The cell G1 wont seem to recognise the minus figures in brackets.

Any ideas?

[MattR]

Wayne have you tried getting it from the Pr/Lo page as it's in minus figures there? I'm assuming you're pasting from the history page of the top of my head

[John]

Hey dude,

Could you not say something like:

"If Column X has a commission value of greater than zero, refer to Cell X as a positive number" and this would be your greater-than-zero values in terms of profit?

Then "If Column X has no commission value, i.e. a commission of £0.00 then Cell X is therefore negative i.e. '(10.00)'..."

So you'd base it on whether a certain corresponding cell has commission taken into account or not - this would tell Excel whether the bet is a winning one or a losing one.

Does that make sense? Hope that is of help! :)

[MattR]

Could you not say something like:

"

Voice activated excel, now that's impressive! :D

[John]

... http://www.spinvox.com - that what you had in mind Matt?! :D

[mathare]

So you'd base it on whether a certain corresponding cell has commission taken into account or not - this would tell Excel whether the bet is a winning one or a losing one.

Does that make sense? Hope that is of help! :)

It makes sense but Excel won't recognise (10.00) as a number so won't include it in any sums. I'd write a quick little macro to search for any numbers in brackets and replace them with the relevant negative number.

Although I just entered (10.00) into Excel and it changed instantly to -10 so it could just be a cell formatting issue Wayne. Make sure the cells are not set to Text formatting and are General, Number or Currency

[wb]

I'll have another play around with the formatting so.

Matt, the reason I don't use the p&l instead is because that is broken down on a race per race basis, rather than individual bets. I often have multiple bets in the one race both back and lay.

[wb]

Although I just entered (10.00) into Excel and it changed instantly to -10

I just tried that Mat, and it changed it for me too. However, that only seems to happen when I type it myself, and not when I copy and paste.

[mathare]

I just tried that Mat, and it changed it for me too. However, that only seems to happen when I type it myself, and not when I copy and paste.

I did wonder. I think I have had this same issue in the past when I have used Betfair figures which is why I went down the macro route

[MattR]

Wayne, do you have excel 2007? I've got this now and I just downloaded the history. If you have 2007 you'll have seen the little drop down box that appears by a cell that has a possible error. Within the drop down list on these (10.00) type figures is the option to change to convert to number. I higlighted the whole column, clicked the drop down box and chose convert to number and it changed them all to minuses, then just had to format the cells to two decimal points. As far as I know (certainly didn't see anything like it) excel 2003 and prior had nothing like this. So if you don't have 2007 this won't be possible.

[wb]

Hi Matt, no it's the older version that I have. Might try it in open office and see what happens.

[Street cry]

Hi guys is there a way in excel that i can have acell automatically clear itself once populated i have amacro linked to a button to clear cells but i would like something that automatically cleared the cell instantaneously i am trying to do this with an if statement but the old circular reference is an obvious concern hope someone can help cheers sc

[TheOldhamWhisper]

Hi guys is there a way in excel that i can have acell automatically clear itself once populated i have amacro linked to a button to clear cells but i would like something that automatically cleared the cell instantaneously i am trying to do this with an if statement but the old circular reference is an obvious concern hope someone can help cheers sc

Can you give an example why you would want to do this? Does the value get copied elsewhere or another calculation take place?

[Street cry]

Hi Oldham,
After a bet is fired a certain cell is populated with "PLACED" for another action to take place the cell must be clear so i do this with a macro and button but i want it to do it automatically.

[TheOldhamWhisper]

I'm not really an expert in macros, but something like:

Private Sub Worksheet_Change(ByVal Target As  Range) 
     
    If Target.Address = "$A$1" Then 
         'write your macro here
         'or code to execute your existing macro
    
    End If 
     
End Sub

Where's Mat when you need him... :wink

[Street cry]

cheers will give it ago SC

[mathare]

Where's Mat when you need him... :wink

He was out shopping but he's back now. Although he's still not 100% on what you're trying to do...

[Street cry]

Hi Mat,
On my sheet i have a status cell , if this cell is populated with any text or number then the sheet can not fire any commands. I can clear this cell witha macro and button i have written but i want it to be able to clear the cell without my input so say 1 second after it has fired and the status cell has been populated with the word PLACED the cell then clears allowing the sheet to fire when again hope that makes sense SC

[mathare]

On my sheet i have a status cell , if this cell is populated with any text or number then the sheet can not fire any commands. I can clear this cell witha macro and button i have written but i want it to be able to clear the cell without my input so say 1 second after it has fired and the status cell has been populated with the word PLACED the cell then clears allowing the sheet to fire when again hope that makes sense SC

Ok, that makes sense. I have a few questions though:
1) How long does the macro take to run? Presumably it's a split second job but just wondering about the 1 second timing is all. If the CPU is busy the macro could take a second to run maybe
2) Does the cell need to get populated in the first place? I know that acts as a blocker to other commands but if you're clearing a second after it is populated what commands have a chance to trigger?

Oldham's code should work on update events to the worksheet so that makes sense. I think you need to put the code in the worksheet module rather than in a separate module. Obviously you just want to set the cell contents to "" or use ClearContents on the relevant cell(s) but it's the timer bit I guess you're struggling with.

I know there are several time functions so there are several ways you could do this, including:
1) OnTime method call to call the clear contents function X seconds after the update to the relevant cell
2) The Wait method but this will suspend all Excel activity during the wait period so I don't think you want this one
3) Store the time of the update to the relevant cell (using at least 1s granularity) and in the clear contents function use something like "while now() - <time cell> < 1"
4) Start a timer when the update is made to the cell and when that timer reaches X seconds call the clear contents function

A lot depends on the structure of your code and whether you need Excel to be usable between the cell updating and being cleared again

[laffo16]

to wb, well a good text editor is "notepad++", it has tones of text tweaking abilities, some of which are order by alpha or numerical, sort case upper or proper, u can hold alt and drag upwards to delete verticaly, and the find/replace which would help you sort out those figures.

im not sure if i understand your problem properly, but have you tried in H1-X =int(G1) copied down, then use sum on H. I havent tested with betfair data but seems to convert ok from a quick test.

or try this
=IF(ISERROR(FIND(")",G1)),IF(G1="","",ABS(G1)),-ABS(MID(G1,FIND("(",G1)+1,FIND(")",G1)-FIND("(",G1)-1)))

hope its what you needed :) was a challange ne ways

[TheOldhamWhisper]

I don't know if the original question was solved, but all you need to do is use a Custom Format for the cells in the column.

Pick #,##0;-#,##0 to start with and then remove the - sign and stick in the parentheses #,##0;(#,##0) and it will allow you to copy and paste minus numbers in the format mentioned and it will also perform the calculations correctly too. I'm using Excel 2002 with SP3, so I would expect anything after this will do the same.

[Street cry]

Ok, that makes sense. I have a few questions though:
1) How long does the macro take to run? Presumably it's a split second job but just wondering about the 1 second timing is all. If the CPU is busy the macro could take a second to run maybe
2) Does the cell need to get populated in the first place? I know that acts as a blocker to other commands but if you're clearing a second after it is populated what commands have a chance to trigger?

Oldham's code should work on update events to the worksheet so that makes sense. I think you need to put the code in the worksheet module rather than in a separate module. Obviously you just want to set the cell contents to "" or use ClearContents on the relevant cell(s) but it's the timer bit I guess you're struggling with.

I know there are several time functions so there are several ways you could do this, including:
1) OnTime method call to call the clear contents function X seconds after the update to the relevant cell
2) The Wait method but this will suspend all Excel activity during the wait period so I don't think you want this one
3) Store the time of the update to the relevant cell (using at least 1s granularity) and in the clear contents function use something like "while now() - <time cell> < 1"
4) Start a timer when the update is made to the cell and when that timer reaches X seconds call the clear contents function

A lot depends on the structure of your code and whether you need Excel to be usable between the cell updating and being cleared again

Hi Mat ideally i would like to make it so that this aaforementioned status cell doesn't populate in the first place, if that's possible thats what i would like to do cheers SC

[TheOldhamWhisper]

Hi Mat ideally i would like to make it so that this aaforementioned status cell doesn't populate in the first place, if that's possible thats what i would like to do cheers SC

What populates the cell in the first place? Is it another macro? Can you not simply rem out the piece of code that populates it?

[mathare]

Hi Mat ideally i would like to make it so that this aaforementioned status cell doesn't populate in the first place, if that's possible thats what i would like to do cheers SC

What populates the cell in the first place? Is it another macro? Can you not simply rem out the piece of code that populates it?

My thinking exactly. Cells don't populate themselves so just take out the line in whatever other macro writes to the cell

[MattR]

Mat, This might help if I am right in what Street Cry means.

I imagine it is a bot linked to excel that is populating the cell when a bet is triggered via excel. Not sure what Street Cry is using but this is similar to Betting Assistant, when the bet is triggered a cell is populated to show this and another trigger on that horse/team whatever, won't happen unless that cell is blank.

[Street cry]

Hi Mat and oldham i would do that but that part of the sheet was not my doing and i don't know where it gets its instruction from if i did i would have just cut out the action how can i find out where it gains its instruction from cheers sc

[Street cry]

sorry just seen what matt has posted, yes it gets its instruction from betangel but i don't know how to detect where from and stop it

[mathare]

sorry just seen what matt has posted, yes it gets its instruction from betangel but i don't know how to detect where from and stop it

What happens if you lock the cell to prevent updates? Does BetAngel unlock it, not update it or crash?

[Street cry]

good point i will have to try that

[Street cry]

right have locked all those status cells, i will have to wait till tomorrow to see if it workd cheers for the help guys