problem in excel with formatting 'last price matched'numbers

[r_ivica]

When i have 'last price matched' prices in excel always have it as number format saved as text and then with that format neither conditional formating or formulas won't work with this numbers because excel don't recognize numbers off course!
So, when i change it into number format as excel suggest me to do it it's ok until price is refreshed. Than comes back to the old format as text...
Please help me how to solve this?! tnx

[PeteB]

It should be fixed to come through as a number, but in the meantime you can use value(<last price matched cell>) in your formulae to convert from text to number

[r_ivica]

i noticed now that only whole numbers (like: 40 36 25 13 10 ....) are in right format, but numbers with decimals (like: 10,5 9,8 7,4 1,12 ...) are wrong formatted

[PeteB]

Try changing your regional settings to UK settings, so your decimal symbol is '.' rather than ','
i.e. 10.5 instead of 10,5
and it should work

[r_ivica]

Try changing your regional settings to UK settings, so your decimal symbol is '.' rather than ','
i.e. 10.5 instead of 10,5
and it should work

i have a lot of other files which are settled and working with ' , ' so it's not good solution for me..

[GeorgeUK]

Not sure if there is anything you can do with the formatting, but you can duplicate the value returned so that your data can read it.

=INT(C4)&","&MID(C4,FIND(".",TEXT(C4,"@"),1)+1,LEN(C4)-FIND(".",TEXT(C4,"@"),1))

If cell C4 had 15.2
whatever cell (D4) has the formula will display 15,2

This may be a workaround you can use - even if it's a bit messy.

[r_ivica]

Not sure if there is anything you can do with the formatting, but you can duplicate the value returned so that your data can read it.

=INT(C4)&","&MID(C4,FIND(".",TEXT(C4,"@"),1)+1,LEN(C4)-FIND(".",TEXT(C4,"@"),1))

If cell C4 had 15.2
whatever cell (D4) has the formula will display 15,2

This may be a workaround you can use - even if it's a bit messy.

little complicated formula and i'm not sure will this help
and i think we didn't understand , i don't even have any '.'
I have numbers displayed as ',' so i have 2,06 1,93 with ',' but excel still read as error... don't recognize this as numbers and they are displayed with ',' as it's normal in my version of excel

[r_ivica]

while on Back or Lay odds numbers are displayed in number format and there is no problem with it, so on back or lay odds i have 1,94 2,08 etc. and they are right displayed as numbers format!

[GeorgeUK]

hmmm - i thought that BA was displaying something like 15.2 while you wanted 15,2

you said

i have a lot of other files which are settled and working with ' , ' so it's not good solution for me..

so when:
1. you type in a number, it displays as 15,2?
2. when odds go into BA, it displays as number 15,2?
3. Is the problem that you are trying to send odds or stake details to BA and it is not accepting 15,2?

4. Is the problem that you want displayed 15,2 as a number and my formula gives 15,2 as text?
(If so put *1 at the end of the formula) - this converts text numbers to actual calculation numbers).

[r_ivica]

hmmm - i thought that BA was displaying something like 15.2 while you wanted 15,2

you said

i have a lot of other files which are settled and working with ' , ' so it's not good solution for me..

so when:
1. you type in a number, it displays as 15,2?
2. when odds go into BA, it displays as number 15,2?
3. Is the problem that you are trying to send odds or stake details to BA and it is not accepting 15,2?

4. Is the problem that you want displayed 15,2 as a number and my formula gives 15,2 as text?
(If so put *1 at the end of the formula) - this converts text numbers to actual calculation numbers).

sorry, maybe i'm wrong said before.

1. Yes
2. Yes
3. No
4. Yes, that is a problem. Yes, i input *1 and now it displays ok, and conditional formatting is working ok because now format is good but only on that new cell where formula is - but i would like to have conditional fomatting exactly on that cell where 'last price matched' is provided, but can't do it because that cell is still not like number format and only can do this cond.formatting on those new cell where new formula with *1 is. Can i somehow do to have conditional formatting displayed to be in cell where Last price matched is, but to 'take info' in those new cell where *1 is inserted?

[GaryRussell]

I will look into this.

[GeorgeUK]

r_ivica

Hopefully Gary can sort this - but meanwhile there should be a workaround.

When you go to do the conditional formatting - on the 'last price matched' are you using the cell value within a certain range? You could change that to formula and put =AND(C4>=15,C4=<10)

Something like that. The formula method will allow you to change the cell colour based on another cell's value.

[GaryRussell]

This problem should be solved in versions 0.99z58 and 1.0.0.51 Beta.

[r_ivica]

r_ivica

Hopefully Gary can sort this - but meanwhile there should be a workaround.

When you go to do the conditional formatting - on the 'last price matched' are you using the cell value within a certain range? You could change that to formula and put =AND(C4>=15,C4=<10)

Something like that. The formula method will allow you to change the cell colour based on another cell's value.

Thanks Gary, this works fine now, but i have one more problem so maybe you know also solution for that too! When in G104 is 0,00 to ignore this conditional formatting - now it gives me yellow color also no matter which number is in G104 because in R104 is 0,00. I want when there is 0,00 not to have that yellow cell formatting..
so, this is formula which i use:
=AND($R$104>=$G$104)

[r_ivica]

r_ivica

Hopefully Gary can sort this - but meanwhile there should be a workaround.

When you go to do the conditional formatting - on the 'last price matched' are you using the cell value within a certain range? You could change that to formula and put =AND(C4>=15,C4=<10)

Something like that. The formula method will allow you to change the cell colour based on another cell's value.

Thanks George, this works fine now, but i have one more problem so maybe you know also solution for that too! When in G104 is 0,00 to ignore this conditional formatting - now it gives me yellow color also no matter which number is in G104 because in R104 is 0,00. I want when there is 0,00 not to have that yellow cell formatting..
so, this is formula which i use:
=AND($R$104>=$G$104)