Google-sheets – Google Sheets function returns an incorrect value

formattingformulasgoogle sheets

I have a very simple function for every cell in column D, for example:

D24 =C24-(B24*N$2)

Example with values:

D24 =10-(1*6,5)

This works; however, not all the time. For cell D24 this returns a date 30-12, while B24 & C24 being empty.

Best Answer

if you see weird values like dates in your calculations, then its most likely due to formatting. to fix it do this (if you don't want decimals then click on More Formats and customize the visual as you wish):

Related Solutions

Google Sheets – Non-Deterministic Parameters to Custom Function

I've made some research and it seems like an old problem, even from old MS Excel versions.

I think the best/easiest way is to create your own randomization function. This way random value won't be regenerated. You use it the same way as before =plusOne(myRandom())

function plusOne(i) {
  return i + 1;
}

function myRandom() {
  # Some function
  return Math.random();
}

You could also shorten it =myRandom()

function myRandom() {
  # Some function
  return Math.random() + 1;
}

Google Sheets – COUNTIFS Returns Incorrect Result When Applied to QUERY Output

You are hitting some strange behavior of Sheets, quite possibly a bug. I will first describe a way to reproduce it, and then a way to avoid it.

To reproduce:

Put some text in cells A1 and A3, leaving A2 blank. In cell B1, enter =query(A1:A3, "select *"). The content of cells B1:B3 will now appear to be identical to A1:A3.

But B2 is not really blank. Specifically:

=isblank(B2) returns FALSE, while =isblank(A2) is TRUE
=istext(B2) returns TRUE, while =istext(A2) is FALSE
=counta(B1:B3) returns 3, while =counta(A1:A3) is 2.
=countif(B1:B3, "<>") returns 3, while =countif(A1:A3, "<>") is 2.

To avoid

Don't use countif or counta on the results of query (at least until this bug is fixed). As an alternative, use filter command to apply the criteria, and then counta to count the (nonempty) results. Thus, instead of

=Countifs(Query!$B$2:$B$500, "221*", Query!$E$2:$E$500, "<>")

use

=counta(filter(Query!$B$2:$B$500, regexmatch(Query!$B$2:$B$500, "^221")*len(Query!$E$2:$E$500)))

which is admittedly more complex but gets correct results. There are two filter criteria, which are imposed as logical AND by multiplication:

column B entry matches the regular expression ^221 (equivalent to wildcard pattern 221*)
column E entry has positive length -- this is a robust check that is not affected by the strange behavior of query.

Since in the filtered results, the column B is guaranteed to be nonempty, counta will return the number of all rows that match the filter.

An alternative, slightly shorter formula with the same result:

=sum(arrayformula(regexmatch(Query!$B$2:$B$500, "^221")*(len(Query!$E$2:$E$500)>0)))

This evaluates the two criteria as True/False, coerces them to 0-1 integers by multiplication, and sums the results.