Ask Your Question

Display number in cell as well as formula?

asked 2017-06-28 13:31:52 +0200

appreciatethehelp gravatar image

updated 2017-07-02 10:51:33 +0200

Hey all, wasn't sure how to word this so allow me to clarify…

let's say I have 4 cells e.g A1, A2, A3 & A4.

In cell A1 I have a number e.g 10. I want to have the number 10 in the cell, plus a formula to the effect of -SUM(A2:A4) (10, minus the sum of cells A2:A4 ).

Let's say cells A2-4 each contain the number 1; the result in cell A1 after entering the formula should be 7 (10 minus the sum of cells A2:A4 (3) = 7).

How do I make the number 10 coexist with the formula in the same cell? What would the formula look like in this case?


EDIT: It seems as if Lupp has got this question covered, and although I have found a way around the issue I was having, I feel that for the sake of discussion I should upload an example document to make the purposes of my question and the question itself more clear: C:\fakepath\Example for Robleyd.ods

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2017-06-28 15:26:03 +0200

Lupp gravatar image

updated 2017-07-02 13:19:39 +0200

You cannot have an entered content, number or text, and a working formula at the same time in a single cell.

(Editing with respect to the amendment to the question by @appreciatehelp using his example file - though I am not "robleyd":)

See this new attachment.

edit flag offensive delete link more


Well, that's a pretty definitive answer. I have figured out a workaround for the issue in this case, but it is good to know. It's a real shame really; I have encountered a substantial number of scenarios wherein it would've been really uselful/helpful to display text/numbers and formulas (their results) in the same cell.

appreciatethehelp gravatar imageappreciatethehelp ( 2017-06-28 17:52:01 +0200 )edit

To be completely clear: A cell always has one of 4 types:
0 - Cell is blank. No content at all. "Unused"
1 - Cell contains a constant of type number.
2 - Cell contains a constant of type text.
3 - Cell contains a formula.
A formula like ="10 ---> "&10 - SUM(A2:A4) can well display a first number and the result of a formula side by side, but the displayed result will be of ResultType text then, and a reference to the cell will return this text and not the value 10.

Lupp gravatar imageLupp ( 2017-06-28 20:08:44 +0200 )edit

In addition the cell will be of content-type formula and entering a number into the cell will make the formula vanish.

Lupp gravatar imageLupp ( 2017-06-28 20:10:22 +0200 )edit

perhaps if you were to explain what it is you are actually trying to do, someone might be able to provide help. I think you see number + formula in the cell as a solution - what is the problem for which it is the solution?

This seems like a typical XY problem

robleyd gravatar imagerobleyd ( 2017-06-29 01:47:22 +0200 )edit

I see the logic. To best explain the result I want to obtain, I have edited my original question, having attached an example document to it.

appreciatethehelp gravatar imageappreciatethehelp ( 2017-07-02 10:41:48 +0200 )edit

answered 2017-06-28 13:45:07 +0200

robleyd gravatar image

updated 2017-07-02 11:19:44 +0200

=10 & -sum(A2:A4)

will show 10-3 assuming the values in your question for A2:A4 The ampersand [&] is a concatenation operator.

If this answer helped you, please accept it by clicking the check mark ✔ to the left and, karma permitting, upvote it. If this resolves your problem, close the question, that will help other people with the same question.

Update (2017-07-02):

I looked at the sample file you uploaded; your concept looks rather complex to me - although possibly it is a bit late at night here for my brain to work at optimum. However, as a thought, might you use columns for date, cost, expenditure type (e.g. llama food, human food, junk food etc) and then use a pivot table (or two or three) to group your costs?

edit flag offensive delete link more


I think we're on the right track, but cell A1 should display 7, not 10 minus 3. I want the formula to deduct the sum of cells A2:A4 from the original number: 10. Does that make sense?

appreciatethehelp gravatar imageappreciatethehelp ( 2017-06-28 13:57:34 +0200 )edit

Therefore, you are asking to see in cell A1 "7 = 10 – 3"?

Denis J Navas gravatar imageDenis J Navas ( 2017-07-03 01:08:36 +0200 )edit

answered 2017-07-02 12:07:09 +0200

m.a.riosv gravatar image

Perhaps display formula and value simultaneously extension does what you are looking for.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-06-28 13:31:52 +0200

Seen: 519 times

Last updated: Jul 02 '17