i am using the formula (A+B)x0.029+.30 which returns at least 0.3. I need a formula that will give me a value greater than 0 only if A+B is greater than 0. Right now 0.3 populates even if the values for A and B are 0. Hopefully this makes sense. Thank you!
Hi Reid,
Try this version:
IF((A2+B2)≤0,"",(A2+B2)×0.029+0.3)
Regards,
Barry
EDIT: Replaced image with one containing a 0, 0 example.
Hi Reid,
Try this version:
IF((A2+B2)≤0,"",(A2+B2)×0.029+0.3)
Regards,
Barry
EDIT: Replaced image with one containing a 0, 0 example.
No. That is distinctly different behavior than what I proposed.
I proposed only providing any answer when both cells have an entered value. You are asking, based on the last email, that the answer only be presented when the sum A+B is great than zero.
in this case the answer barry provided should work.
=if((A1+B1)>0, (A1+B1)*0.029+0.3, "")
I think you only want an answer if someone type values in for the cells that contain the A and B values... right?
if so, use the following formula:
assume cell A1 contains A
assume cell B1 contains B
the result will be in C1
=if(and(A1<>"", B1<>""),(A1+B1)*0.029+.30, "")
Reid6887 wrote:
..only problem now is the field in which that function is in returns a blank field if “A and B” are not populated with something other than 0. How do I make it show a valid number or 0?
The "" in the formula displays a blank cell. If you want a 0 to be displayed instead, you can replace the "" with 0.
SG
The solution that SGIII provided above worked. I needed to replace the quotations at the end with 0.
Barry,
if I am understanding your formula correctly, it reads as "if D43+E43 is less than or equal to 0, then (referred to as "") it should return a value equal to the equation in the second paragraph, correct? I updated the equation on both line 42 and line 43, with real values populated in D42 and E42, which returns exactly what I want, then zero populated in line 43, which returns a blank value in cell F43. Essentially what I need is if real values are populated in the D and E cells, then I want (D+E) x 0.029+.3 to populate in the F cell. If zero is populated in D and E cells on a line (which it will automatically when I add a new line), I want the F cell to show zero until D and E cells show a real number.
Sorry I’m being a pain in the butt ha ha...almost there...only problem now is the field in which that function is in returns a blank field if “A and B” are not populated with something other than 0. How do I make it show a valid number or 0?
One last question - each cell in row 2 on the spreadsheet add up all of the cells below row 2 (ie A2 = A3:A10, etc). How do I make so that when I add row 11, it will automatically update the sum in A2 to include the new row without manually changing the equation in A2?
"How do I make so that when I add row 11, it will automatically update the sum in A2 to include the new row without manually changing the equation in A2?"
Change rows 1 and 2 to Header rows. Change the formula in A2 to SUM(A)
Regards,
Barry
Correct- essentially only populate a value in C1 if A1+B1 > 0. Thanks for the reply. I will give it a go!
It looks like that basically gave me the same result as before -
That did the trick!!! Thank you very much!
Hi Reid,
What "valid number" do you want column F to show when both D and E are empty?
What "valid number" do you want column F to show when D contains 0 and E is empty?
What "valid number" do you want column F to show when D contains 1 and E is empty?
What "valid number" do you want column F to show when D contains 1 and E contains 0?
Does this do what you need?
in F2: IF((D2+E2)≤0,0,(D2+E2)×0.029+0.3)
Regards,
Barry
Numbers formula help
