If formula please

josh613

New Member
Joined
May 2, 2012
Messages
4
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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"))
 
Upvote 0
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?
 
Upvote 0
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.
I greatly appreciate your time!
 
Upvote 0
Excel Workbook
C
15=IF(sum($B$1:G1)<=100,0,IF(sum($B$1:G1)>100,sum($B$1:G1)-100,""))
Sheet1
Excel 2007
copy in G2 and drag across
 
Last edited:
Upvote 0
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")
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top