|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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
|
|
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:
[Quoted Text] > 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
>
|
|
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:
[Quoted Text] > 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
>
|
|
Marcelo wrote:
[Quoted Text] > 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?
|
|
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:
[Quoted Text] > 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
>
|
|
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
<john.h.williamsREMOVETHIS[ at ]btinternet.com> wrote:
[Quoted Text] >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
|
|
Ron Coderre wrote:
[Quoted Text] > 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.
|
|
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:
[Quoted Text] > 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?
>
|
|
Saxman wrote:
[Quoted Text] >> 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.
|
|
Hi Saxman,
=IF(ISERROR(CHOOSE(A1,6,5,4,3)),0.5,CHOOSE(A1,6,5,4,3))
Ken Johnson
|
|
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:
[Quoted Text] > 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
> >
|
|
|