Solved I need an if statement that states the following

September 4, 2015 at 09:19:06
Specs: Windows 7
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 500

Can someone help me put this if statement together so it works? Im using Microsoft Office 2010.


See More: I need an if statement that states the following

Report •


#1
September 4, 2015 at 10:38:18
✔ Best Answer
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 none of 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      500

You 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.


Report •

#2
September 4, 2015 at 11:30:34
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


Report •

#3
September 4, 2015 at 12:10:29
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.


Report •

Related Solutions

#4
September 4, 2015 at 14:39:32
I just thought I didn't provide enough information. Your response was very help and I appreciate it.

Thank you,


Report •

#5
September 4, 2015 at 17:04:47

Report •

#6
September 8, 2015 at 14:41:31
Yes, they worked great!!! Thank you so much!

Report •


Ask Question