Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Impossible Task?
Basically, I would like to convert some numbers.
I would always like to convert:- 1 to 6 2 to 5 3 to 4 4 to 3 else = 0.5 If I had the following numbers displayed in cells A1:A10 1 2 3 4 5 6 7 8 9 10 How could I get them displayed as:- 6 5 4 3 0.5 0.5 0.5 0.5 0.5 0.5 in cells B2:B10? They would not normally be in numerical order as above. Would I require a LOOKUP? TIA |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Impossible Task?
Get yourself a new sheet with 2 columns
Sheet2 ColA ColB From To 1 6 2 5 3 4 4 3 etc Then use this Sheet1 ColA ColB 1 =VLOOKUP(A1,Sheet2!A:B,2,0) 2 =VLOOKUP(A2,Sheet2!A:B,2,0) 3 etc -- Allllen "Saxman" wrote: Basically, I would like to convert some numbers. I would always like to convert:- 1 to 6 2 to 5 3 to 4 4 to 3 else = 0.5 If I had the following numbers displayed in cells A1:A10 1 2 3 4 5 6 7 8 9 10 How could I get them displayed as:- 6 5 4 3 0.5 0.5 0.5 0.5 0.5 0.5 in cells B2:B10? They would not normally be in numerical order as above. Would I require a LOOKUP? TIA |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Impossible Task?
hi Saxman
use a auxiliar table when you have 1 6 2 5 3 4 4 3 5 .5 etc use a vlookup to find the answer so in B1 you use =vlookup(a1,h1:i10,2,false) hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Saxman" escreveu: Basically, I would like to convert some numbers. I would always like to convert:- 1 to 6 2 to 5 3 to 4 4 to 3 else = 0.5 If I had the following numbers displayed in cells A1:A10 1 2 3 4 5 6 7 8 9 10 How could I get them displayed as:- 6 5 4 3 0.5 0.5 0.5 0.5 0.5 0.5 in cells B2:B10? They would not normally be in numerical order as above. Would I require a LOOKUP? TIA |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Impossible Task?
Marcelo wrote:
use a auxiliar table when you have 1 6 2 5 3 4 4 3 5 .5 etc use a vlookup to find the answer so in B1 you use =vlookup(a1,h1:i10,2,false) Thanks, but where do I place the auxiliar table? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Impossible Task?
A couple alternatives...
If there will only be positive integer values in A1 Try this: B1: =(A1<=4)*(6.5-A1)+0.5 Otherwise, perhaps something like this: B1: =SUM(COUNTIF(A1,{1,2,3,4})*{5.5,4.5,3.5,2.5})+0.5 Does that help? *********** Regards, Ron XL2002, WinXP "Saxman" wrote: Basically, I would like to convert some numbers. I would always like to convert:- 1 to 6 2 to 5 3 to 4 4 to 3 else = 0.5 If I had the following numbers displayed in cells A1:A10 1 2 3 4 5 6 7 8 9 10 How could I get them displayed as:- 6 5 4 3 0.5 0.5 0.5 0.5 0.5 0.5 in cells B2:B10? They would not normally be in numerical order as above. Would I require a LOOKUP? TIA |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Impossible Task?
Positive values only?
=LOOKUP(A1,{1,2,3,4,5},{6,5,4,3,0.5}) Entered in B1 and copied down. Gord Dibben MS Excel MVP On Thu, 28 Sep 2006 16:17:26 +0100, Saxman wrote: Basically, I would like to convert some numbers. I would always like to convert:- 1 to 6 2 to 5 3 to 4 4 to 3 else = 0.5 If I had the following numbers displayed in cells A1:A10 1 2 3 4 5 6 7 8 9 10 How could I get them displayed as:- 6 5 4 3 0.5 0.5 0.5 0.5 0.5 0.5 in cells B2:B10? They would not normally be in numerical order as above. Would I require a LOOKUP? TIA |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Impossible Task?
Ron Coderre wrote:
A couple alternatives... If there will only be positive integer values in A1 Try this: B1: =(A1<=4)*(6.5-A1)+0.5 Otherwise, perhaps something like this: B1: =SUM(COUNTIF(A1,{1,2,3,4})*{5.5,4.5,3.5,2.5})+0.5 Does that help? It certainly does, using the first formula. Thanks. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Impossible Task?
doesn't matter, you can put it in any place of your spreadsheet, in may
sample, I put it on h1:i10 range -- regards from Brazil Thanks in advance for your feedback. Marcelo "Saxman" escreveu: Marcelo wrote: use a auxiliar table when you have 1 6 2 5 3 4 4 3 5 .5 etc use a vlookup to find the answer so in B1 you use =vlookup(a1,h1:i10,2,false) Thanks, but where do I place the auxiliar table? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Impossible Task?
Saxman wrote:
Otherwise, perhaps something like this: B1: =SUM(COUNTIF(A1,{1,2,3,4})*{5.5,4.5,3.5,2.5})+0.5 That worked as well. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Impossible Task?
Hi Saxman,
=IF(ISERROR(CHOOSE(A1,6,5,4,3)),0.5,CHOOSE(A1,6,5, 4,3)) Ken Johnson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Impossible Task?
copy the formaula below in Cell B1
=IF(A1=1,6,IF(A1=2,5,IF(A1=3,4,IF(A1=4,3,0.5)))) and drag to copy it to the rest... Allllen wrote: Get yourself a new sheet with 2 columns Sheet2 ColA ColB From To 1 6 2 5 3 4 4 3 etc Then use this Sheet1 ColA ColB 1 =VLOOKUP(A1,Sheet2!A:B,2,0) 2 =VLOOKUP(A2,Sheet2!A:B,2,0) 3 etc -- Allllen "Saxman" wrote: Basically, I would like to convert some numbers. I would always like to convert:- 1 to 6 2 to 5 3 to 4 4 to 3 else = 0.5 If I had the following numbers displayed in cells A1:A10 1 2 3 4 5 6 7 8 9 10 How could I get them displayed as:- 6 5 4 3 0.5 0.5 0.5 0.5 0.5 0.5 in cells B2:B10? They would not normally be in numerical order as above. Would I require a LOOKUP? TIA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a schedule from a list of dates ? | Charts and Charting in Excel | |||
change from copy & paste task pane (yuk) to c&p little box | Excel Discussion (Misc queries) | |||
I check "windows in task bar", after restarting, it is no longer c | Excel Discussion (Misc queries) | |||
Seemingly impossible task | Excel Discussion (Misc queries) | |||
loop trough e-mail address list to send task lists with outlook | Excel Discussion (Misc queries) |