simple if/then stuff

Mr Krayzie · Feb 23rd, 2002, 08:53 AM

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 · Feb 23rd, 2002, 09:12 AM

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

Can it be as simple as that?

Mr Krayzie · Feb 23rd, 2002, 09:19 AM

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

Anne Troy · Feb 23rd, 2002, 09:23 AM

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 · Feb 23rd, 2002, 09:33 AM

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 · Feb 23rd, 2002, 09:49 AM

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 · Feb 23rd, 2002, 09:53 AM

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 · Feb 23rd, 2002, 09:55 AM

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 · Feb 23rd, 2002, 10:06 AM

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 · Feb 23rd, 2002, 10:16 AM

"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.

Feb 23rd, 2002, 09:12 AM	#2
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	=IF(A1>500,A1-500&"U",A1) Can it be as simple as that? __________________ ~Anne Troy

Feb 23rd, 2002, 09:23 AM	#4
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	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. __________________ ~Anne Troy

Feb 23rd, 2002, 09:53 AM	#7
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	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? __________________ ~Anne Troy

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 23rd, 2002, 08:53 AM	#1
Mr Krayzie New Member Join Date: Feb 2002 Location: Springfield Posts: 8	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

Feb 23rd, 2002, 09:19 AM	#3
Mr Krayzie New Member Join Date: Feb 2002 Location: Springfield Posts: 8	Nope Sorry tried that. That works for the number over 500 but makes the numbers under 500 negative.

Feb 23rd, 2002, 09:33 AM	#5
Mr Krayzie New Member Join Date: Feb 2002 Location: Springfield Posts: 8	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.

Feb 23rd, 2002, 09:49 AM	#6
Mr Krayzie New Member Join Date: Feb 2002 Location: Springfield Posts: 8	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.

Feb 23rd, 2002, 09:55 AM	#8
Mr Krayzie New Member Join Date: Feb 2002 Location: Springfield Posts: 8	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.

Feb 23rd, 2002, 10:06 AM	#9
Mr Krayzie New Member Join Date: Feb 2002 Location: Springfield Posts: 8	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.

Feb 23rd, 2002, 10:16 AM	#10
Mr Krayzie New Member Join Date: Feb 2002 Location: Springfield Posts: 8	"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.