 New Contributor

# Help with IF AND OR

Hi,
I need a formula that multiplies a figure by different amounts depending on the number in one particular element.
IF “I4” is 2.999 or less then times by 50, IF I4 is between 3 and 4.999 then times by 55, or IF it’s 5 or higher than times by 65.

The formula I have now is obviously wrong because it is saying 0;

=(IF(I4&lt;=2.999,C7*50))*OR(IF(I4&gt;=5,C7*65))*OR(IF(AND(I4&lt;=4.999,I4&gt;=3),C7*55))
9 Replies

# Re: Help with IF AND OR

Here is a quick solution

File inserted as an example

=IF(I4<"2.9999",(C7*50),IF(I4>5,(C7*65),IF(I4<"4.999",(I4*55),IF(I4>3,(C7*55),""))))

IF function – nested formulas and avoiding pitfalls

While Excel will allow you to nest up to 64 different IF functions, it’s not at all advisable to do so. Why?

NikolinoDE

I know I don't know anything (Socrates)

This will help all forum participants.

# RE: Help with IF AND OR

=IF(I4<3,I4*50,IF(I4<5,I4*55,I4*65))

# Re: Help with IF AND OR

Thanks but it’s not quite right… giving a slightly wrong figure.

# RE: Help with IF AND OR

Thanks but that’s not giving the right figure.

# RE: Help with IF AND OR

Can you give me some example I4 values and what you expect the result to be? Based on my understanding of your problem, my formula is returning the right values (I must be misreading your problem - sorry)

# RE: Help with IF AND OR

Thanks - if I4 is 3 then the answer should be 4950. If I4 is 2 then the answer should be 4500. And if I4 is 6 then the answer should be 5850.

# RE: Help with IF AND OR

Ah, and the missing C7 value is 90?
In which case: =IF(I4<3,C7*50,IF(I4<5,C7*55,C7*65))

# Re: Help with IF AND OR

Please give precise details: from which value to which value should it be multiplied with which value?
example:
from 3 to 4 * 50
from 4 to 5 * 55
...and so forth

# Re: Help with IF AND OR

As variant with

``=LOOKUP(I4, {0,3,5}, {50,55,65})*\$C\$7``