Communities

Writing
Writing
Codidact Meta
Codidact Meta
The Great Outdoors
The Great Outdoors
Photography & Video
Photography & Video
Scientific Speculation
Scientific Speculation
Cooking
Cooking
Electrical Engineering
Electrical Engineering
Judaism
Judaism
Languages & Linguistics
Languages & Linguistics
Software Development
Software Development
Mathematics
Mathematics
Christianity
Christianity
Code Golf
Code Golf
Music
Music
Physics
Physics
Linux Systems
Linux Systems
Power Users
Power Users
Tabletop RPGs
Tabletop RPGs
Community Proposals
Community Proposals
tag:snake search within a tag
answers:0 unanswered questions
user:xxxx search by author id
score:0.5 posts with 0.5+ score
"snake oil" exact phrase
votes:4 posts with 4+ votes
created:<1w created < 1 week ago
post_type:xxxx type of post
Search help
Notifications
Mark all as read See all your notifications »
Q&A

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.

Posts tagged spreadsheet-formula

Synonyms: excel-formula libreoffice-calc-formula openoffice-calc-formula google-sheets-formula formula

Use for questions about spreadsheet formulas using common spreadsheet formula syntax. A spreadsheet formula is written in a cell; it starts with an equal sign and is followed by an expression, i.e., =5*2.
Tag Wiki

Use this tag for questions about spreadsheet writing formulas.

To make it easier for readers and people willing to answer your question, whenever it is possible, use function names in English and set your spreadsheet to use United States as the spreadsheet locale setting; otherwise, include in your question the language used for the functions names and the locale used, as this affects the function parameter used among other settings.

When using cell references as part of a formula, clearly describe the cell reference content. When necessary, include sample input and the corresponding expected result as text directly into the question body. You might include links to external resources, but remember that all the relevant details to answer the question should be included directly in the question body. This is because the external resource might not be available later, and if you have not included all the details, the question will be unclear for future readers.

Use it with the tag corresponding to the spreadsheet application used to write and calculate the formula when appropriate.

Spreadsheet application tags

  • excel
  • google-sheets
  • libreoffice-calc
  • openoffice-calc
60%
+1 −0
Q&A Drop-down values in Excel cells are not specified in Data Validation rules and global search didn't find them in workbook, so where do they come from?

On top of values seemingly coming from nowhere in multiple columns, they also affect each other's behavior: In the GIF below, if the cells in the left column have no value selected, then there is n...

1 answer  ·  posted 3mo ago by toraritte‭  ·  edited 3mo ago by toraritte‭

60%
+1 −0
Q&A 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...

1 answer  ·  posted 3y ago by pnuts‭  ·  last activity 8mo ago by Wicket‭

66%
+4 −1
Q&A 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...

1 answer  ·  posted 3y ago by pnuts‭  ·  edited 9mo ago by ArtOfCode‭

50%
+2 −2
Q&A 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) f...

1 answer  ·  posted 3y ago by pnuts‭  ·  edited 9mo ago by Wicket‭

60%
+1 −0
Q&A 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 ...

1 answer  ·  posted 3y ago by pnuts‭  ·  edited 9mo ago by Wicket‭

71%
+3 −0
Q&A 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 | PER...

2 answers  ·  posted 3y ago by pnuts‭  ·  edited 9mo ago by Wicket‭

66%
+2 −0
Q&A 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...

1 answer  ·  posted 3y ago by pnuts‭  ·  last activity 3y ago by Alexei‭

60%
+1 −0
Q&A 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 ...

1 answer  ·  posted 3y ago by pnuts‭  ·  last activity 3y ago by Alexei‭

57%
+2 −1
Q&A 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,"^...

1 answer  ·  posted 3y ago by pnuts‭  ·  last activity 3y ago by Alexei‭

50%
+0 −0
Q&A 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 ...

1 answer  ·  posted 3y ago by pnuts‭  ·  last activity 3y ago by Alexei‭

42%
+1 −2
Q&A 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...

1 answer  ·  posted 3y ago by pnuts‭  ·  last activity 3y ago by Alexei‭

50%
+0 −0
Q&A 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".

1 answer  ·  posted 3y ago by pnuts‭  ·  last activity 3y ago by Alexei‭

50%
+0 −0
Q&A Copy a sparse matrix with removal of blanks

A user of Web Applications gave the following as an example: https://docs.google.com/spreadsheets/d/1nPTBFXaIgpruPgNoWFSBUL2yKaibjeNJNExhnHVr8LE/edit#gid=0 and asked: How do I read a long ro...

0 answers  ·  posted 3y ago by pnuts‭  ·  last activity 3y ago by pnuts‭

50%
+0 −0
Q&A 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":. ...

0 answers  ·  posted 3y ago by pnuts‭  ·  last activity 3y ago by pnuts‭

50%
+0 −0
Q&A 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 rai...

0 answers  ·  posted 3y ago by pnuts‭  ·  last activity 3y ago by pnuts‭

66%
+2 −0
Q&A 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 c...

0 answers  ·  posted 3y ago by pnuts‭  ·  last activity 3y ago by pnuts‭

50%
+0 −0
Q&A 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("sha...

0 answers  ·  posted 3y ago by pnuts‭  ·  last activity 3y ago by pnuts‭

60%
+1 −0
Q&A 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 a...

0 answers  ·  posted 3y ago by pnuts‭  ·  last activity 3y ago by pnuts‭