Can't manage a formula...

[Legedien]

Mornin' all!

Excel really is my weak spot and I need help with creating a formula. If anybody would feel helpful, I'd really appreciate it!

Well, the excel-problem I have:
In cell A1 I've entered number 12134. In cell B1 I've added the formula =LEFT(A1;2) (I must add that my formulas are in swedish, but I'll try to translate it as correct as possible).

So, the number I now see in B1 is 12. Then in cell C1 I have the formula =IF(B1>10;1019). And since the value is BIGGER than 10, in C1 I see the number 1019.

The problem is that I want to add, that if the number in B1 also is SMALLER than 19, the TRUE is also 1019.

And to make it even messier, I'd like to add more conditions/terms. Like, if the number is BIGGER than 19 or SMALLER than 21 the TRUE is 2021.

Anyone?

Thanks in advance,
Legedien

 

[royUK]

Hi

You need to look at Nested IF statements in your help and also lots of examples on this site

 

[Aladin Akyurek]

In B1 enter:

=LEFT(A1,2)+0

In C1 enter:

=IF(AND(B1>10,B1<19),1019,"?")

For more conditions, you should use VLOOKUP, not IF. Something like this...

=VLOOKUP(B1,{0,0;12,1019;19,"?"},2,0)

Replace the list separator , with your ; on your system.

 

[Dave Patton]

You might try a lookup or Vlookup with the information either in the formula or in a supporting table.

one example

=LOOKUP(B1*1,{0,10,19,21;0,1019,2021,2050})

Notes
1. the *1 converts the text in B1 to a number
2. check the thresholds
for example 19 will show 2021

3. check the commas and semicolon conversions to your system

 

[Legedien]

Hi all!

Thank you very much for you replies!!

I'd like to ask you about the formulas you've entered. This one: =IF(AND(B1>10,B1<19),1019,"?") I understand without problems and made it work. Very interesting!

But! These formulas...
=VLOOKUP(B1,{0,0;12,1019;19,"?"},2,0)
=LOOKUP(B1*1,{0,10,19,21;0,1019,2021,2050})

...I do not understand. And since we use both commas and semicolons in our system I have no clue where to change those in your formulas.

The first formula I understood, and could therefor change the commas.

Could anyone explain the LOOKUP formulas a bit?

TIA
/Legedien

 

[Aladin Akyurek]

On 2002-10-18 03:45, Legedien wrote:
Hi all!

Thank you very much for you replies!!

I'd like to ask you about the formulas you've entered. This one: =IF(AND(B1>10,B1<19),1019,"?") I understand without problems and made it work. Very interesting!

But! These formulas...
=VLOOKUP(B1,{0,0;12,1019;19,"?"},2,0)
=LOOKUP(B1*1,{0,10,19,21;0,1019,2021,2050})

...I do not understand. And since we use both commas and semicolons in our system I have no clue where to change those in your formulas.

The first formula I understood, and could therefor change the commas.

Could anyone explain the LOOKUP formulas a bit?

TIA
/Legedien

The VLOOKUP as well as the LOOKUP version use an in-lined table...

It would be much easier for you to set up a 2-column list, give it a name, and use that name in the VLOOKUP formula... like in

=VLOOKUP(B1,Table,2)

See for an example

http://www.mrexcel.com/board/viewtopic.php?topic=25724&forum=2&6