You get a circular reference because you have
=ARRAYFORMULA(C2:C - A3:A)
in column C. This is circular reference because column C references itself (C2:C) in the formula.
What you need is something like the following in column C
=ARRAYFORMULA(B2:C - A3:A)
A first thing to think about would be to grab all the data at once with range.getValues()
, then grab the bits you need.
so
var twoWeapons = AttackMath.getRange("C8").getValue()
var twoWeaponTable = AttackMath.getRange("A30:A48")
would become
var attackMathRange = AttackMath.getRange("A1:C48").getValues()
// note that arrays are zero based
// so C maps to 2 and 8 maps to 7
var twoWeapons = attackMathRange[2][7]
// slice to get a range of values
// slice gets all values from first index up to
// but NOT including the second index
var twoWeaponTable = attackMathRange[0].slice(29, 48)
Every call you can reduce will speed up the script. You could even just get the entire range of values on a sheet, make adjustments appropriately, then use range.setValues
once all changes are complete.
Best Answer
This is how I would do that.
Formula 1
Explained
The
sourceArray
is filtered by the firstarrayCondition_1
by means of having an entry ("<>"
= not equal and""
= an empty string). The secondarrayCondition_2
will subtract, from the current date (without time), 30 days. This is compared to thesourceArray
. The thirdarrayCondition_3
will filter column B for instances where it matches the word "Success". TheCOUNT
function usesvalue_1
to count the instances and it will ignore#N/A
(COUNTA
doesn !!).Formula 2
Explained
The
QUERY
function is build, almost the same as theFILTER
function in formula 1. Thedata
is the same as thearraySource
. The beginning of the query starts with aSELECT
statement, which is related to SQL, followed by COUNT(A). The count is performed on column A, when column A contains something (IS NOT NULL
) AND column B must match the stringSuccess
AND the dates from column A are greater equal (>=
) to the current date minus 30 days:Pay close attention to how the date is being prepared: Language Elements, Literals
The
TEXT
function offers the possibility to format the date, as described above.The header part starts with
LABEL
and calls for COUNT(A), which is column A. The replacement text of the header must be an empty string (''
). You need to do that, because otherwise the header is included (as count). If the test of theIFERROR
function yields an error (#N/A
), then it must display the valueNo successes
.Remark
The first formula fooled me the first time, when I used the
COUNTA
function. This gives a count of 1 if there are no matches (@Nick5a1: thanks !!). Therefore I created the second formula, that ultimately lead to a revised first formula !!Both formulae are quite logical to build. The catchy part for the first is to get rid of the time notation when you use
NOW()
and (very important) to use theCOUNT
function. For the second formula you need to really understand how theQUERY
function works, but allows for a better error handling.Ultimately I would chose for the second formula.
Example
See example file you created yourself.