if value is 900.00 to 949.99 then cell is 100.

if value is 950 to 999.99, then cell is 200

if value is 1000 to 1049.99, then cell is 300

if value is 1050 to 1099.00, then cell is 400

if value is 1100 or greater, then cell is 500Can someone help me put this if statement together so it works? Im using Microsoft Office 2010.

I am assuming that by "if value is 900.00 to 949.99 " you are refer to a value is a specific cell and by "then cell is 100" you are referring to the cell where the formula resides. In other words, with the formula in B1, you are looking at the value in A1.

If so, there are 2 methods:

A Nested IF function would look like this:

=IF(A1>=1100,500,IF(A1>=1050,400,IF(A1>=1000,300,IF(A1>=950,200,IF(A1>=900,100)))))However, there are 2 issues with this solution:

1 - You didn't tell us what you want the formula to rerun if the value is less than 900. As written, that formula will return FALSE since this condition is not specifically addressed. In other words, we haven't told Excel what to do if

noneof the conditions are met.That issue can be solved by telling Excel to return an empty for any value that doesn't meet any of the specified conditions:

=IF(A1>=1100,500,IF(A1>=1050,400,IF(A1>=1000,300,IF(A1>=950,200,IF(A1>=900,100,"")))))2 - This may not be an issue, but you only have 2 decimal places in your conditions, e.g. 999.99. While your cell may display 999.99, if that value is the result of a calculation,

the actual value that Excel may use could be 999.991. If we were to use your exact values, we would need to tell Excel what to with values between e.g. 999.99 and 1000.As written, the formula I posted will use <1000, as opposed to =999.99, which handle 999.991 very well. If there is some reason that you need to restrict your conditions to xxx.99, the formula will be more complicated, but can still be written.

OK, there is also another method that uses VLOOKUP.

If you create a table with your values, you can use a much simpler formula. If you use the VLOOKUP method, you can change the values in the table and not have to touch the formula should any of your criteria change in the future.

For example, if you start with this table:

C D 1 0 2 900 100 3 950 200 4 1000 300 5 1050 400 6 1100 500You can use this formula:

=VLOOKUP(A1,$B$1:$C$6,2,1)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

I didn't provide enough information. This is a bonus pay calculation. If her PRU is between 900.00 to 949.99, then she is paid 100.

If her PRU is between 950 to 999.99, then the bonus is 200.00 instead of 100.00 and so on down the list above.

Thank you for your response

Why don't you think you provided enough information? I believe that I provided 2 solutions that should work for you. Have you tried either of them?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

I just thought I didn't provide enough information. Your response was very help and I appreciate it. Thank you,

So, did either of my suggestions work for you?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Yes, they worked great!!! Thank you so much!

Ask Your Question

Weekly Poll

Would you use "Spotlight Search" on Windows?

Discuss in The Lounge

Poll History