SUMPRODUCT #15

Can anyone tell me why this formula is giving me a fraction for the
result:

=SUMPRODUCT(--(J5:J23>=0.95),--(J5:J23<1.05),--(J5:J23))*100/
COUNTA(J5:J23)

J5:J23 are all percentages. J5 value is 96.67% the rest are all 100%
so I was expecting a result from the formula above of 100% instead I
get 99.82%.

When I check the formula out I see the SUMPRODUCT returns 18.966666667
rather than 19 that I would have expected.

Regards

Peter
0
6/11/2011 2:09:38 PM
excel 39879 articles. 2 followers. Follow

1 Replies
487 Views

Similar Articles

[PageSpeed] 7

On Jun 11, 3:09=A0pm, Pete <fell-wal...@hotmail.co.uk> wrote:
> Can anyone tell me why this formula is giving me a fraction for the
> result:
>
> =3DSUMPRODUCT(--(J5:J23>=3D0.95),--(J5:J23<1.05),--(J5:J23))*100/
> COUNTA(J5:J23)
>
> J5:J23 are all percentages. J5 value is 96.67% the rest are all 100%
> so I was expecting a result from the formula above of 100% instead I
> get 99.82%.
>
> When I check the formula out I see the SUMPRODUCT returns 18.966666667
> rather than 19 that I would have expected.
>
> Regards
>
> Peter

Got it sorted now, I was summing the values in J5:J23 silly me.
0
6/11/2011 2:29:07 PM
Reply:

Similar Artilces:

Macros #15
Hi - New to macros so forgive what is probably a silly question. I am experimenting with developing a macro and have used one as a template which places data in A3:F3. What I am trying to do is to click in the first coilumn of any row,eg A24, run the macro and have it place data in that row and I cant work out how to do that. Any help much apprecaited Cheers John An example 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the...

debug smartdeviceproject 01-15-10
here are the steps to view my problem:(using VS2008) 1) create vbasic(not c#) smartdevice project 2choose target platform pocket pc 2003 3)choose device application 4)without writeing any code ,hit F11(step into),pointer locates itself on statement which begins with declaration of form class then one more hit F11 again when you complete these steps, you will see the find source dialog box which seeking for 17d14f5c-a337-4978-8281-53493378c1071.vb file ,,i dont know what it is,i just want to start debugging process form the very much beginning,so i hit the F11. thanks fo...

Exchange migration #15
Hello all I recently did an Exchange migration from 2000 to 2003, and i removed the Exchange 2000 server. The issue i am having now is user no longer have access to other users calendars. D I have to readd the delegates manually , or is there a way to scrtip this? also is this normal behavior when you do a migration? thanks just one user? or all delegates? -- Susan Conkey [MVP] "skip" <skip@discussions.microsoft.com> wrote in message news:B88FC751-39FC-4A48-B0F1-EDAD336E01E4@microsoft.com... > Hello all > > I recently did an Exchange migration from 2000 ...

SUMPRODUCT with conditional OR
Hi chaps, Am using the following formula to check two columns of numbers (G3-G1002 & H3-H1002). If the value in either column is 4 or greater, I'm summing a corresponding value in column AK (AK3-AK1002): =IF(AK1014="","",SUMPRODUCT(--(($G$3:$G$1002>=4)+($H$3:$H$1002>=4)),--($G$3:$G$1002<>""),--($H$3:$H$1002<>""),AK3:AK1002)) This works fine except when both values are 4 or greater, as it then sums the value in column AK twice. Would appreciate any help to tweak the formula into submission. Thanks for looking. ...

Help with SUMPRODUCT logic
Hi, I'm officially stumped regarding this formula. The formula works correctly for month 2-12, but for month 1 the formula also returns results for cells in the specified D range that are blank. =SUMPRODUCT(--(MONTH(Reinstatements!$D$5:$D$77)=1),Reinstatements!$M$5:$M$77) Logic: Sum the cells in the M range where the date in the D range is from a specific month. In my data, there may be values in the M range without a value (a blank value) in the D range. The formula above sums the M range data when the D range is a blank. I do not want blank cells in the D range evaluated. What ...

Sumproduct if
I want a worksheet to perform a SUMPRODUCT but only if the rows fall between certain dates. please help. please help! thank you =SUMPRODUCT((B1:B10>=DATE(2003,1,12))*((B1:B10<=DATE(2003,7,18)))*C1:C10) This will sum the cells in C if the date is between the two dates in the formula -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "frank" <mrfrank73@aol.com> wrote in message news:00c101c35dca$2a5c60e0$a301280a@phx.gbl... > I want a worksheet to perform a SUMPRODUCT but only if the > rows fall between certain dates. please help. p...

$15,000 a month in 15 days #3
See how I made 32,000 in 12 days. Not hype or bs. This opportunity is smoking hot and people who have never made a dime are earning thousands per week. http://www.watchthemoneyonline.com ...

multiple criteria SUMPRODUCT
Hello Everyone, I am sure this has been discussed, but I can't seem to find a threa that matches my exact situation. I have one sheet called "Actuals" and one called "JuneActuals". Th "Actuals" sheet contains Product and Months where the total units sol for the month would be listed by product: A B(Jan04) C(Feb04) etc. ProductA ProductB The second sheet "JuneActuals" contains the raw product shipment data. In column A the product is listed each time the product ships, column has the date it shipped, and column C has the q...

Date Format and Sumproduct
I have a column A for the date MM/DD/YYYY format. I need to have a sumproduct formula look thru the database and return sales totals for salesperson by month, day and year separately. For example: Column A is date B is salesperson C is sales On a separate page in the workbook, I have a report with these selection criteria... A1 is salesperson A2 is month A3 is year I need a formula to return sales for this salesperson for the month and for the year. Looking ahead, the report/database will span several years. Also, is there a way to sumproduct sales by day of the week...

Sumproduct Question
Can you use sumproduct to return an answer based on what is typed in a cell? Here is what my spreadsheet looks like. A B C D E Load# Dest. Acc. Charge Charge Total Charge 58100 Tampa Stop $50 $1000 58100 Tampa Fuel $100 $1000 58100 Tampa Tarp $50 $1000 I'm trying to pull the individual Acc.Charges based on the Load # I put in type in a cell on a different sheet. For instance if I want to know the fuel charge for...

Using If with SumProduct help please!
I have a formula that gives me the average sales price for properties that fall within a certain value. I would like to break it down further by designating these same averages by state rather than just overall. Here is my existing formula: =(SUMPRODUCT(--($E$2:$E$495>50000),--($E$2:$E$495<75001),$D$2:$D$495)/SUMPRODUCT(--($E$2:$E$495>50000),--($E$2:$E$495<75001))) I would like to add state criteria to this, such as all properties in CA. How can I accomplish this? I'd greatly appreciate any suggestions!!! Thanks Suzanne Hi I assume that you have state...

Windows Live Mail Version 2011 (Build 15.4.3002.0810) 08-23-10
I installed this vers. Yesterday and it upgraded fine. The problem is that when I get new mail it does not download the message body. I get WINDOWS LIVE MAIL HAS NOT YET DOWNLOADED THIS MESSAGE; CLICK HERE OR HIT SPACE. An email came in yesterday and I checked it this morning and it still said the same thing. It does not indicate I have new mail; the icon does not change and no sound. I have it set to notify me when there is new mal and it does not. It may just be my IMAP server AOL because I get a lot of timeout messages. If anyone has any ideas that would be great. Does Micro...

Run code then repeat every 15 minutes
Hello I am sorry to re-post this question but I can't get it to work after many hours of trying and searching various websites for assistance. I am trying to look to see if a text box on my form contains the string "Found". If it does it will send an e mail (this section works fine). If it does not then wait for 15 minutes and repeat the check to look for the string. I can not get the code to wait and repeat. I tried John Vinson's code Me.Timer = 1000*60*15 Call Timer ' to have it "do something" the first time but in this case I get the...

SUMPRODUCT Returning 0 or wrong totals
I have been trying to figure this out for 3 days. I have 2 criterias that need to be met and then I want the total of a column. I am trying to get a total of each region with notes and without notes. Col A is region Col M are notes on accts Col L are dollars A M L 1. C Notes in field 3.15 2. W Blank 100.50 3. NE Notes in field 600.00 Here is my formula: =SUMPRODUCT((Detail!$A2:$A12440 ="^C"),(Detail!$M2:$M12440 =""),(Detail!L2:L12440)) It is returning 0. If I play with...

OWA #15
How do I sync my OWA so I can use it offline, access my Inbox, folders and other e-mail groups? I know how you do it in Outlook but I can't figure out how in OWA. I am sure there is a way to access e-mail after being on line with OWA. I would appreciate anyone's help. I have a boss who is anxious to travel and use the OWA features but also wants the offline capabilities because he will be using dial-up. Thanks for your help. marcus owa does not offer offline sync. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 fo...

How to install a "Communications cable between two computers" 07-15-10
I need a PPP connection thru a COM port. Going thru the New Connection Wizard XP installs a new modem "Communications cable between two computers" which is then used by a new "Direct" RAS connection. Setting up the RAS connection was easy by means of RasSetEntryProperties. But how to install the "Communications cable between two computers" modem without user interaction? Uwe Uwe Sieber wrote: > > I need a PPP connection thru a COM port. > Going thru the New Connection Wizard XP installs > a new modem "Communications ca...

how to calculate 15 working days of a certaing record
VBA Master, i just want to ask on how to calculate only the 15 working days of a certaing record (dont mind the holidays only the saturdars and sundays will be disregard on count). hope you can help me. many thanks. pnexs ignus -- just need help One approach is outlined in http://www.mvps.org/access/datetime/date0012.htm at "The Access Web". I showed another way in my September, 2004 "Access Answers" column in Pinnacle Publication's "Smart Access". You can download the column (and sample database) for free at http://www.accessmvp.com/DJSteele/...

runtime error #15
After loading Win XP, I cannot get into outlook 2000. It gives me a runtime error msg and kicks me out when I click ok. Help! Try a new mail profile. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, anonymous@discussions.microsoft.com asked: | After loading Win XP, I cannot get into outlook 2000. It | gives me a runtime error msg and kicks me out when I | click ...

Re: New Beta Release Of Windows Live -- Version 2011 (Build 15.4.3002.0810) 08-23-10
The problem of WLM’s “stepping on itself” when compacting and freeing up empty space seems to have been fixed as well. Nancy ...

Sumproduct with condition???
Please Help: I am trying to do a sumproduct between column B and C for the rows where the values in column A are under 300 in the table below. Is there a way to add this condition in the sumproduct formula or do it in another way? A B C 100 1,400 5 200 1,800 4 400 1,800 4 150 2,500 4 200 2,500 4 300 3,000 4 Really appreciate it. Neda Have you tried this?: Using your sample data: =SUMPRODUCT(--(A2:A7<300),B2:B7,C2:C7) Does that help? *********** Regards, Ron "neda5" wrote: > Please Help: > > I am trying to do...

How do you subtract time? (ie 03:15 am minus 5 minutes)
I am setting up a spreadsheet to figure on start time from the end time to start time. I need to subtract time from the previous cell to the next cell. Hi, Use the article 214094 from support.microsoft.com Also make sure that you have the cell formatting to Time 37:30:55 Manish "Dennis" wrote: > I am setting up a spreadsheet to figure on start time from the end time to > start time. I need to subtract time from the previous cell to the next cell. One way: XL stores times as fractional days, so all you need to do is subtract, and format the resulting cell as a time: ...

Can I reference =, <, or > sign in SUMPRODUCT
Thanks to Biff for the last response. One more for the group: Can I reference the "=", "<", or ">" etc sign in a SUMPRODUCT function of the following form: "=SUMPRODUCT((Range1=Criteria1)*(Range2>Criteria2)* (Range3<Criteria3)" I can use an indirect to reference the range, and direct references to the criteria. In countif and sumif functions I can direct reference "=", "<", or ">" etc signs but can't seem to get it right for this sumproduct function. If you have a way, please check out the min,...

matching full name to 'two column' name using sumproduct
Assume your names in Sheet1 are in column A, the dates are in column D, and the values you want to add are in column F. Further assume that the target_name in this_sheet is in A2. Try this formula in a cell in this_sheet: =SUMPRODUCT(--(Sheet1!A2:A100=A2),--(MONTH(Sheet1!D2:D100)=4),Sheet1! F2:F100) This formulae works very well (thanks to Pete for his help), however I need to use the same formulae to match the name in A2 to a spreadsheet that has the name to be matched to in two columns (first name (col A), last name (Col B). I currently use the following to match names i...

global address list #15
hi guys, I have a small issue here. I migrated to new exchange 2003 from 2000 last month, the new exchange 2003 is a new domain altogether which we buit from scrach. We migrated using exmerge and every thing worked fine. But when users are trying to send email using global addresss list the email is bouncing back with 450 error recepient not found error. When they are typing in the email address manually, the mail is going through. I think its an issue with the x.400 as global address list uses x.400 for delivery. Can any one help me out on this. Regards Jim Taylor just encountered ...

Time - 0.25
Hey Is there a formula that can convert hours in decimal into real time? I need to convert say 2.25 hours into 2 Hours 15 Mins... Any ideas? -- Mark Divide by 24 and format the result as hh:mm =a1/24 (and format nicely) Mark Solesbury wrote: > > Hey > > Is there a formula that can convert hours in decimal into real time? > > I need to convert say 2.25 hours into 2 Hours 15 Mins... > > Any ideas? > > -- > Mark -- Dave Peterson Ps. If you want to update in place, you can: put 24 in an empty cell edit|copy that cell select the range to fix edi...