Help with IF AND OR

New Contributor
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<=2.999,C7*50))*OR(IF(I4>=5,C7*65))*OR(IF(AND(I4<=4.999,I4>=3),C7*55))
9 Replies

@Snichols2310 

 

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),""))))

 

Additional Info:

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?

more info about IF functions in the upper link

 

Hope I was able to help you with this info.

 

NikolinoDE

I know I don't know anything (Socrates)

 

Was the answer useful? Mark them as helpful!

This will help all forum participants.

=IF(I4<3,I4*50,IF(I4<5,I4*55,I4*65))
Thanks but it’s not quite right… giving a slightly wrong figure.
Thanks but that’s not giving the right figure.

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)

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.
Ah, and the missing C7 value is 90?
In which case: =IF(I4<3,C7*50,IF(I4<5,C7*55,C7*65))
Please give precise details: from which value to which value should it be multiplied with which value?
Please in tabular form
example:
from 3 to 4 * 50
from 4 to 5 * 55
...and so forth

@Snichols2310 

As variant

image.png

with

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