Excel 2007 help

I have the following cenerio and not sure what function to use. I am a new user and any help would be appreciated.

IF A1=26.75 then the value returned to B2 is 75

IF A1=20.00 then the value returned to B2 is 45

IF A1=18.00 then the value returned to B2 is 40

IF A1=17.50 then the value returned to B2 is 40

The value of A1 will always be one of these four values. Thank you in advance for your help.

How about nested IFs?

=IF(A1=26.75,75,IF(A1=20,45,IF(A1=18,40,IF(A1=17.5,40,"Invalid"))))

If A1 will definitely be one of those values, you could use

=75-(A1<26.75)*30-(A1<20)*5

Sorry its = LOOKUP(A1,{17.5,18,20,26.75},{40,40,45,75}.

Roger, can you explain a little how you've come with your formula? Was it complicated to get to it? can most or any "nested ifs" based on one cell be transofrmed in such a formula?

I would have used Nick's formula, if not this, then that, if not this, then that.... but yours seems sophisticated.

Regards,

Jack Gopher

 Create a table consisting of 2 columns and 4 rows as follows:

17.50     40

18.00     40

 20.00    45

26.75     75

Name this range NumbersTwo

go to cell B2 and enter: =vlookup(A1,NumbersTwo,2,false)

Solved. 

Hi Jack
Sorry for the dealy in response, but I have been away at the MVP Summit in Seattle, without my computer.

The formula breaks down as follows.

The highest value it could be is 75, when A1 = 26.75
If A1<26.75, then the value need to be 45, which is 30 lower than 75
(A1<26.75) will either return True or False. When Multiplied by a number, the True is coerced to 1, and the False is coerced to 0
If it is True, then (A1<26.75)*30 equals 1 * 30 and this value will be taken from 75 to give a result of 45
If it is False then (A1<26.75)*30 will result in 0 * 30, hence the value will stay at 75.

Continuing on, the test for whether the value is <20, will return similar true or false results, and multiplying the result * 5, will give the further deduction to end up with 40. I chose 20 arbitraly as a value which was above 18 and 17.5. You could have used <=18.

This works because a value of 17.5, for example, is both less than 26.75, therefore 30 is deducted, AND is less than 20, so a further 5 is deducted.

There are many instances where you can use this logic in place of IF statements.
Hope the expalantion helps.

Thanks a lot Roger, Now I understand it!

Jack