IF formulas

[pmb81]

Hello, I need help coming up with the proper IF formula for the 2 below scenarios written out in plan English. Any help is appreciated!!

If D3 is greater than or equal to 90% but less than 100%, E3 = 5. If D3 is greater than or equal to 100% but less than 110%, E3 = 15. If D3 is greater than or equal to 110%, E3 = 25. If D3 is less than 90%, E3 = 0.

If L3 is greater than or equal to 90% but less than or equal to 99%, M3 = 5. If L3 is greater than or equal to 100%, M3 = 10. If L3 is less than 90%, M3 = 0.

 

[tigeravatar]

pmb81,

Here's one way. Use this formula in cell E3:
=IF(AND(D3>=0.9,D3<1),5,IF(AND(D3>=1,D3<1.1),15,IF(D3>=1.1,25,0)))

 

[Gerald Higgins]

Hi, welcome to the board.

For E3 . . .

=if(D3< .9,0,if(D3< 1,5,if(D3< 1.1,15,25)))

For M3 . . .

=if(L3< .9,0,if(L3<= .99,5,10))

Alternatively, you can do this with LOOKUP formulas, such as VLOOKUP, which would be a good way to go especially if you start adding in more conditions for E3 or M3.

 

[pmb81]

Beautiful! Thank you so much for the help.

I'm hoping you can also help me with this scenario:

in N3 for every multiple of 10,000 I need to enter 3 points in O3.
So:
if N3 = 10,000, then O3 = 3
if N3 = 20,000, then O3 = 6
if N3 = 30,000, then O3 = 9
if N3 = 9,999 then O3 = 0

Is there a formula that would help me accomplish this?

 

[Gerald Higgins]

Perhaps something like this
=rounddown(N3/10000,0)*3

This will give 3 for 15,000, which I assume is what you want.

 

[Scott Huish]

With INT:

=INT(N3/10000)*3

 

[pmb81]

Wonderful. You have know idea how much time you've saved me! Thank you all for your help!

 

[pmb81]

okay hopefully this is my last and final question. I'm not sure if it still qualifies as an IF statement.

I have 4 columns that each could have a 1 or a 0 in it. If all 4 columns have a 1 then i need the 5th column to populate a 5. If even just one of the columns has a 0 (for instance 3 1's and 1 0) then the 5th column should populate a 0.

So:
S4=1, T4=1, U4=1, V4=1. Therefore W4 = 5
S4=1, T4=0, U4=1, V4=1. Therefore W4 = 0
S4=1, T4=0, U4=0, V4=1. Therefore W4 = 0

Does anyone have any idea how to approach this one?

 

[tigeravatar]

Give this a try:
=IF(COUNTIF(S4:V4,0),0,5)

 

[pmb81]

that worked perfectly, what about this?

W10, W11, and W12 could either have a 5 or a 0 as the value. If all 3 of the cells have a 5 in it then I need to place a 5 in X10. If even just one of the cells has a 0 in it (it could be one 0 or up to 3 0's), then I need to place a 0 in X10.