Wikipedia:Reference desk/Archives/Computing/2019 May 16
| Computing desk | ||
|---|---|---|
| < May 15 | << Apr | May | Jun >> | May 17 > |
| Welcome to the Wikipedia Computing Reference Desk Archives |
|---|
| The page you are currently viewing is an archive page. While you can leave answers for any questions shown below, please ask new questions on one of the current reference desk pages. |
May 16 edit
Excel functions modify own cells edit
Can an Excel function modify the cell it's in?
This is prompted by a colleague who keeps sending me population statistics with decimal values. I know the reason is that they are computed estimates but they should know that we need them to be integers, so I keep having to round them to the nearest integer. I do it by making an equivalent matrix of =round() values and then copying the matrix back 'as values'.
I assume that if there is a way to modify the original cells directly, it will be applicable to most or all functions. Rounding is just my example. Hayttom (talk) 16:27, 16 May 2019 (UTC)
- It's not entirely clear what you want to do, but you have some options. If I have a value in cell A1 that reads as 56.1, I can make that appear as just 56 in a few different ways. I can do as you are doing, I can go into each cell and do the same kind of thing (i.e. type =round(56.1) in cell A1), or I could just alter the appearance of the cell through "Format cells", flagging them as numbers and reducing the decimal value to zero from the default of two. That last one is the easiest to do, though the value of 56.1 still exists there in the cell - it's just that you only see 56 being displayed. Matt Deres (talk) 12:28, 17 May 2019 (UTC)
- Thanks Matt. I'm sorry I wasn't clear, but I need to change the real numerical content - not leaving a formula and not relying on formatting to hide the decimals. I suspect it isn't possible without the the process I use of placing a =round() function elsewhere and then manually copying its value back as the specific value. Hayttom (talk) 13:00, 17 May 2019 (UTC)
- @Hayttom: A possible solution: you can just add another column (or row) of rounded values and then hide the column (or row) of original values. Benefit: both columns are in the spreadsheet available for further calculations or exports, and you can control their visibility by hiding and displaying the chosen column. This is important if actually any further calculations are needed, e.g. for values 0.333, 0.333 and 0.333 rounding to integers would yield three zeros which sum to zero, whilst the former sum up to almost one. Limitation: it's only possible if you have just one table in a spreadsheet; otherwise hiding the additional column may spoil a structure of data lying below or above the table you want to modify. --CiaPan (talk) 14:09, 17 May 2019 (UTC)
- Yes, and you should never do this.
- You don't need to. Find a better way. Usually by having a hidden column alongside - the "basic" value goes in one and is hidden, the "massaged" value goes alongside it, selected by some complex selection function (or whatever). This is closer to concepts of functional programming.
- To actually do this self-modification (as self-modification), you need VBA or a cruder macro. They're also a nuisance to debug. Andy Dingley (talk) 14:46, 17 May 2019 (UTC)
I was going to use my sandbox and the history, but the content has nothing to do with Wikipedia and some of it may in fact be a copyright violation.
I sent emails to myself and they were quite long, so when I was trying to delete the information, I couldn't copy and paste on my computer at home. The easiest thing was just to send myself a reply to the original email and figure it out later. Now, a lot of the emails have the same content but there's so much of it I can't see it.— Vchimpanzee • talk • contributions • 22:14, 16 May 2019 (UTC)
- WWhat you're looking for is a diff tool, AKA a File comparison tool. (Wikipedia did not invent the concept.)
- If your need is infrequent, and doesn't need security, I'd recommend one of the many online diff tools.
- https://text-compare.com/ seems to work well.
- A more heavy duty tool might be WinMerge, and if you're on a Unix-style machine, you could always use the command line tool [[diff].
- ApLundell (talk) 00:01, 17 May 2019 (UTC)
- Thanks. I never suspected Wikipedia was the first, but it's where I was introduced to the idea.— Vchimpanzee • talk • contributions • 15:14, 17 May 2019 (UTC)