Welcome to Software Development on Codidact!
Will you help us build our independent community of developers helping developers? We're small and trying to grow. We welcome questions about all aspects of software development, from design to code to QA and more. Got questions? Got answers? Got code you'd like someone to review? Please join us.
Activity for pnuts
| Type | On... | Excerpt | Status | Date |
|---|---|---|---|---|
| Answer | — |
A: Automatic adjustment when copying of column reference within INDIRECT function When in ColumnC `B` can be returned with `=char(Column()+63)` and since not inside double quotes this will automatically adjust to `C` in ColumnD. This way a string acceptable to INDIRECT can be constructed in C16 that should produce the same result as the current formula in C16 and also, unlike t... (more) |
— | over 3 years ago |
| Question | — |
Automatic adjustment when copying of column reference within INDIRECT function An as yet unanswered question on Web Applications, INDIRECT cell reference - Can't copy cell as reference cell pattern remains same, from @Daniel Klose is: > I have an Overview Sheet where I want to display a sum of values from another sheet. I'm using the indirect function to grab the sheet ... (more) |
— | over 3 years ago |
| Question | — |
Delete specific cells with shift cells up Answers to Excel - Get only values from column that are not Zero “0” asked by @CiscoNewb includes ones applying AGGREGATE: =IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$12)/(A$2:A$12>0),ROW(A1))),"") from @bosco-yip and FILTER from @Harun24HR (Excel 365): =FILTER(A2:A12,A2:A12>0) This sample... (more) |
— | over 3 years ago |
| Answer | — |
A: How to pivot text? Perhaps (if a process of several relatively familiar steps is easy to remember) but additional formulae may be appropriate, a little extension to the source data required and some formatting. However, the offered solution only works for at most three states (here `YES`, `NO`, and `X`). First, add ... (more) |
— | over 3 years ago |
| Question | — |
How to pivot text? In this Q a user asked for a simple way to represent this data: ``` - PERSON 1 | PERSON 2 | YES - PERSON 1 | PERSON 3 | YES - PERSON 2 | PERSON 1 | YES - PERSON 2 | PERSON 3 | YES - PERSON 3 | PERSON 1 | NO - PERSON 3 | PERSON 2 | NO ``` in this format: ``` - | PERSON 1 | PERSON 2 | PE... (more) |
— | over 3 years ago |
| Question | — |
Merge a pair of columns where in each row one is blank, without a formula A Q on Stack Overflow asks how to merge two columns where each row has only one populated cell. The example was from (on the left) to (on the right): a|b a 0| 0 |1 1 |5 5 9| ... (more) |
— | over 3 years ago |
| Question | — |
Copy a sparse matrix with removal of blanks A user of Web Applications gave the following as an example: Array example https://docs.google.com/spreadsheets/d/1nPTBFXaIgpruPgNoWFSBUL2yKaibjeNJNExhnHVr8LE/edit#gid=0 and asked: >How do I read a long row of cells only some of which have scores in them, and print those scores out? ... (more) |
— | over 3 years ago |
| Answer | — |
A: Trigger Conditional Formatting by Check Box Assuming Check Box Form Controls in ColumnD, yes in OpenOffice Calc 4.1.4, though for some the process may be too tedious and entering say an `x` instead may be preferred. Unlike in Google Sheets for example, where a Tick box has a FALSE/TRUE value associated with it automatically, in Calc the ass... (more) |
— | over 3 years ago |
| Question | — |
Trigger Conditional Formatting by Check Box One part of an as yet unanswered Q on Super User asks whether conditional formatting in OpenOffice Calc can be used to highlight an entire row based on whether a checkmark in the row has been selected. This example was provided: Data sample Can it be? (more) |
— | over 3 years ago |
| Answer | — |
A: Controlling format when importing CSV This is happening because of assumptions made by the software. "E notation" is a standard convention popular with scientists, mathematicians and engineers as a convenient way to represent and process very large or very small numbers. Such scientific notation is so popular that without context the cha... (more) |
— | over 3 years ago |
| Answer | — |
A: A function to round negative numbers towards 0 Yes, several. Example outcomes from various inputs and functions are shown in the table below, with those compliant with the current requirement highlighted: Rounding examples CEILING CEILING.MATH CEILING.PRECISE (more) |
— | over 3 years ago |
| Question | — |
A function to round negative numbers towards 0 Some while ago a user of Web Applications asked after a function to avoid "an if/else just to handle negative numbers properly". Clarification provided was: >Presently it round based on absolute value, meaning that -1.5 rounds to -2.0, which is wrong. I want it to round to -1.0 An answer was... (more) |
— | over 3 years ago |
| Answer | — |
A: Iterative references in LibreOffice Calc Since the formulae have circular references (Err:522) the spreadsheet setting needs to be changed to enable iteration (Tools > Options… > LibreOffice Calc > Calculation, tick Iterations and choose a sensible combination of `Steps:` and `Minimum change:`, OK). For current purposes I suggest `100` for ... (more) |
— | over 3 years ago |
| Question | — |
Iterative references in LibreOffice Calc An as yet unanswered Q from Stack Overflow [SO] enquired: >how to stop looping this formula where the formulae were: in B9 =16,50 in B10 =B9-B11 in B11 =B10/2 >without changing text in cell. (The above after changing `` to `/` based on a comment. It can also be deduced ... (more) |
— | over 3 years ago |
| Question | — |
Loss of precision when concatenating an exchange rate with a string In response to a Q on Web Applications [WA] the asker self-answered with: =CONCATENATE("@ £/€ rate: ",text(J21,"0.00000")) The issue was that, with "in a cell J21 I have a set value 1.10000":. =CONCATENATE("@ £/€ rate: ",(J21)) was returning `@ £/€ rate: 1.1` rather than `@ £/€ ra... (more) |
— | over 3 years ago |
| Question | — |
A Conditional Formatting formula rule expected to apply to all cells is only affecting some A very recently asked Q on Web Applications has no answer yet (and, in my opinion, is so badly written may not receive an answer at all promptly). However it does seem (in my interpretation) to raise a very common issue with Conditional Formatting [CF] – with a very simple solution. The asker chose ... (more) |
— | over 3 years ago |
| Answer | — |
A: In a column, set populated cells to 1 and empty cells to 0 An easy way (in LibreOffice Calc 5.4.3.2) is not to attempt directly to overwrite what is already present but to create the desired results with a formula in a different column, then replace the contents of the original column with the values (not formulae) from the added column. Assuming the data... (more) |
— | over 3 years ago |
| Question | — |
In a column, set populated cells to 1 and empty cells to 0 In an as yet unanswered Q from early this year a user of Stack Overflow asked: >how to fill all non-empty cells of a column by 1 and the empty cells by 0. It's an xlsx file open with LibreOffice Calc. Is this possible with a formula? (more) |
— | over 3 years ago |
| Question | — |
Highlight the maximum value per row across multiple columns An as yet unanswered OpenOffice Calc - Highlighting the higest value in multiple columns posted by Rui on Super User from early this year asked whether a condition was possible to: >fill with a color in the highest value between multiple columns The context was OpenOffice Calc and a data sample... (more) |
— | over 3 years ago |
| Question | — |
Summing values formatted for a different locale A couple of year ago a user of Super User reported difficulties with summing values purporting to be Euros imported to LibreOffice Calc in CSV format. In the Q the user does not mention the locale settings, but did write "I've used the LEFT function to get just the number part, though that still l... (more) |
— | over 3 years ago |
| Question | — |
TRUE/FALSE flags for alternating colouring by group For use in connection with conditional formatting with alternating coloured fills in OpenOffice Calc, several years ago a user of Super User was applying this Ruby script: f = File.readlines("shading.txt") #<-- I just copy and paste a column from spreadsheet to here $i = 0 $sw... (more) |
— | over 3 years ago |
| Answer | — |
A: Combine the first character of a cell with another cell Regarding length, the correct answer used 43 characters, but one was a space serving no purpose, so say 42 for: =CONCATENATE(LOWER(MID(A1,1,1)),LOWER(B1)) Most obviously, the concatenating function is a great deal less compact than an `&`: =LOWER(MID(A1,1,1))&LOWER(B1) Then apply... (more) |
— | over 3 years ago |
| Question | — |
Combine the first character of a cell with another cell A Q on Stack Exchange from a very long time ago included: >I have first names in one column and second names in another, I want to create a third column that contains the first character from the first name and add it to the surname creating first initial + surname. The user asked "How can I do... (more) |
— | over 3 years ago |
| Answer | — |
A: How to add to the value of one cell an amount that depends upon the range of a different cell? One way is to create a lookup table (sorted ascending) and take advantage of VLOOKUP's `issorted` parameter to opt for "the nearest match (less than or equal to the search key)". Say a table like so: 0 0 5 1 10 2 15 3 20 4 25 5 with the Named Range `Table`. Then a formula such as: ... (more) |
— | over 3 years ago |
| Question | — |
How to add to the value of one cell an amount that depends upon the range of a different cell? A user of Web Applications asked how to: > - +1 when the value is >=5, > - +2 when the value is >=10, > - +3 when the value is >=15, > - +4 when the value is >=20, > - +5 when the value is >=25 and had tried: =IF(F7>=5,H7+1,IF(F7>=10,H7+2,IF(F7>=15,H7+3,IF(F7>=20,H7+4,H... (more) |
— | over 3 years ago |
| Answer | — |
A: Conditionally format a pair of columns First, such formatting is conditional, so there is no need for the IF functions here (nor, normally, in general for Conditional Formatting [CF]). Formatting is triggered when the response to a formula is TRUE (or equivalent). So, for red fill: =$K4>5 is sufficient, as is: =$K4>=2 ... (more) |
— | over 3 years ago |
| Question | — |
Conditionally format a pair of columns A user of Web Applications asked "How should I fix the formula?" with reference to: =IF($K4<2,$K4) applied as a Conditional Formatting [CF] formula to J4:J8, where the desired (green fill) format was to apply to values under `2` in K4:K8. I have modified the requirement to extend the format... (more) |
— | over 3 years ago |
| Answer | — |
A: How to convert an RFC822 timestamp to a date index number? Yes, only the sixth to eleventh characters are required and these may be extracted with the MID function. MID is a string function that returns Text but a double unary operator should trigger Sheets' automatic date detection and display as `44105` when in default format: =--mid(A1,6,11) The... (more) |
— | over 3 years ago |
| Question | — |
How to convert an RFC822 timestamp to a date index number? A self-answered question - How to get DATEVALUE to recognize RSS 2 pubDate (RFC822)? - written by spcsLrg on Web Applications Stack Exchange offers this formula: =DATEVALUE(TRIM(REGEXREPLACE(A1,"^(Sun|Mon|Tue|Wed|Thu|Fri|Sat)\,(.)\s+\+?[0-9]{4}.$","$2"))) as a way to convert `Thu, 01 Oct 20... (more) |
— | over 3 years ago |
| Question | — |
Data validation applied to tickboxes Given ColumnA contains a Text header (A1), an allowed maximum number (A2), fifty tickboxes (A3:A62) and a formula in A63 of: =countif(A3:A62,TRUE) how can I block application of more than the allowed maximum number of ticks, or at least warn that the limit has been exceeded? There is a Q... (more) |
— | over 3 years ago |
| Answer | — |
A: How to calculate the Hamming weight of a binary string? I believe the answer at the moment is "No" but a very simple formula in LibreOffice 5.4.3.2 can achieve the desired result: =LEN(SUBSTITUTE(B1;0;"")) where B1 contains: =BASE(A1;2;8) as valid and A1 contains the number in decimal. The B1 cell would not be required with this ver... (more) |
— | over 3 years ago |
| Question | — |
How to calculate the Hamming weight of a binary string? A presently unanswered question on Stack Overflow asks: >Does there exist a popcount function in libreoffice calc? and clarifies that "I often use BASE(A1;2;8) to show a number in binary". (more) |
— | over 3 years ago |
| Answer | — |
A: Count the number of occurrences in a text string Although spreadsheets were never really intended for text processing, I think a formula solution is possible (LibreOffice 5.4.3.2): For the first example above: =(LEN(A1)-LEN(SUBSTITUTE(A1,"XYZ","")))/LEN("XYZ") and for the second example: =(LEN(B1)-LEN(SUBSTITUTE(B1,"Mary","")))/... (more) |
— | over 3 years ago |
| Question | — |
Count the number of occurrences in a text string If I have some text in a cell, how can I find the number of times another piece of text appears in it? For example, suppose `A1` contains `Peter Piper picked a peck of pickled peppers.`. `pick` occurs 2 times in this string. What formula can I put in `A2` so that it counts the occurrences of `pick... (more) |
— | over 3 years ago |
| Answer | — |
A: Multiple string concatenation in Excel In Office 365 and Excel 2019 there is a TEXTJOIN: =TEXTJOIN(",",,A1:A10) which can be written into the cell where the output is required. Assumes A1:A10 has been populated to suit. (more) |
— | over 3 years ago |