This is a discussion on If formula please within the Excel Questions forums, part of the Question Forums category; Can I please have the formula for: if a range is less than a number (in a different cell) then ...

Can I please have the formula for: if a range is less than a number (in a different cell) then 0. If the range is greater than the number then take the amount greater than the number.
Thank you

2. ## Re: If formula please

Originally Posted by josh613
Can I please have the formula for: if a range is less than a number (in a different cell) then 0. If the range is greater than the number then take the amount greater than the number.
Thank you
If a cell is less than the number in that different cell, or if all items in the range are smaller?

would something like the below work? Start your list at A2; \$D\$1 is your cell with the value you are testing for.

=if(A2<\$D\$1,0,if(A2>\$D\$1,A2,if(A2=\$D\$1,{true condition},"ERROR")))

or

=if(A2<=\$D\$1,0,if(A2>\$D\$1,A2,"error"))

Sheet1
C
Excel 2007

4. ## Re: If formula please

Welcome to the board..

So to be specific with the ranges..

A1 and B1
If A1 is less than B1, 0
If A1 is greater than B1, A1

What if the 2 cells are the same?

5. ## Re: If formula please

Thank you all for the warm welcome and trying to lend me a hand!
Let me try to be more specific.
A1 has an amount of \$100. If B1 through G1 add up to \$90 then G2 should have a value of \$0. If B1 through H1 add up to \$110 then H2 should have a value of \$10.

6. ## Re: If formula please

Sheet1
C
15 =IF(sum(\$B\$1:G1)<=100,0,IF(sum(\$B\$1:G1)>100,sum(\$B\$1:G1)-100,""))
Excel 2007

copy in G2 and drag across

7. ## Re: If formula please

Is this what you're looking for?

Formula in G2: =IF(SUM(B1:G1)=A1-10,0,"false value")

Formula in H2: =IF(SUM(B1:H1)=A1+10,0,"false value")

8. ## Re: If formula please

Originally Posted by mmodroger
Sheet1
C
15 =IF(sum(\$B\$1:G1)<=100,0,IF(sum(\$B\$1:G1)>100,sum(\$B\$1:G1)-100,""))
Excel 2007

copy in G2 and drag across
Thank you! It works great and I am very appreciative!!
Have a good night!

9. ## Re: If formula please

Thank you also to all who responded!!
It is a great feeling to know that there are people out there who can help when you are stuck!
Have a great night!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•