Can't manage a formula...

Legedien

New Member
Joined
Oct 17, 2002
Messages
2
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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

royUK

Well-known Member
Joined
Jul 24, 2002
Messages
1,606
Hi

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

Well-known Member
Joined
Feb 15, 2002
Messages
5,029
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Oct 17, 2002
Messages
2
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
 

Forum statistics

Threads
1,148,277
Messages
5,745,816
Members
423,980
Latest member
zimza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top