simple if/then stuff

[Mr Krayzie]

Sorry forthis I'm new to excel and hope someone can help me.
Problem: 2 columns of numbers/
I need all the numbers above the value of 500
to have 500 taken off and a U added to them.
I have managed it but it means having to have not just the two original colums but four more.
below is an exmaple of what i want.


A B C D
576 674 to 76U 174U
128 496 to 128 128
364 222 364 222
971 777 471U 277U

 

[Anne Troy]

=IF(A1>500,A1-500&"U",A1)

Can it be as simple as that?

 

[Mr Krayzie]

Nope Sorry tried that. That works for the number over 500 but makes the numbers under 500 negative.

 

[Anne Troy]

Are you sure? I am only changing numbers that are OVER 500, otherwise, it keeps the SAME number. Perhaps it looks like a formula you tried but is slightly different? Or perhaps you're doing something with column B that I'm not aware of.

 

[Mr Krayzie]

Doh, I must have something strange going on in that column. I tried pastin the column to a new book and tried it, it works.

Thanks very much.

Possibly something to do with the fact I entered the data in access then imported it.

 

[Mr Krayzie]

Still hasnt worked. When I copied the entire column over it still made negative numbers. I've noticed ir's aligned to the left for some reason.

 

[Anne Troy]

Perhaps your numbers are being read as text. If so, then it is subtracting 500 from zero (probably). Do this to the numbers you're brining over:

Put a one in any cell. Copy that cell.
Select your *bad* numbers.
Hit Edit-Paste special-Multiply.
This forces Excel to see them as numbers.
If there are spaces or something in the cell besides the text-formatted numbers, it won't work. If you've got a query or something that is getting the numbers from Access, let me know what it's doing. In your Access DB, is that column in the table formatted as text or numbers?

 

[Mr Krayzie]

now I've tried typing the same numbers manually into another colum doing the formula for that colum and it works!!!!!!
I'm going to have to type the whole thing manually.

 

[Mr Krayzie]

tried the edit paste special multiply thing. I get a column of zeros.
I'm going to re tpye it in excel.

Mr Gates has won again.

 

[Mr Krayzie]

"zer case is sol-ved" as Inspector Cluseau would say. My access in put thing was set as "text" and not "number". Now changed it and copied all and it works fine. Such a silly little mistake.
Thank you everyone.