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
1168 Views

Similar Articles

[PageSpeed] 22

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:

problems #2
I have had problems with Outlook for awhile... Both Outlook Express and Outlook 2000 are loaded on my computer. Outlook 2000 worked for a while but something happened?? It stopped working and to keep things simple I began using Outlook Express. I was able to retreive and send e-mail. Yesterday after loading the Window XP sp2 Outlook Express disappeared. I can't get it back. I have tried everything to get Outlook 2000 to work to no avail. And My address book and saved email are in Outlook Express. I'm freaking out. Two error messages are popping up. When I try to get in...

Can not right click items in Outlook 2003 #2
Hello, Has anyone come across a problem in Outlook 2003 11.6359.6360 SP1 where you can not right click items and get the quick menu items? So for example, if I right click a mail message nothing comes up, versus the reply, mark as read, etc items. Thanks Much Lots of people are reporting it, but we don't have a fix yet. Are you using a script blocker? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips: http://ww...

Formula Help! #3
Hi, I need to transform a date to week date , for example: 10/11/05 to Tuesday Is there any formula can do this transform? Thanks in advance -- Meme Hi! Try this: A1 = 10/11/2005 =TEXT(A1,"dddd") This returns a TEXT value = Tuesday OR, you could just format the cell, A1, as dddd. This retains it's numeric value but will display as Tuesday. Biff "Meme" <Meme@discussions.microsoft.com> wrote in message news:64B1FB51-4EC0-42DC-97B9-B788A9586811@microsoft.com... > Hi, > > I need to transform a date to week date , for example: > 10/11/05 ...

Formula Error Message
Is there a way to not have the formula error message DIV/0! display in a cell and still keep the formula? If the formula is, e.g., =a1/b1 change it to =IF(B1=0,"",A1/B1) On Tue, 8 Mar 2005 20:01:03 -0800, Chambersteacher <Chambersteacher@discussions.microsoft.com> wrote: >Is there a way to not have the formula error message DIV/0! display in a cell >and still keep the formula? ...

How do I return to 1st slide when not in use?
I'm makeing a touch screen ppt and I want the ppt to return to the homepage after 5 min of non use. Can I do that? On 12/8/09 1:55 PM, in article 1DEBBFEC-0713-4A6E-90F5-8138A0BC3C2B@microsoft.com, "mpmb" <mpmb@discussions.microsoft.com> wrote: > I'm makeing a touch screen ppt and I want the ppt to return to the homepage > after 5 min of non use. Can I do that? Kiosk mode (Slide Show > Set Up Show > Browsed at a Kiosk) includes this functionality, but it has only worked sporadically in some versions. Which version of PowerPoint are you using? ...

imap problems #2
Hi! I have a problem while using IMAP with new Outlook 2003. When I transfer a PLAIN email message from my IMAP Inbox to any of my IMAP subfolders it is automatically convertes to HTML. Is there a solution for this? Tnx a lot in advance. Mirek ...

update 2008 12.1.2
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Once again the update will not install. After doing the down load up comes this message "You cannot install Office 2008 12.1.2 Update on this volume. A version of the software required to install this update was not found on this volume." this happened to the last update so how do we install the new update? Hope the answer is not another complete install I am sorry you are seeing this. There are numerous reasons that you may be seeing this. If you would like we can try to deal with them here - and it might he...

can't change formula toolbar height
Whenever I open either a new sheet or an existing worksheet the formula toolbar/namebox is way to high, the fonts inside the namebox are 20 and the font size in the Normal style is 20. I can change the Normal style to 10pt, but it doesn't change the formula height or the font in the namebox. And it doesn't stay changed. When I close the program and restart it the huge fonts are back. Any ideas? You should change the font name/size specified under Tools, Options, General. Then restart Excel. This will change the definition of the default Normal style for _new_ workbooks only...

Using Regular expressions in Excell 2007
I have code written in excell 2003 where I use Regular Expressions 'heavily'. In Excell 2003 you had to import the RegExp library to be able to use it. Is Regular Expressions part of VBA in Excell 2007? or do you still need to import the RegExp library? Same as in Excel 2003, the library is part of IE not Excel or VBA. -- HTH Bob "ncunha" <ncunha@discussions.microsoft.com> wrote in message news:7EF31D63-6C38-451B-9CEF-2E091DA7B2E7@microsoft.com... >I have code written in excell 2003 where I use Regular Expressions > 'heavily'...

Using Worksheets #2
I have created a database of names and associated information. Names are located in A1 thru A500 and sorted alphabetically. Corresponding data is contained in B thru I for each name. Now I would like to break up the list so that A-K is on worksheet1, L-S on worksheet2 and the remaining T thru Z on the worksheet3. What would be the best method of copying the information and then be able to add or delete names and data, and have it propagated into the correct sheet alphabetically? Thank you in advance. CS Hi! What is the format of the names column? Last_name, First._name There may be be...

Multiple Email Accounts #2
I have users that have multiple email accounts. I know that I can have all three email accounts dump into one mailbox using alias accounts. However, how can I send from different accounts in outlook? Does Exchange 2007 slove this problem. On Tue, 6 Feb 2007 11:39:02 -0800, Shannon <Shannon@discussions.microsoft.com> wrote: >I have users that have multiple email accounts. I know that I can have all >three email accounts dump into one mailbox using alias accounts. However, >how can I send from different accounts in outlook? Does Exchange 2007 slove >this problem. ...

Outlook has problems connecting to Exchange server when I use remote desktop
Hi, all I've noticed a strange problem recently: when I connect to my PC at the office from my home PC, using remote desktop, Outlook running on the office PC frequently has problems connecting to the company Exchange server. I get the following error message: Task 'Microsoft Exchange Server' reported error (0x80040115) : 'The connection to the Microsoft Exchange Server is unavailable. Outlook must be online or connected to complete this action.' The problem does go away after a while, and seems to go away if I close & re-open Outlook. I haven't noticed t...

sorting data #2
Sorry if this has been asked before, but ive searched to no avail, as I dont really know what to call the question. i have the following A B 4 2 1 5 2 3 1 6 4 3 Can I automate the sorting of this data so that for each value in column A the Values in Column B are totaled, ie it becomes A B 1 11 2 3 4 5 Thanks Andy --- Message posted from http://www.ExcelForum.com/ You could either sort your data by column A and then Data|subtotals or you could add headers and do Data|pivottable. powelly wrote: > > Sorry if this has been asked before, but ive searched to no avail, as I >...

Pasting Values #2
Hi, I have developed a quoting tool which creates an output sheet detailing all the info, What I would like to be able to do is take a copy of this and paste the values (and Format to a new workbook). I'm currently doing this manually and I unsure how to automate it! I have never used VB which I'm guessing is the only way of doing it, so please be gentle!! Thanks, Sam. -- sammy2x ------------------------------------------------------------------------ sammy2x's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29674 View this thread: http://www.excelf...

window size #2
Hello: No matter what feature in the Windows Vista Control Panel I try, the windows within GP cannot be increased in size. True, the menu and borders around the window can be increased in size. But, the window from below the menu to the bottom of the window cannot be changed. Is there a tool that will change the size of GP windows, or is this something that cna be changed in the dex.ini file? childofthe1980s Child, While you can resize most Dynamics GP windows, the result is certainly not pleasant. The only tool available to do such resize is Modifier, but you will have to resize ...

'Microsoft Money has stopped working' #2
I'm running Microsoft Money Plus Delux and Vista Home Premium. When attempting to start Money I recieve the Error message: <'Microsoft Money has stopped working' A problem caused the program to stop working correctly. Windows will close the program and notify you know if a solution is available.> I have read other threads with similar sounding problems and I have tried: +> http://support.microsoft.com/kb/942485 +> running as administrator by right clicking icon +> article 836197; MSXML 4.0 SP2 +> uninstall won't work for some reason; nor does reinstall f...

free advertiseing #2
31 yr old gets 20,000 hits per day find out how he is still doing it for zero $ more traffic to your site means more profit email me today to learn more --- MAF Anti-Spam ID: 20051014234316X0a6CpL8 ...

Information Store size #2
Hi, there I'm looking for advice We re running several E2K boxes, and one of them is growing bigger tha the others We run ESEUTIL /d several times to defrag it, but nothing good result just a little mbs free. The problem is if I add all the mailbox sizes the database size double it Do you know about any tool (not GoExchange) can I use it, (for free ??) Thanks in advance, any help will be appreciated -- Gabriel N Argentina Gabriel wrote: > Hi, there > > I'm looking for advice > We re running several E2K boxes, and one of them is growing bigger > tha the others > ...

Auto spell correction
Pretty basic...But I can't work it out. I have a table that just has a list of acronyms and then the description next to them. One acronym is 'CNA' = Customer not available However access keeps changing it to 'CAN' I've looked everywhere to turn this off, but cant find owt. I wouldnt mind but it is bound to a external report generated by another company and they wont change the acronym! Any pointers? Sorted, found the Auto correect menu and deleted CNA from the list. "Steve B" <meinthecorner@gmail.com> wrote in message news:CEB28289-2C77-42...

Repeating Formula
I have an Excel spread sheet I am working on, in which I have a column of over 100 prices. I need to have each of these prices multiplied by .47 and put into another column. So if my price is in cell 'C8', my entry into D8 would be '=C8*.47'. Was wondering if there is a way to do this without having to individualy put the formula in for each cell? Thank You ...

Pasting macro #2
I need a macro that will copy a set range of data on a row, say A2:L2, and paste it on each alternate row beginning on A15. I need it to paste on each alternate row until the cell in column A above the row to be pasted is blank. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Something like: Option Explicit Sub testme02() Dim iRow As Long With ActiveSheet iRow = 15 Do If IsEmpty(.Cells(iRow - 1, "A")) Then ...

Sorting in one tabs messes up formulas in another tab
Hi, I'm not sure if this problem was solved before so sorry if this is a repeat question. Let's say I have one spreadsheet tab that originally has the following: Col A Col B Row 1: 3 3 Row 2: 5 5 In another tab, I have the following formulas that reference the 1st tab: Col A Row 1: =average(A1:B1) - which would yield the number 3 Row 2: = average (A2:B2) - which would yield the number 5 Now let's say I resort all the data in the first tab, by col A, in descending order. The first tab now looks like: Col A Col B Row 1: 5 5 Row 2: 3 3 All is well enough until you look at the v...

Results show as a minus. WHY?
I have an application that was developed by another developer using VB.net which reside in a SQL table. I need to use some of the data from this table, however the data shows as -0.5 or - 0.99 or -1.70 etc in my Access tables. How can I get those numbers to show as a percentage with only one decimal point to the left? Example: 3.6% or 31.4 %. Any help would be greatly appreciated. No matter what I do it shows that dang "-" at the beginning. You can use the Abs function to strip off the negative Abs([YourTable].[YourField]) And you can format that as percentage either using th...

Problem with Executable File generated using Debug Build
Hi All, I am facing a really wierd problem. I am working on a project in which I have implemeted a GUI for controlling laser microscopes. Everything was going fine till I added support for Filter Wheels. The program compiles perfectly fine. Runs perfectly fine if I run it using Microsoft Visual Studios. But When I try to run the program by double clicking on its icon in the debug folder, the program shows that main frame and then hangs. The problem was not there before I added the code for filter wheels. I am placed all the DLL files in the debug folder. Could any body help me out. Regard...

How can I make a 3-page leaflet using Publisher 2003?
How can I make a 3-page leaflet using Publisher 2003? What size? A folded document? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Phil" <Phil@discussions.microsoft.com> wrote in message news:2D93A460-A9AE-4ED6-AB93-53A06DC85292@microsoft.com... > How can I make a 3-page leaflet using Publisher 2003? Phil When you open Publisher, do you not see a large panel on the left of the screen that has the heading 'Publications for print'?.. If you don't, click on FILE - NEW, then click on Publication to...