We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

copy formula not working [closed]

asked 2017-04-05 03:28:59 +0200

Pheeble gravatar image

updated 2020-08-11 13:08:41 +0200

Alex Kemp gravatar image

In Calc (Xubuntu 16.04), I have a spreadsheet containing two sheets. One of the sheets contains a row of cells containing a formula with absolute references that displays the totals for a given month extracted from the other sheet.

The formula to get the totals for March (ie. month = 3) is: {=SUM(IF(MONTH(2017_Itemised.H3:2017_Itemised.H1048576)=3,2017_Itemised.I3:2017_Itemised.I1048576,0))}

I realised that the total for April was displaying 0.0 despite valid values for April being in the '2017_Itemised' sheet.

I tried copying and pasting the formula from the formula bar for 'March' into 'April', but in selecting the formula bar the brace characters { and } disappear, so the resulting formula is: =SUM(IF(MONTH(2017_Itemised.H3:2017_Itemised.H1048576)=4,2017_Itemised.I3:2017_Itemised.I1048576,0))

I then tried manually editing the formula in the formula bar to insert the braces, but the cell then just displays the formula string as text without calculating anything.

I have no idea what is going on here. The formula works for every other cell except this one, but as far as I can see the cells are identically configured in every way. Is there some special way to copy such a formula to another cell to make it work?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-11 13:09:08.699752

1 Answer

Sort by » oldest newest most voted

answered 2017-04-05 03:50:52 +0200

Pheeble gravatar image

Answering my own question: I got the formula to work by using 'Function Wizard' to insert the formula and ticking the 'Array' checkbox.

Eventually I realised that, after copying and pasting the array formula, I should have pressed 'Ctrl+Shift+Enter' instead of just 'Enter'. Talk about obscure input requirements. I don't see why 'Enter' wouldn't be sufficient.

edit flag offensive delete link more


Because in array/matrix mode formulas calculate differently than in "normal" mode. As you have encountered. The difference is that for functions that expect a single scalar value as argument (here for example MONTH and IF) in normal mode the implicit intersection of the formula cell position and the range reference given is passed as argument, while in array mode the function iterates over every element of the range reference and the result is an array instead of a single value.

erAck gravatar imageerAck ( 2017-04-06 14:52:32 +0200 )edit

Question Tools

1 follower


Asked: 2017-04-05 03:28:59 +0200

Seen: 1,000 times

Last updated: Apr 05 '17