# My IF Formula isnt working

This is a discussion on My IF Formula isnt working within the Excel Questions forums, part of the Question Forums category; Hello, heres my deliema.. J2 2=3.65 3=2.70 4=2.30 5=2.08 6=1.93 7=1.82 8=1.74 9=1.67 10=1.62 #=O28 What Im trying to do ...

1. Hello, heres my deliema..

J2
2=3.65
3=2.70
4=2.30
5=2.08
6=1.93
7=1.82
8=1.74
9=1.67
10=1.62

#=O28

What Im trying to do is that when I enter in a number in a cell and its one of the numbers on the left column, then it needs to be multiplied by another cell (O28)

Heres the formula I have been trying to work with but its not letting me go through with it.

By the way its set it up, you should be able to figure out what Im trying to do.

=IF(J2=2,O28*3.65,IF(J2=3,O28*2.7,IF(J2=4,O28*2.3,IF(J2=5,O28*2.08,IF(J2=6,O28*1.93,IF(J2=7,O28*1.82,IF(J2=8,O28*1.74,IF (J2=9,O28*1.67,IF(J2=10,O28*1.62)))))))))

If someone could help me on this I would appreciate it. Thanks.

j

2. You may want to consider setting up a table and using Vlookup. It's much easier to edit and friendlier to use.

I see the problem with your IF formula. The last IF needs to read IF(J2=10,o28*1.62,xxx)))
In other words, If J2 doesn't equal 10, it needs one last condition(xxx), such as IF(J2=10,O28*1.62,"Number not in list")))
so it can conclude.

Consider setting a table and using Vlookup. It would be much easier than an IF formula, and you could make the entry cell a data validation drop down list box.

G

3. Not sure what you want, but you might try the CHOOSE function.

=CHOOSE(J2,"",028*3.65,028*2.70,028*2.40,...)

4. Consider one of:

1. =IF((J2>=2)*(J2<=10),O28*INDEX({3.65,2.7,2.3,2.08,1.93,1.82,1.74,1.67,1.62},
MATCH(J2,{2,3,4,5,6,7,8,9,10},0)),"?")

2. =O28*INDEX({0,3.65,2.7,2.3,2.08,1.93,1.82,1.74,1.67,1.62},
MATCH(J2,{0,2,3,4,5,6,7,8,9,10}))

The first formula will give "?" whenever J2 is less than 2 or greater than 10.
The second formula will result in 0 whenever J2 is less than 2 and use 1.62 as multiplier whenever J2 is greater than 10.

5. With any values outside your stated range, the following defaults to either the first (J2=2) or last (J2=10). Thus, if J2 = 0, then O28*3.65 would be the result. If J2 = 100, O28*1.62 would be the result.

=O28*CHOOSE(MIN(10,MAX(2,INT(J2)))-1,3.65,2.7,2.3,2.08,1.93,1.82,1.74,1.67,1.62)

Bye,
Jay

6. Hi, thanks! Aladin's formula seemed to be a better fit for what Im doing and works well.

Very Happy!

j

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•