Automate a formula in excel

[Guest]

Hello. I am trying to figure out how to have a formula automatically be
applied to the next row but only once information has been entered into the
cell which the formula applies.

Like if I wanted to add a1 + b1 and have the result in c1. How would I set
up the formula so that a result for c2 would only show and be applied if I
entered information in a2 and b2? Hopefully this makes sense.

Thanks for any help.

Farris

 

[JE McGimpsey]

One way:

C2: =IF(COUNT(A2:B2)=2,A2+B2,"")

Copy down as far as required.

 

[Guest]

hi, not sure if your mean columns or rows, if do mean "row" will try to use
the OFFSET() function, but with just what you showed in characters:
=IF(OR(A1="",B1=""),"",A1+B1)

which reads if either a1 or b1 = nothing, then nothing (TRUE), else perform
operation (FALSE).

 

[Guest]

I don't know if this will help:

=IF(OR(A2="",B2=""),"",A2+B2+IF(OFFSET(C2,-1,0)="",0,OFFSET(C2,-1,0)))

cannot start in row 1 with this as it would be in error.

 

[Guest]

This is my formula
=IF(AND(C4>=39234,C4<=39447),1,0)
now, how do I get it so that it will automatically be applied to c5 and
enter the result but not show up in the formula bar until a value is placed
in c5

 

[David Biddulph]

You can simplify your =IF(AND(C4>=39234,C4<=39447),1,0) to
=--(AND(C4>=39234,C4<=39447)
If you want to check for a value in C4, then use
=IF(C4="","",--AND(C4>=39234,C4<=39447))
Copy down a row to apply to C5.

Note that you said "not show up in the formula bar until a value is placed
in c5". This wouldn't be possible. If a formula is there you can see it in
the formula bar (unless protection stops you), but you can let the
visibility of the *result* be dependent on conditions.

 

[Guest]

OK, I figured out what it was. You have to have "extend data range formats
and formulas" activated in the options, edit menu. Once you have entered at
least five rows of the same formula it automatically applies the formula to
the new rows