Distinguishing 0 from blank cell

[aasmith]

I’m trying to make a function to calculate a weighted sum, not counting any blank entries. (SUMPRODUCT doesn’t help, since it considers blank entries to be 0.) However, my code is running into the same issue:

Function CalcWeightedSum(values, weights) As Double
	If NOT isArray(values) Then
		CalcWeightedSum = values
	Else
		Dim total, sum
		total = 0.0
		sum = 0.0
		Dim i

		For i = LBound(values) to UBound(values)
			REM the following line fails to detect empty cells
			If NOT isEmpty(values(i,1)) Then
				sum = sum + values(i,1) * weights(i,1)
				total = total + weights(i,1)
			End If
		Next i

		CalcWeightedSum = sum / total

	End If
End Function

How can I detect these blanks, and skip over them? The above code considers any blank cell to be a 0, which is not helpful at all.

Note: I found some similar questions, and a couple of them pointed toward a function called queryEmptyCells(). However I cannot find any documentation or examples on how this function is used. It’s not obvious, since the arguments passed into the function are simply arrays of values. If this function is indeed the solution, what does it take as arguments? What does it return? My experiments with it didn’t teach me much.

Thank you. This has been frustrating me for a few hours now.

[PYS]

Hi

For example, if you want to delete rows for empty cells in a range:

Sub DeleteEmpty

dim oRange as object, oFilter as object, document as object, dispatcher as object

oRange = thiscomponent.sheets.getByName("Sheet1").getCellRangeByname("A1:A10")
oFilter = oRange.queryEmptyCells
thiscomponent.currentController.select(oFilter)

document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(document, ".uno:DeleteRows", "", 0, Array())

end sub

In oFilter you get a com.sun.star.sheet.XSheetCellRanges

However why not simply something like: =SUMPRODUCT(A2:A9<>"";A2:A9*B2:B9)

Regards

[karolus]

@PYS
Your formula does not, what obviously should do the UDF.

[aasmith]

Is there a way to do this as a function? I’m looking for arguments and return values, which this really needs. It’s not going to work if I have to hard-code a range into it, since my spreadsheet is going to have to do this about 60-100 times. I’m looking for something wherein I pass it numbers, and it returns the weighted average (leaving out blank entries).

[aasmith]

Also, your SUMPRODUCT solution doesn’t work for me. It runs into the same problem as @karolus’s solution. (You can see the toy data I gave him.) When there are blank entries in the values but not the weights, it seems to automatically insert a 0 for the value. This is exactly what I need to avoid.

[PYS]

Of course, I just wanted to show how to distinguish 0 from blank. You need to do the same for the weight. Something like: =SUMPRODUCT(A1:D1*A2:D2;A1:D1<>"")/SUMPRODUCT(A2:D2;A1:D1<>"")

[karolus]

Hallo

use =SUMPRODUCT( values ; weights)/sum(weights)

apart User-defined-Basicfunction cannot distinguish between blanc and zero, there is no need to distinguish because it makes no difference in this particular calculation.

edit: new formula:

=SUMPRODUCT( scores ; weights )/SUMPRODUCT(NOT(ISBLANK( scores )); weights )

[aasmith]

Unfortunately this does not work.

If the data looks like this:

90% 0% 90%

35% 20% 20% 25%

Where the first row is the scores and the second row is the weights, what I want is an answer of 66%. Your solution gives an answer of 49.5%. (Think of the first row as a student’s scores, and the second row being the weights toward a final grade. Since this student hasn’t done the 4th assignment yet, his/her grade is currently 66%.)

[karolus]

Ok - I see…try:

=SUMPRODUCT( scores ; weights )/SUMPRODUCT(NOT(ISBLANK( scores )); weights )

[aasmith]

Danke schön! That seems to have worked.

[Lupp]

(Basic considerations)
There is nothing like an “empty value”. Standard functions may give their body access to additional properties of ranges (also single cells) put on their parameter places. User functions cannot. They get either one scalar value or an array which always is 2D (may be n x 1 e.g.) and whose elements are ‘Variant’ with types ‘Double’, ‘String’ and ‘Error’ allowed. You can’t pass an arbitrary (scalar) cell property this way. Empty cells are assumed numeric 0.
However, the evaluator handling your formula will have to prepare the 2D array to pass to the function. If you put on a parameter position a subexpression like
IF(ISBLANK(Reference);Reference;"<blank>")
where Reference is an array, and enter the formula for array-evaluation, the evaluator will pass an array containing the string <blank> for any element derived from a blank cell. You may use this (or different/additional indicators) to control the execution of your user code. Of course you may also create a second parameter of your user function dedicated to pass the array of results for any condition you want to evaluate, and which may depend on any cell properties handled by standard functions.

(An alternative I would dissuade from in most cases explicitly, is to not pass any values at all to the user function but the cell ranges to work on given as addresses which are text then on their behalf. This would require to handle explicitly a lot of things automatized for standard use of functions. Low efficiency! Many possible errors/malfunctions!)