[Bug]: ADDRESS function ignores `defaultValue` when user provides empty value for an optional parameter

[marcin-kordas-hoc]

Description

When a user passes an empty argument to ADDRESS (e.g. =ADDRESS(1,1,) or =ADDRESS(1,1,1,)), the absNum and a1Style parameters should use their declared defaultValue (1 and true respectively). Instead, EmptyValue is coerced to 0/false, producing incorrect results.

Note: This applies only to ADDRESS. Excel 2021 and Google Sheets consistently treat empty arguments as the zero-value for the type (0 for numbers, FALSE for booleans) across all other functions — that is correct behavior, not a bug. ADDRESS is the confirmed exception.

Function	Parameter	empty arg (Excel)	omitted (Excel)	HF bug?
LOG	base	#NUM! (base=0)	2 (base=10)	No — HF matches Excel
VLOOKUP	sorted	#N/A (exact match)	"one" (sorted)	No — HF matches Excel
MATCH	matchType	#N/A (exact)	1 (approx)	No — HF matches Excel
ADDRESS	absNum	"$A$1" (abs=1)	"$A$1" (abs=1)	Yes — HF gives 0
ADDRESS	a1Style	"$A$1" (a1=true)	"$A$1" (a1=true)	Yes — HF gives false

Root cause

In FunctionPlugin.coerceArgumentsToRequiredTypes, the guard checks only for undefined when selecting the defaultValue. EmptyValue passes through and is coerced to 0/false.

Fix

Add an opt-in emptyAsDefault flag to FunctionArgument metadata. When set, EmptyValue is treated as omitted (uses defaultValue). Applied only to ADDRESS absNum and a1Style parameters.

Reproduction steps

const hf = HyperFormula.buildFromArray([
  ['=ADDRESS(1,1,)'],   // empty absNum — should be "$A$1", bug: error or wrong format
  ['=ADDRESS(1,1,1,)'], // empty a1Style — should be "$A$1"
], { licenseKey: 'gpl-v3' })

HyperFormula version

3.2.0

Your framework

No response

Your environment

Node.js

[sequba]

@marcin-kordas-hoc How does Excel behave in these scenarios with LOG, VLOOKUP, HLOOKUP, MATCH and ADDRESS? I see LOG(10,) returns #NUM which is different than LOG(10), so Excel distinguishes between an empty argument and no argument at all.

[marcinkordas]

You're right — Excel consistently treats empty as the zero-value for the type (0 for numbers, FALSE for booleans), not as the declared defaultValue. I went ahead and tested Excel 2021 + Google Sheets, both agree:

formula	empty arg	omitted
LOG(100,)	#NUM!	2
MATCH(1.5,range,)	#N/A	1 (approx)
VLOOKUP(1.5,range,2,)	#N/A	"one"
FIND/SEARCH("l","hello",)	#VALUE!	3
SUBSTITUTE("hello","l","L",)	#VALUE!	"heLLo"
ADDRESS(1,1,,TRUE)	"$A$1"	"$A$1" ← exception
ADDRESS(1,1,1,)	"$A$1"	"$A$1" ← exception

ADDRESS is the only confirmed exception — both absNum and a1Style treat empty as their declared default, not 0/FALSE. Fix is in #1631 (will narrow the scope to ADDRESS only - which might require different approach).

[sequba]

So just to confirm:

1. LOG, VLOOKUP, MATCH and other functions behave the same as Excel:

Formula	Excel interpets it as	HF interprets it as	Explanation
LOG(100, 2)	LOG(100, 2)	LOG(100, 2)	all params provided
LOG(100)	LOG(100, 10)	LOG(100, 10)	10 is a default value for base param
LOG(100,)	LOG(100, 0)	LOG(100, 0)	EmptyValue is coerced to 0

2. The bug affects only ADDRESS. @marcin-kordas-hoc could you fill up the following table?

Formula	Excel interpets it as	HF interprets it as
ADDRESS(2, 3, 1, FALSE(), "Sheet2")	ADDRESS(2, 3, 1, FALSE(), "Sheet2")	ADDRESS(2, 3, 1, FALSE(), "Sheet2")
ADDRESS(2, 3, 1, FALSE())
ADDRESS(2, 3, 1)
ADDRESS(2, 3)
ADDRESS(2, 3,, FALSE(), "Sheet2")
ADDRESS(2, 3, 1,, "Sheet2")
ADDRESS(2, 3, 1, FALSE(),)

[marcin-kordas-hoc]

@sequba Here's the filled table, validated in Excel desktop (Microsoft Office 2021) and cross-checked against HyperFormula (both develop and fix/empty-default-value).

1. Confirming: the bug affects only ADDRESS, and only absNum and a1Style parameters.

Formula	Excel	HF (develop, before fix)	HF (with fix)	Explanation
ADDRESS(2,3,1,FALSE(),"Sheet2")	Sheet2!R2C3	Sheet2!R2C3	Sheet2!R2C3	all params — baseline
ADDRESS(2,3,1,FALSE())	R2C3	R2C3	R2C3	sheetName omitted
ADDRESS(2,3,1)	$C$2	$C$2	$C$2	a1Style omitted → default TRUE
ADDRESS(2,3)	$C$2	$C$2	$C$2	absNum+a1Style+sheet omitted → defaults
ADDRESS(2,3,,FALSE(),"Sheet2")	Sheet2!R2C3	#NUM!	Sheet2!R2C3	absNum empty → default 1 (fixed)
ADDRESS(2,3,1,,"Sheet2")	Sheet2!$C$2	Sheet2!R2C3	Sheet2!$C$2	a1Style empty → default TRUE (fixed)
ADDRESS(2,3,1,FALSE(),)	R2C3	!R2C3	!R2C3	sheetName empty — see note below

Additional edge cases:

Formula	Excel	HF (develop)	HF (with fix)	Notes
ADDRESS(2,3,,)	$C$2	#NUM!	$C$2	both empty → defaults (fixed)
ADDRESS(2,3,,,"Sheet2")	Sheet2!$C$2	#NUM!	Sheet2!$C$2	both empty + sheet (fixed)
ADDRESS(2,3,0)	#VALUE!	#NUM!	#NUM!	pre-existing: wrong error type for absNum=0
ADDRESS(2,3,2)	C$2	C$2	C$2	absNum=2 relative row ✓
ADDRESS(2,3,3)	$C2	$C2	$C2	absNum=3 relative col ✓
ADDRESS(2,3,4)	C2	C2	C2	absNum=4 all relative ✓

2. The emptyAsDefault approach is correct — rows 5, 6, 8, 9 in the main table all match Excel after the fix.

3. Two pre-existing ADDRESS bugs (not related to emptyAsDefault):

• Empty sheetName (ADDRESS(2,3,1,FALSE(),)): Excel returns R2C3, HF returns !R2C3. ADDRESS concatenates sheetName + "!" without checking for empty string. This is a STRING param issue, not an emptyAsDefault issue.
• absNum=0 (ADDRESS(2,3,0)): Excel returns #VALUE!, HF returns #NUM!. Wrong error type in absNum validation.

4. Other functions correctly treat empty args as zero-value (NOT default):

Formula	Excel	HF	Explanation
LOG(100,)	#NUM!	#NUM!	empty → 0 (not default 10) ✓
LOG(100)	2	2	omitted → default 10 ✓
MATCH(1,{1;2;3},)	1	1	empty → 0 → exact match ✓
MATCH(1,{1;2;3})	1	1	omitted → default 1 → approx ✓

This confirms ADDRESS is the only exception on develop — emptyAsDefault scoped to its absNum and a1Style params is the right approach.

Note: The emptyAsDefault flag will also be used by the upcoming SEQUENCE function (feature/SEQUENCE branch), where empty optional args should use declared defaults (cols=1, start=1, step=1) — same behavior as ADDRESS. This is consistent with how Excel treats =SEQUENCE(3,,,) and has been validated against Excel desktop. However, on the current develop branch, ADDRESS remains the only function that needs this fix.