Software

Ask something
Question Locked

Concatenated formula Excel will not calculate

0 helpful
3 replies

by egirard

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I found a solution for a problem I had, but would like to know if a more elegant way existed.

For a large table, I concatenated multiple cells to form a formula in about 2000 variations. I know now how to use relative and absolute references to do my work witout concatenating but here goes:

=CONCATENATE("=";Q2;R2;")") had this result: =AVERAGE(F2:F2), the goal was to have it growing to =AVERAGE(F2:F2000)at every line.

This would not calculate.

I copied and pasted the results so the content of the cell was now =AVERAGE(F2:F2), but still did not calculate.

These would not calculate until I doubleclicked in the formula and pressed enter. My auto calc is on, cell is defines as number or general, I tried CLEAN()etc.

I also did not want to doubleclick and enter in every 2000 cells.

So I tried Replace All ''='' with ''='' and the calculation was performed!

There is a better way, I hope!

3 replies

by locolobo

and I got the same results. I was recommending "Paste Special - Values"

The "Replace All" was interesting. I don't know why that worked. Maybe that was similar to editing each cell and hitting enter?

by egirard

The replace all must include a carriage return in its workings.

Happy I am not alone!

Thanks,

Eric

This discussion is closed to new replies.