Automatically add to cell values

Discuss the spreadsheet application

Automatically add to cell values

Postby adamberkey » Thu Apr 23, 2020 5:25 am

Message has been deleted by an incompetent book keeper (according to Villeroy)

 Edit: Added by MrProgrammer, moderator, on 2020-04-24 at 14:24 UTC.
adamberkey rudely deleted the original question after receiving responses from several volunteers who tried to help. For the benefit of others who might have similar questions, the post was along these lines:
Is there a way that I can automatically add an additional .000000000001 to a cell immediately after filling the cell? My cells are formatted as currency. I enter 120.00 in the formula bar and Calc displays $120.00 in the cell but changes my entry to 120 when I press Enter. However, when I do a Find for $120.00, that cell will not be in the results because it was originally input as 120.00. Is it still 120.00 or did OO change it to 120 without .00? How can I keep OO from getting rid of the .00? I need to be able to find a cell with $120.00 and I don't want to search for $120 because that would find cells like $120.75.
The topic is locked to prevent any further tampering by adamberkey. 
Last edited by adamberkey on Fri Apr 24, 2020 5:35 am, edited 1 time in total.
OOo 2.4.X on Mac OSx other
adamberkey
 
Posts: 15
Joined: Mon Sep 08, 2008 9:44 am

Re: Automatically add to cell values

Postby FJCC » Thu Apr 23, 2020 5:44 am

To answer your question, I do not know of any way to automatically add an amount to the value entered in a cell, other than to write a macro.

I cannot replicate the problem you are trying to solve. Here is what I tried. In a cell with default formatting I entered 120 and it appeared as 120. I formatted another cell to display as currency with two decimal places and then entered 120. It appeared as $120.00 due to the formatting. In a third cell with default formatting I entered 1205 and it appeared as 1205. If I use the Find & Replace dialog (menu Edit-> Find & Replace) to search for 120, it finds all of these cells.

If you are getting a result different from what I saw, please upload a small sample file. To upload a file, click Post Reply and look for the Upload Attachment tab just below the box where you type a response.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7749
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Automatically add to cell values

Postby adamberkey » Thu Apr 23, 2020 5:54 am

Message has been deleted by an incompetent book keeper (according to Villeroy)
Attachments
ScreenCap 2020-04-22 at 9.05.17 PM.jpg
Last edited by adamberkey on Fri Apr 24, 2020 5:35 am, edited 1 time in total.
OOo 2.4.X on Mac OSx other
adamberkey
 
Posts: 15
Joined: Mon Sep 08, 2008 9:44 am

Re: Automatically add to cell values

Postby MrProgrammer » Thu Apr 23, 2020 5:53 pm

adamberkey wrote:Is there a way that I can automatically add an additional .000000000001 to a cell immediately after filling the cell?
I believe we have XY Problem with this question. Do you really want to find specified values in cells? For example, you want to find cells which display $120.00 but not those that display $120.25 or cells which display $15 but not those with $5.15. If so, then you want to enable Entire Cells in the Find & Replace dialog box. Then search for 120 or 15.

adamberkey wrote:However, when I do a Find for $120.00, that cell will not be in the results because it was originally input as 120.
No! The find fails because searches are for values, not for what you see displayed on the screen. The cell's value is 120. It does not contain a $ and you cannot (successfully) search for that. You need to read section 2. Controlling how data is displayed in Ten concepts that every Calc user should know. Even if you had some voodoo which added .000000000001 to the value, your seach for $120.00 will still fail. So will a search for $120 because there is no $ in the cell.

adamberkey wrote:Is it still 120.00 or did OO change it to 120 without .00?
Non-empty cells contain text or they contain a number. The values of the numbers 120 and 120.00 are the same, of course. The concepts of value and formatting are different. Read the tutorial.

adamberkey wrote:How can I keep OO from getting rid of the .00?
You can format the cell as text. Calc does not try to interpret, and will not change, text in cells. But you probably do not really want to do that because a spreadsheet's ability to perform calculations on text is limited. Continue to use numeric values and perform your searches with the Entire Cells option.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Mac FAQ
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3961
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Automatically add to cell values

Postby adamberkey » Thu Apr 23, 2020 6:15 pm

Message has been deleted by an incompetent book keeper (according to Villeroy)

 Edit: adamberkey had replied that, yes, the actual goal was to find specified values in cells. Further responses suggested to me that adamberkey had either not read the tutorial or had not appreciated the relevance of the material there. — MrProgrammer, moderator, 2020-04-24 15:16 UTC 
Last edited by adamberkey on Fri Apr 24, 2020 5:36 am, edited 1 time in total.
OOo 2.4.X on Mac OSx other
adamberkey
 
Posts: 15
Joined: Mon Sep 08, 2008 9:44 am

Re: Automatically add to cell values

Postby Villeroy » Thu Apr 23, 2020 8:23 pm

No, this is no problem because 120.7 is exactly the same value as 120,70. Your approach is the problem. Book keeping in spreadsheets is the first problem. Not knowing the tool you are using is a secondary problem.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28544
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Automatically add to cell values

Postby adamberkey » Fri Apr 24, 2020 5:33 am

Do you intend to school me on how I should take care of my business then, Villeroy?
I feel so little that I can't be as big and competent as you with book keeping.

Tell you what, guys, just forget I was ever here...
OOo 2.4.X on Mac OSx other
adamberkey
 
Posts: 15
Joined: Mon Sep 08, 2008 9:44 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 14 guests