Re: Formula problem

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: David McRitchie (dmcritchie_at_msn.com)
Date: 05/17/04 

Date: Mon, 17 May 2004 15:06:06 -0400

Check for >=26 use 2, else failure the rest of you formula
and another close paren..

Untested but this should do it.
  {=SUM(IF(G7:G1000>=26,2,IF(G7:G1000>=13,IF(H7:H1000="JaneD",1,0))))} 

-- 
---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm
"rightdp >" <<rightdp.16ey4h@excelforum-nospam.com> wrote in message news:rightdp.16ey4h@excelforum-nospam.com...
> I work for a travel-nursing agency.  We send nurses to hospitals around
> the country on a contractual basis.  I am trying to create a
> spreadsheet that will monitor how many contracts are "closed" (all
> paperwork is completed) each month.  I am using a query to pull all of
> the information from our database.  All but one thing is working great.
> Our quota is 4-13 week contracts per month.  I am using the following
> formula to monitor total number of 13-week contracts closed by each
> Recruiter:
>
> {=SUM(IF(G7:G1000>=13,IF(H7:H1000="JaneD",1,0)))}
>
> Column G is where the number of contracted weeks is shown.  JaneD is
> the name of the Recruiter.  This formula works great because it counts
> every contract that is greater than or equal to 13 as one contract
> toward the quota.  The problem is, if the contract is 26 weeks, this
> will count as two contracts toward the quota but my formula still
> counts it one time.  I basically need to know if it is possible to
> create a formula that will count as one toward the quota for every 13
> weeks.  i.e.-  13 weeks = 1,  24 weeks = 1,  26 weeks = 2
>
> I hope this doesn’t sound ridiculously confusing.  Thanks for any
> help!
>
> -Dustin
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>

Relevant Pages

  • Formula problem
    ... the country on a contractual basis. ... spreadsheet that will monitor how many contracts are "closed" (all ... Our quota is 4-13 week contracts per month. ...
    (microsoft.public.excel.misc)
  • Re: why>?
    ... and the number of contracts many, they can be produced by automated ... fact that most courts won't accept reprints pulled from a database over ... Spreadsheets are poor platforms for writing complex ... .You actually admit when Excel has a weakness. ...
    (microsoft.public.excel)
  • Re: why>?
    ... if you've got 10,000 contracts that you create and you keep them all in ... in excel if you have to search through the contact1 and the contact2 ... in a database; it's easy to union those ranges and search through them ... dont break other reports. ...
    (microsoft.public.excel)
  • Re: Very Slow reading excel data into an array (while opened in ne
    ... memory problem. ... We use a batch pdf converter to convert the files to excel. ... of information from pdf contracts taking many hours a week. ... I just need to get the data into an array. ...
    (microsoft.public.excel.programming)
  • Re: Email generated from Excel database
    ... I have a file tracking contracts for my department. ... In column D I have the expiry date and in Column E I have the notification ... the notification period and at this point send just 1 email out to that Buyer ... - But I can imagine this is not possible with excel, so if I could just open ...
    (microsoft.public.excel.programming)