How to make a more complicated version of the if function

[darblooz]

Hi,

I would like to make a formula that allows me to have a number set
automatically according to the number i put in the previous cell.

Hope you understand what i mean

I want to put a number in cell A1 so that there appears another number
automatically in cell A2, this number will depend on the number i put
in A1.

Let's say i do this:
I put the number 0 in CELL A1 -----> CELL A2 automatically becomes
0.4
CELL A1 = 0 to / or equal to 1.25 -----> CELL A2 = 0.4
CELL A1 = 1.25 to / or equal to 3.10 -----> CELL A2 = 0.4
etcetera....

Sorry for my bad English i'm from Belgium.

Hope you can help me out.

 

[Bob Phillips]

According to your post, A2 always contains 0.4, so just enter 0.4

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[darblooz]

Sorry, my mistake i edited my post.

 

[Bob Phillips]

Would you care to post that edit. I don't see it here, as I am in the public
newsgroups, not your forum.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[darblooz]

Off course, sorry.

Here's the part i edited.

Let's say i do this:
I put the number 0 in CELL A1 -----> CELL A2 automatically becomes 0.4
CELL A1 = 0 to / or equal to 1.25 -----> CELL A2 = 1
CELL A1 = 1.25 to / or equal to 3.10 -----> CELL A2 = 2
etcetera...

 

[Pete_UK]

Off course, sorry.

Here's the part i edited.

Let's say i do this:
I put the number 0 in CELL A1 -----> CELL A2 automatically becomes 0.4
CELL A1 = 0 to / or equal to 1.25 -----> CELL A2 = 1
CELL A1 = 1.25 to / or equal to 3.10 -----> CELL A2 = 2
etcetera....

"etcetera" implies that you have more of these values, so it might be
better to set up a small table which lists the values and what you want
them to be tranformed into, and then you can use a VLOOKUP formula with
the final parameter set to TRUE.

Hope this helps.

Pete

 

[darblooz]

Thanks,

I've never heard of that function before.

I've searched a bit about this function, wow looks kinda complicate to
me but i guess i'm on my way to resolve my problem .

 

[Pete_UK]

Thanks,

I've never heard of that function before.

I've searched a bit about this function, wow looks kinda complicate to
me but i guess i'm on my way to resolve my problem .

If you wanted to use a series of nested IF statements, there is a limit
of 7 that you can use in one formula, so if you have more than 7 values
you have to use something else. VLOOKUP is one such alternative - it
enables you to search through a table looking for a match, which can
either be an exact match (final parameter set to FALSE or 0) or a match
on the highest value which is less than the value you are searching
for. In this latter case the table has to be sorted. The third
parameter enables you to select which column of the table the data
should be returned from if you find a match - in your case it will be
2. The second parameter is the table range - if you set up a little
table of your values say in cells L1 to M10, then this is your range.
The table could be on another sheet.

Post back if you need any further help.

Pete

 

[Bob Phillips]

You could try

=LOOKUP(A1,{0,0.0001,1.2501},{0.4,1,2})

and just add extra limits and values

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)