not sure if I'm using the correct formula for the result I want #2

I'm trying to calculate a 'low net' score. A persons 'actual score' minus
'their handicap' = 'low net' score.

I have this worksheet setup like this;

A4:A23 = list of names
Columns B-U have the weekly calculated low net scores
B4:U23 = the calculated 'low net' scores using this formula
=SUM(Scorecard!B4-Scorecard!Y4); (this is for player one)
  Scorecard!B4 is player one's first week 'actual score', Scorecard!Y4 is
player one's calculated 'handicap'.

In Row B25:U25 I have this formula {=MIN(IF(B$4:B$23>0,B$4:B$23))}, to pull
the lowest score of all persons for that week.

My problem is this;
I'm getting the #DIV/0 error and I don't know how to get around that because
we do have occational zero's "0" for scores when people don't show up. The
error is coming from my "handicap" formula
=((SUM(B4:C4)/COUNTIF(B4:C4,">0"))-36)*0.8

I have two bits of data to work around;
How can I write a formula to work above zero AND ignore the #DIV/0 cells?
I'm referring to B25:U25 formula.

Thanks,
Brad






0
brad4668 (5)
6/17/2009 10:50:06 AM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
891 Views

Similar Articles

[PageSpeed] 11

Hi,

Please only post a question once.  For the answer to this see your previous 
post.
-- 
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Brad" wrote:

> I'm trying to calculate a 'low net' score. A persons 'actual score' minus
> 'their handicap' = 'low net' score.
> 
> I have this worksheet setup like this;
> 
> A4:A23 = list of names
> Columns B-U have the weekly calculated low net scores
> B4:U23 = the calculated 'low net' scores using this formula
> =SUM(Scorecard!B4-Scorecard!Y4); (this is for player one)
>   Scorecard!B4 is player one's first week 'actual score', Scorecard!Y4 is
> player one's calculated 'handicap'.
> 
> In Row B25:U25 I have this formula {=MIN(IF(B$4:B$23>0,B$4:B$23))}, to pull
> the lowest score of all persons for that week.
> 
> My problem is this;
> I'm getting the #DIV/0 error and I don't know how to get around that because
> we do have occational zero's "0" for scores when people don't show up. The
> error is coming from my "handicap" formula
> =((SUM(B4:C4)/COUNTIF(B4:C4,">0"))-36)*0.8
> 
> I have two bits of data to work around;
> How can I write a formula to work above zero AND ignore the #DIV/0 cells?
> I'm referring to B25:U25 formula.
> 
> Thanks,
> Brad
> 
> 
> 
> 
> 
> 
> 
0
6/18/2009 3:52:01 AM
SORRY!
I didn't mean to post twice...after sending, it got stuck in my outbox. 
While trying to fix the issue I inadvertantly sent it twice.


"Shane Devenshire" <ShaneDevenshire@discussions.microsoft.com> wrote in 
message news:E4483D62-15CA-469E-989C-C13059321C0F@microsoft.com...
> Hi,
>
> Please only post a question once.  For the answer to this see your 
> previous
> post.
> -- 
> If this helps, please click the Yes button.
>
> Cheers,
> Shane Devenshire
>
>
> "Brad" wrote:
>
>> I'm trying to calculate a 'low net' score. A persons 'actual score' minus
>> 'their handicap' = 'low net' score.
>>
>> I have this worksheet setup like this;
>>
>> A4:A23 = list of names
>> Columns B-U have the weekly calculated low net scores
>> B4:U23 = the calculated 'low net' scores using this formula
>> =SUM(Scorecard!B4-Scorecard!Y4); (this is for player one)
>>   Scorecard!B4 is player one's first week 'actual score', Scorecard!Y4 is
>> player one's calculated 'handicap'.
>>
>> In Row B25:U25 I have this formula {=MIN(IF(B$4:B$23>0,B$4:B$23))}, to 
>> pull
>> the lowest score of all persons for that week.
>>
>> My problem is this;
>> I'm getting the #DIV/0 error and I don't know how to get around that 
>> because
>> we do have occational zero's "0" for scores when people don't show up. 
>> The
>> error is coming from my "handicap" formula
>> =((SUM(B4:C4)/COUNTIF(B4:C4,">0"))-36)*0.8
>>
>> I have two bits of data to work around;
>> How can I write a formula to work above zero AND ignore the #DIV/0 cells?
>> I'm referring to B25:U25 formula.
>>
>> Thanks,
>> Brad
>>
>>
>>
>>
>>
>>
>> 


0
brads6399 (10)
6/18/2009 7:24:49 PM
Reply:

Similar Artilces:

Price List #2
I created a profile for an Inventory person, when she goes into price list and clicks on 'currency id' it says she does not have enough priviliges. Does she have access to the 'Currencies' window? "rcr" wrote: > I created a profile for an Inventory person, when she goes into price list > and clicks on 'currency id' it says she does not have enough priviliges. > > ...

Usiing a formula to Conditional Format XL2007
Hi all, I'm trying to use the following formula to format a single row if any of the cells ($AD$149 or $AL$149 or $AS$149 or $AZ$149) is equal to or greater than the target cell ($M$149) =OR($AD$149=>$M$149,$AL$149=>$M$149,$AS$149=>$M$149,$AZ$149=>$M$149) All comments/pointers welcomed. -- Steve Efficiency is intelligent laziness. - Anonymous Are you getting a message in the CF when you try to enter that formula? Just reverse the operators. Instead of using => use >=. =OR($AD$149>=$M$149,$AL$149>=$M$149,$AS$149>=$M$149,$AZ$149>=$M...

Programming using user forms
I have a workbook with 69 worksheets. 68 of the worksheets are formatted exactly the same. Each represents a different jurisdiction. The 69th sheet is called "Pivots". I have code that creates csv files by drilling down on each pivot table. The code is something like... Dim pt as PivotTable For each pt in Worksheets("Pivots").Pivottables Do some stuff Next pt This works very well but I may always want to do the "stuff" on every pivot table. Is there a way, with user forms or something, to select certain pivot tables (based on their name, maybe?). Ma...

If Statment #2
I need help with a formula that would do the following: for example i you has a list of leters in B1:B10 and a list of numbers for A1:A1 what I need to know is the fomula that would look at the letters i B1:B10 and if they met a value that I needed then would count th numbers in A1:A10. So, if B1:B10 = A then it would count the numbe value for A in A1:A10. Hope that made sense, if not I will try t clerify, thanks for the hel -- Message posted from http://www.ExcelForum.com Hi try =INDEX(A1:A10,MATCH("A",B1:B10,0)) -- Regards Frank Kabel Frankfurt, Germany > I need help with ...

Leave mail on ISP using exchange
Hello, I am trying to figure out a way for exchange to leave mail at the ISP. I have a user who is used to using POP3 and wants to look at his mail at work using exchange and wants to use POP3 at home. The only thing that I can think of is to create another account at the ISP and alias the mail to that account and then for exchange not to download that mail. Thank You, Joe Bucar Set up a VPN and have him pop it from your Exch server. or better yet, have him use OWA. On Mon, 20 Sep 2004 18:48:20 -0400, "Joe Bucar" <jbucar@hotmail.com> wrote: >Hello, >I am trying ...

Printout Method
In building a form to allow users to print a "blank form" I've constructed as an Access Report, I'd like the user to be able to select the number of copies to print. It looks like the PrintOut Method would be a good choice for this, but I'm not smart enough to know quite how to get the "Copies" argument to get its input from the textbox on my form, where the user will enter the number of copies they'd like to print. Suggestions? -- Thanks, Croy Does the report have a record source? -- Duane Hookom Microsoft Access MVP If I have helped you, please help...

use the insert key
I use a table in word and I want to insert part of a date over a date using the insert key. How can I turn on the insert key pad? I'm not aware of any "insert key pad." The Insert key toggles between Insert and Overtype, and it's not clear which of these you're trying to do. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "ladyfisher" <ladyfisher@discussions.microsoft.com> wrote in message news:B4E25894-A127-41CE-AD2C-FB251DD83B52@microsoft.com... >I use a table in word and I w...

Unable to log in to Hotmail using Windows Live Mail
For the past couple of days I have been getting the following error when I try to log in to my hotmail account. I can log in to my hotmail account using a browser (firefox). I can log in to my account using WLM on another computer. I have not touched the settings and have not got any new anti-virus software. ========================================== Unable to send or receive messages for the Hotmail (***) account. To send and receive messages in your Hotmail account, go to http://hotmail.live.com on the Web, or try again later. To get help from Windows Live Customer Support,...

Like criteria on a combo box ot working the way I want... Please help!
All, Below is a the standard code I use in a combo box. Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[llListingID] = " & str(Nz(Me![cboTerritoryName], 0)) If Not rs.EOF Then Me.Bookmark = rs.Bookmark Me.Form.AllowAdditions = False The query used to find the data is... SELECT qryLister.llListingID, qryLister.llTerritoryName FROM qryLister WHERE (((qryLister.llTerritoryName) Like "*"+[Enter any part of Territory Name:]+"*")); All works fine for the first look-up. But in order to refresh the combo box so it allows one to &...

Not sure why this code isn't working
Hello, I have a subform in Access 2003 with these fields, type of day, OLPEndDate, OLPBeginDate. At the end of each row, I want to calculate the total day taken for that particular vacation request, so if a person took 12/1/2009-12/2/2009 then the last field "TotalOLPTaken" should calculate as 1. What's happening is that it does calculate but all of the rows calculate to the same number. If I put the cursor in the second row and that row happens to have 12/4/2009-12/6/2009, then even the row with 12/1-12/2 calculates as 2. Then if I put the cursor back in the fi...

Kirk Allen Evans---? how do I use your code to update my xmlDocument?
Kirk, The other day you very kindly explained how the client/server thing works. May I ask just one more question? Could you give me an example of what code I would put in the client html to post back the user input to the server xmlDocument? You gave me: One way is to use the Request.Form collection to read the values directly out of the post. System.Xml.XmlDocument doc = new System.Xml.XmlDocument(); doc.Load(Server.MapPath("data/xmlfile1.xml")); if(!IsPostBack) { System.Xml.XmlDocument doc = new System.Xml.XmlDocument(); doc.Load(Server.MapPath("data/xmlfile1.xml&...

Choose record info using ComboBox
I have tblProjects (Key = ProjectID) I made a form (record source = tblPtojects) containing all the fields form tblProjects I want the user to pick a project using a combobox, and have the other fields updated with the project's info, instead of navigating through projects using the form buttons. Currently, when I select a project in the combo, nothing happens. What do I need? Antonio. "AntonioMachado" <u35762@uwe> wrote in message news:7507769e3a4da@uwe... >I have tblProjects (Key = ProjectID) > > I made a form (record source = tblPtojects) containing all...

Previously used file option GONE? help please
Win98SE 'Office XP Pro' In Excel I am unable to find how to enable "open previosly used files" or words to that effect. I have checked in "options" but cannot see it there. When I used it previouly I was able to select the number of files last used, which would be available for me to select when starting EXCEL. TIA Hi do you mean the option in the 'File' menu (at the bottom)? -- Regards Frank Kabel Frankfurt, Germany Terry wrote: > Win98SE > 'Office XP Pro' > > In Excel I am unable to find how to enable "open previosly used...

Money 2008 #2
Are they making a UK version as we have not had one for some time, I use 2006 version which was last they produced for us. I'm not aware of any new one at present. The last UK version was 2005. On the Money support page, it says: "Note: Money 2006, 2007, and Money Plus are only supported in the United States and Japan. Money 2005 is supported in countries outside the United State and Japan, including France and the United Kingdom. Money 2003 and 2004 are no longer supported. Please contact the appropriate support center for your product." Which implies to me, that Money ...

printing problems #2
When some of my coworkers print timelines, the arrows show up but notthe text of the milestones. We use different kinds of printers or print to Adobe. Has anybody else seen this? What's the fix. What version of Visio? Are all service packs installed? What operating system(s)? Which printers are successful and which ones are not? Are all printer drivers up to date? Randall Arnold "Sherrie" <sb6633@sbc.com> wrote in message news:%23PAQ07ioDHA.1708@TK2MSFTNGP12.phx.gbl... > When some of my coworkers print timelines, the arrows show up but notthe > text of the mi...

Not Sure
Hi, Can anyone help? If I use a Form selecting from the range "make" I want the next Form to be able to pick from the "model" range of that make. So if I picked Ford in the 1st form, the 2nd form will only give me the options of Mondeo LxTD or Mondeo Aspen. The other information I can already obtain using Vlookup() but I cannot find a way of inputting the "model" range into the Vlookup() In cell A1 the form will bring in the option picked (ford Or Vauxhall) What I need is in cell B2 I want to be able to pick from only the cars made by ford or vauxhall...

Do You Want To Know For Sure That You Are Going To Heaven? The reason some people don't know for sure if they are going to Heaven when they die is because they just don't know. The good news
The reason some people don't know for sure if they are going to Heaven when they die is because they just don't know. The good news is that you can know for sure that you are going to Heaven which is described in the Holy Bible as a beautiful place with no death, sorrow, sickness or pain. God tells us in the Holy Bible how simple it is to be saved so that we can live forever with Him in Heaven. "For if you confess with your mouth Jesus is Lord and believe in your heart that God raised Him from the dead, you WILL BE SAVED." (Romans 10:9) Over 2000 years ago God came from ...

Protecting a document while still using it
I am trying to make a document where information can be typed into it, without the threat of losing any of my original form. The cells that you want to be able to type into can be unlocked by formatting the cell. Choose Format/Cell then click the Protection tab and uncheck the box marked "Locked" You can then protect your workesheet and only the unlocked cells will be available for change. "Jolene" <anonymous@discussions.microsoft.com> wrote in message news:D58D9A85-CFE4-4E24-8409-FD6D98A4CDE3@microsoft.com... > I am trying to make a document where information...

how to use atm tranactions in check register?
heyas all! just trying my money program(2001). i use my atm/checkcard alot. how can i enter a transaction using atm card? when i try to enter a new entry i get the check register, asking for a check number? is there a workaround for this? atm/checkcard is my checking account? help The Dog! thanks. So leave the check number blank. "The Dog" <one_junkdog@hotmail.com> wrote in message news:231201c38493$26a851d0$a001280a@phx.gbl... > heyas all! just trying my money program(2001). i use my > atm/checkcard alot. how can i enter a transaction using > atm card? when i t...

Protecting Formulas #4
Is it possible to protect a formula and still be able to edit the contents in the cell without changeing the formula.-- Brose No But: you can have your calculations in one set of cells. In another set of cells reference the original set. (=A1 etc) and have the second set be your Display set. You can then write over the second set as needed, and "refresh defaults" by re referencing the original set. Ha "brose" wrote: > Is it possible to protect a formula and still be able to edit the contents in > the cell without changeing the formula.-- > Brose >...

Publisher 2003 printing issue #2
Hi, i have msPub 2003 on Vista 64. It seems I can only print one copy of a document with publisher. No problem with other programs. Any idea's would be very appreciated! Erwin Is your printer driver current? Look at the printing preferences in the printer folder. Be certain the default is set at one copy. Publisher is more critical about printer drivers. If you decide to re-install your printer driver, completely remove the driver before you install/reinstall. Is yours a Bluetooth printer? There is a Hotfix. When you use a Bluetooth printer to print multiple documents on a Windo...

hours used
i need to calculate hours and minutes between eks. "clock" 18:30 and 03:30 into hh:mm it does not take into account that it's 9 hours not 14,5 excel 2003 =MOD(end-start,1) or =end-start+(start>end) -- Regards, Peo Sjoblom "richardandre" <richardandre@discussions.microsoft.com> wrote in message news:82FAAC27-016A-4CA6-80C9-8286B2D1A7D9@microsoft.com... >i need to calculate hours and minutes between eks. "clock" 18:30 and >03:30 > into hh:mm > it does not take into account that it's 9 hours not 14,5 > > excel 2...

Formula Query
I'm hoping someone out there may be able to help with a formula that would be of tremendous help to me. As someone who does a good deal of trading on ebay I use excel 97 to keep a running tab on my profit margins. The problem is I really want it to be more accurate. As ebay charges a final 'Value Fee' based on a percentage of the final sale price, I wanted to know how I could use this in a formula. The fees range as follows: Selling price of �0 - �29.99 - Ebay takes - 5.25% Selling price of �30.00 - �599.99 - Ebay takes - 3.25% Selling price of �600.00 and up - Ebay takes - 1....

using letterhead
How do I type on letterhead created in Publisher?? Import into a word document?? Save the letterhead as a picture. Group all the objects, right-click, save as picture. There are different format & resolution options. Insert into Word. -- Mary Sauer http://msauer.mvps.org/ "anita" <anita@discussions.microsoft.com> wrote in message news:59064E42-6704-448D-8BB9-B45360A62149@microsoft.com... > How do I type on letterhead created in Publisher?? Import into a word > document?? ...

419 Nigerian scam
Just got one of these "wonderful" emails and want to read the full header of the email. I know in OE you can get properties and see this. When I click on Options in O2K and the next tab, I don't get the full header. Any ideas or utilities that will allow me to read the full header to see where this came from. Thanks Open the message - go to View, Options. You can see the full Internet message headers there, select, copy & paste into the forwarded messages. �� wrote: > Just got one of these "wonderful" emails and want to read the full > header of the email....