WUGNET, the Windows User Group Network
Your Complete Resource Center for "The Best" in Shareware, Computing Tips and Support, Windows Industry News... and much more!
Home Forums Shareware Windows Tips Hot Offers FREE Newsletters Arcade Contact Us About Partners
Search WUGNET: RSS Feeds RSS Feeds Advertise with WUGNET    |    Shareware eBooks
HomeHome FAQFAQ      ProfileProfile    Private MessagesPrivate Messages   Log inLog in

IF Statement

  
   Home -> Office -> Worksheet Functions RSS
Next:  sort when using VLOOKUP  
Author Message
Libby

External


Since: Feb 28, 2007
Posts: 37



(Msg. 1) Posted: Wed Aug 06, 2008 7:01 am
Post subject: IF Statement
Archived from groups: microsoft>public>excel>worksheet>functions (more info?)
I have a list of 266 budget numbers and I want to create a formula that will
translate the four diget code into the name of the program. I've started with
=IF(A2=1401,"name",IF(A2=2200,"name",IF(A2=2201,"name",IF(A2=2202,"name",IF(A2=2203,"name",IF(A2=2204,"name",IF(A2=2205,"name",IF(A2=2206,"name",0))))))))
But I cannot exceed 8. Suggestions?
I will be pasting this formula into Crystal Reports
Back to top
Login to vote
Iriemon

External


Since: Sep 11, 2006
Posts: 20



(Msg. 2) Posted: Wed Aug 06, 2008 7:11 am
Post subject: RE: IF Statement [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
Use VLOOKUP instead:

=VLOOKUP(a2,D2:E267,2)

Place the budget numbers in cells d2 through d267 and the name of the
program in cells e2 through 3267.



"Libby" wrote:

> I have a list of 266 budget numbers and I want to create a formula that will
> translate the four diget code into the name of the program. I've started with
> =IF(A2=1401,"name",IF(A2=2200,"name",IF(A2=2201,"name",IF(A2=2202,"name",IF(A2=2203,"name",IF(A2=2204,"name",IF(A2=2205,"name",IF(A2=2206,"name",0))))))))
> But I cannot exceed 8. Suggestions?
> I will be pasting this formula into Crystal Reports
Back to top
Login to vote
Iriemon

External


Since: Sep 11, 2006
Posts: 20



(Msg. 3) Posted: Wed Aug 06, 2008 7:17 am
Post subject: RE: IF Statement [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
SHOULD SAY:
"program in cells e2 through e267."


"Iriemon" wrote:

> Use VLOOKUP instead:
>
> =VLOOKUP(a2,D2:E267,2)
>
> Place the budget numbers in cells d2 through d267 and the name of the
> program in cells e2 through 3267.
>
>
>
> "Libby" wrote:
>
> > I have a list of 266 budget numbers and I want to create a formula that will
> > translate the four diget code into the name of the program. I've started with
> > =IF(A2=1401,"name",IF(A2=2200,"name",IF(A2=2201,"name",IF(A2=2202,"name",IF(A2=2203,"name",IF(A2=2204,"name",IF(A2=2205,"name",IF(A2=2206,"name",0))))))))
> > But I cannot exceed 8. Suggestions?
> > I will be pasting this formula into Crystal Reports
Back to top
Login to vote
Libby

External


Since: Feb 28, 2007
Posts: 37



(Msg. 4) Posted: Wed Aug 06, 2008 7:21 am
Post subject: RE: IF Statement [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
but because I will be pasting it into another program all of the information
needs to be in the one formula, hence If, and not Lookup.

"Iriemon" wrote:

> SHOULD SAY:
> "program in cells e2 through e267."
>
>
> "Iriemon" wrote:
>
> > Use VLOOKUP instead:
> >
> > =VLOOKUP(a2,D2:E267,2)
> >
> > Place the budget numbers in cells d2 through d267 and the name of the
> > program in cells e2 through 3267.
> >
> >
> >
> > "Libby" wrote:
> >
> > > I have a list of 266 budget numbers and I want to create a formula that will
> > > translate the four diget code into the name of the program. I've started with
> > > =IF(A2=1401,"name",IF(A2=2200,"name",IF(A2=2201,"name",IF(A2=2202,"name",IF(A2=2203,"name",IF(A2=2204,"name",IF(A2=2205,"name",IF(A2=2206,"name",0))))))))
> > > But I cannot exceed 8. Suggestions?
> > > I will be pasting this formula into Crystal Reports
Back to top
Login to vote
Graeme K Moore

External


Since: Jul 23, 2008
Posts: 5



(Msg. 5) Posted: Wed Aug 06, 2008 8:13 am
Post subject: RE: IF Statement [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
Libby,
You can use a LOOKUP function that will copy easily.

e.g.

=LOOKUP(A2,{1401,2201,2203...},{Name1,Name2,Name3...})

All you are doing is changing the data from excel references to actual data
arrays.

NOTE: Notice the different perentheses, and make sure that both data arrays
have an equal amount of data within them.

Hope this is more like what you are looking for.
--
GKM mcp2000


"Libby" wrote:

> I have a list of 266 budget numbers and I want to create a formula that will
> translate the four diget code into the name of the program. I've started with
> =IF(A2=1401,"name",IF(A2=2200,"name",IF(A2=2201,"name",IF(A2=2202,"name",IF(A2=2203,"name",IF(A2=2204,"name",IF(A2=2205,"name",IF(A2=2206,"name",0))))))))
> But I cannot exceed 8. Suggestions?
> I will be pasting this formula into Crystal Reports
Back to top
Login to vote
Iriemon

External


Since: Sep 11, 2006
Posts: 20



(Msg. 6) Posted: Wed Aug 06, 2008 10:18 am
Post subject: RE: IF Statement [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
Is there any way to replace Name1, Name2 etc with formulas rather than text?

I tried this but got an error

=LOOKUP(A2,{1401,2201,2203...},{b2*3,b3*5,b3*7...})

"Graeme K Moore" wrote:

> Libby,
> You can use a LOOKUP function that will copy easily.
>
> e.g.
>
> =LOOKUP(A2,{1401,2201,2203...},{Name1,Name2,Name3...})
>
> All you are doing is changing the data from excel references to actual data
> arrays.
>
> NOTE: Notice the different perentheses, and make sure that both data arrays
> have an equal amount of data within them.
>
> Hope this is more like what you are looking for.
> --
> GKM mcp2000
>
>
> "Libby" wrote:
>
> > I have a list of 266 budget numbers and I want to create a formula that will
> > translate the four diget code into the name of the program. I've started with
> > =IF(A2=1401,"name",IF(A2=2200,"name",IF(A2=2201,"name",IF(A2=2202,"name",IF(A2=2203,"name",IF(A2=2204,"name",IF(A2=2205,"name",IF(A2=2206,"name",0))))))))
> > But I cannot exceed 8. Suggestions?
> > I will be pasting this formula into Crystal Reports
Back to top
Login to vote
Peo Sjoblom

External


Since: Jul 07, 2008
Posts: 939



(Msg. 7) Posted: Wed Aug 06, 2008 10:27 am
Post subject: Re: IF Statement [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
Not in a lookup formula you can use CHOOSE and match,




=CHOOSE(MATCH(A2,{1401,2201,2203},0),B2*3,B3*5,B3*7)


will throw an error if no match so you might want to use


=IF(ISNUMBER(MATCH(A2,{1401,2201,2203},0)),CHOOSE(MATCH(A2,{1401,2201,2203},0),B2*3,B3*5,B3*7),"No
Match")




replace "No Match" with a number or a blank if you prefer that


=IF(ISNUMBER(MATCH(A2,{1401,2201,2203},0)),CHOOSE(MATCH(A2,{1401,2201,2203},0),B2*3,B3*5,B3*7),0)


=IF(ISNUMBER(MATCH(A2,{1401,2201,2203},0)),CHOOSE(MATCH(A2,{1401,2201,2203},0),B2*3,B3*5,B3*7),"")






--


Regards,


Peo Sjoblom

"Iriemon" <Iriemon DeleteThis @discussions.microsoft.com> wrote in message
news:6585A0A0-7B2F-4E34-B5AC-28DDCF3CAEDC@microsoft.com...
> Is there any way to replace Name1, Name2 etc with formulas rather than
> text?
>
> I tried this but got an error
>
> =LOOKUP(A2,{1401,2201,2203...},{b2*3,b3*5,b3*7...})
>
> "Graeme K Moore" wrote:
>
>> Libby,
>> You can use a LOOKUP function that will copy easily.
>>
>> e.g.
>>
>> =LOOKUP(A2,{1401,2201,2203...},{Name1,Name2,Name3...})
>>
>> All you are doing is changing the data from excel references to actual
>> data
>> arrays.
>>
>> NOTE: Notice the different perentheses, and make sure that both data
>> arrays
>> have an equal amount of data within them.
>>
>> Hope this is more like what you are looking for.
>> --
>> GKM mcp2000
>>
>>
>> "Libby" wrote:
>>
>> > I have a list of 266 budget numbers and I want to create a formula that
>> > will
>> > translate the four diget code into the name of the program. I've
>> > started with
>> > =IF(A2=1401,"name",IF(A2=2200,"name",IF(A2=2201,"name",IF(A2=2202,"name",IF(A2=2203,"name",IF(A2=2204,"name",IF(A2=2205,"name",IF(A2=2206,"name",0))))))))
>> > But I cannot exceed 8. Suggestions?
>> > I will be pasting this formula into Crystal Reports
Back to top
Login to vote
Display posts from previous:   
       Home -> Office -> Worksheet Functions All times are: Eastern Time (US & Canada) (change)
Page 1 of 1

 
 You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
Categories:
 Windows XP
 Windows Vista
 Windows Other
  Office
 Office Other
 Security
 WinRAR
  • Home |
  • Shareware |
  • Windows Tips |
  • Hot Offers |
  • FREE Newsletters |
  • Arcade |
  • Forums |
  • eBooks |
  • About WUGNET |
  • Partners |
  • Contact

  • WUGNET Privacy Policy |
  • Link to WUGNET