#VALUE! Problem

[XLXLXL]

When there is no entry in E for the following formula, I am getting the #VALUE! return in the formula cell:

=IF(Q244=0,"",SUMIF($E$18:$E$258,E244,$D$18:$D$258))/Q244

The best result would be an empty cell for the formula cell if there is no E...better than a 0...

Thanks

 

[VoG]

Try

=IF(ISERROR(SUMIF($E$18:$E$258,E244,$D$18:$D$258)/Q244),"",SUMIF($E$18:$E$258,E244,$D$18:$D$258)/Q244)

 

[Aladin Akyurek]

When there is no entry in E for the following formula, I am getting the #VALUE! return in the formula cell:

=IF(Q244=0,"",SUMIF($E$18:$E$258,E244,$D$18:$D$258))/Q244

The best result would be an empty cell for the formula cell if there is no E...better than a 0...

Thanks

On Excel 2007 or later:

=IFERROR(SUMIF($E$18:$E$258,E244,$D$18:$D$258))/Q244,"")

Otherwise...

=IF(ISNUMBER(V(SUMIF($E$18:$E$258,E244,$D$18:$D$258))/Q244),V(),"")

=IF(ISNUMBER(SUMIF($E$18:$E$258,E244,$D$18:$D$258))/Q244),
 SUMIF($E$18:$E$258,E244,$D$18:$D$258))/Q244,"")

In order to run the 2nd formula: Add the following code (a function [udf] in VBA) as a module to your workbook:

________________________________________
Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function
________________________________________

 

[XLXLXL]

Thankyou you guys.

VoG, I went with yours, it seems to have done the trick, thankyou...

 

[XLXLXL]

One more quick question please:

Column L is adata entry column. I would like Es (the letter E) to show up as a 0 (zero).

This is some golf stuff I'm trying to hash out. They use Es instead of zeros for a par score...

 

[VoG]

If you want the values to change as you enter them try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 12 And LCase(Target.Value) = "e" Then
    Application.EnableEvents = False
    Target.Value = 0
    Application.EnableEvents = True
End If
End Sub

 

[XLXLXL]

VoG that one gives me a run time error 13...

When I close the Code menu or window with the code I pasted, (ideally) should it also clean the spreadsheet of the Es that are already there automatically or would this just work during data entry? Either way is fine, just would like to know...

 

[VoG]

You'll need to exit Excel to clear the error. Then

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Target.Column = 12 Then
    Application.EnableEvents = False
    For Each c In Target
        If LCase(c.Value) = "e" Then c.Value = 0
    Next c
    Application.EnableEvents = True
End If
End Sub

This only changes entries as they are entered.

 

[Aladin Akyurek]

Thankyou you guys.

VoG, I went with yours, it seems to have done the trick, thankyou...

For the record: Corrected for parens...

=IFERROR(SUMIF($E$18:$E$258,E244,$D$18:$D$258)/Q244,"")
 
=IF(ISNUMBER(V(SUMIF($E$18:$E$258,E244,$D$18:$D$258))/Q244),V(),"")
 
=IF(ISNUMBER(SUMIF($E$18:$E$258,E244,$D$18:$D$258)/Q244),
     SUMIF($E$18:$E$258,E244,$D$18:$D$258)/Q244,"")

 

[XLXLXL]

VoG...no error message, but no change with regards to the E.

I tried pulling data from the source and from another entry in the spreadsheet (I don't know if that matters). The source data is a little inconsistent in that sometimes there is an extra column for a playoff score when players tie at the end of a tournament. Also, one tournament is 5 rounds, so I have an extra column for the round score. There is enough room to paste the line, and everything outside the entry box is protected, but I have to move some of the data, including the box that contains the E, one time a little to the right. I hope this isn't affecting anything. As I mentioned, I did try to use correctly formatted data from inside the work sheet (sheet 5) to test the code, also...

OK...just checked. If I type the E, the code works. However, if I paste the E, the code doesn't work. The data is pulled copy/paste from the source...