0

I've data in 3 columns.

1 Value.

2 Yearly percent change.

3 Daily percent change.

My problem:

I've used formula for yearly change,

=SUM(B18-70)/70

and for the daily change this one has been used.

=SUM(B12-B11)/B11

But Excel does not automatically fills both cells when I add new value in first column.

I do not want to copy down the formula in each columns because I do not want to see -100 and #div/0 error.

What should I do?

  • Show the starting data state, specify what value(s) is inserted and in what cell(s), tell what value(s) (or formulas?) does you want to be auto-inserted and in what cell(s). For example; I use the formula =SUM(B18-70)/70 for the yearly change, and =SUM(B12-B11)/B11 for the daily change Does you think we guess the addresses of cells where these formulas are posessed? – Akina Jul 12 at 4:45
  • 2
    If you want to prepopulate the formula and avoid error messages or nonsense results, add a test for a blank data cell. For example: =IF(ISBLANK(B11),"",formula). The result cell will display as blank until the data is there to produce a real result. – fixer1234 Jul 12 at 5:52
  • Please add some sample data, help us to fix the issue!! – Rajesh S Jul 12 at 9:52
  • Use =IFERROR(formula,"-") and fill down. It will evaluate the formula and show the result, unless if there is an error in which case - will be shown. In a similar manner; test for conditions that creates invalid output with IF(...) and display e.g. - again (see fixer1234's comment). There is no other way - this is very similar to standard programming. – Hannu Jul 12 at 10:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.