#VALUE! Problem

XLXLXL

Board Regular
Joined
Jan 10, 2011
Messages
52
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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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:
Rich (BB code):
=IFERROR(SUMIF($E$18:$E$258,E244,$D$18:$D$258))/Q244,"")

Otherwise...
Rich (BB code):
=IF(ISNUMBER(V(SUMIF($E$18:$E$258,E244,$D$18:$D$258))/Q244),V(),"")

Rich (BB code):
=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

Board Regular
Joined
Jan 10, 2011
Messages
52
Thankyou you guys.

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

XLXLXL

Board Regular
Joined
Jan 10, 2011
Messages
52

ADVERTISEMENT

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

Legend
Joined
Jun 19, 2002
Messages
63,650
If you want the values to change as you enter them try this: right click the sheet tab, select View Code and paste in

Code:
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

Board Regular
Joined
Jan 10, 2011
Messages
52

ADVERTISEMENT

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

Legend
Joined
Jun 19, 2002
Messages
63,650
You'll need to exit Excel to clear the error. Then

Code:
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Thankyou you guys.

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

For the record: Corrected for parens...
Rich (BB code):
=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

Board Regular
Joined
Jan 10, 2011
Messages
52
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,122,561
Messages
5,596,846
Members
414,107
Latest member
Tigretto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top