Myujth

I'm currently attempting to write a formula that averages together 4 values that may or may not be filled. The formula searches a string for the value in each cell, and it then averages those values together.

The problem is that if the value isn't found, I need it to return null, because if I return 0 it doesn't average properly, and a blank "" value returns a #VALUE error, even if wrapped in a the VALUE() function and using the AVERAGEA() function.

I also tried writing a very simple macro that just returns null, but from what I understand Excel doesn't like that.

So is there some way to return null that I've overlooked? I know that in GoogleSheets the formula =IFERROR(1/0) would do the trick - is there a similar workaround?

I'm honestly expecting that I'll have to change the formula to all be in VBA, but I wanted to see if I've overlooked something here.

Thanks in advance!

Edit:
So the code I'm using looks like this (I've shortened it down a bit to make it easier):

=AVERAGE(IFERROR(VALUE(MID($AF2, FIND("Hit:", $AF2) + 4, 3)), ""), IFERROR(VALUE(MID($AH2, FIND("Hit:", $AH2) + 4, 3)), ""))

In cells AF2 and AH2 are long strings, and the number I'm searching for is always right after it says "Hit:" (if the number is there). So with AVERAGEIF() I'm not sure how to input the range argument there

You can use iferror() to return whatever value you wish:

=iferror(your_function,"")

or

=iferror(your_function,"Check")

even numbers...

The only solution I can think of is to use the AVERAGE() function as an array. The "" is treated as a string in regular AVERAGE() or SUM() functions, but is seen as a null value in arrays.

Try this (as an array, Ctrl + Shift + Enter):

IFERROR(VALUE(MID($AF2:$AH2, FIND("Hit:", $AF2:$AH2) + 4, 3)), "")

So I had to give up on trying to do this without VBA. I wrote a function that just goes through and adds together the values found after the searchFor argument. If anyone ever runs into this issue, here's what I did:

'''Searches for a string in each cell in the range, then pulls the value
'''averages together all values that were found offset number of characters away from the string
Function averageWithErrors(searchFor As String, rng As range, offset As Integer, extractLength As Integer) As Double

'Declare Variables
Dim characterPosition As Integer
Dim num As Double
Dim count As Integer
Dim cellCheck As range
Dim cellTotals As Integer


'Set Variables
count = 0
num = 0

'Check each cell
For Each cellCheck In rng
 If InStr(cellCheck.Value, searchFor) Then 'Check if cell has the string
 characterPosition = InStr(cellCheck.Value, searchFor)
 num = num + Val(Mid(cellCheck.Value, characterPosition + offset, extractLength)) 'Add the value to the running total
 count = count + 1
 End If
Next cellCheck

averageWithErrors = num / count 'return the average of the values found

End Function