return different values based on 4 criteria?

Need Formula or macro to allocate the "Scheduled hours" based on Group Counts:

If Group "A" and "C" are both greater than 1, then Scheduled "A" and "C" 
should both be 6 hours			
If Group "A" and "C" are equal to or less than 0, then Scheduled "A" and "C" 
should both be 0 hours			
If Group "A" greater than 1, and Group "C" is equal to 0, then Scheduled "A" 
should 12 hours			
If Group "C" greater than 1, and Group "A" is equal to 0, then Scheduled "C" 
should 12 hours			

		What should be returned for Scheduled Hours	
Group Count 	Scheduled Hours	
A	B	A	B
1047	1192	6	6
0	995	0	12
1752	0	12	0
0	0	0	0
0	0	0	0

0
Utf
3/16/2010 4:53:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

4 Replies
854 Views

Similar Articles

[PageSpeed] 46

- Assume Group A is in column A
- Assume Group C is in column B
- Assume data begins in row 2

- Scheduled Hours for Group A would be...
=IF(AND(A2>0,B2>0),6,IF(AND(A2<=0,B2<=0),0,IF(AND(A2>0,B2=0),12,0)))

-Scheduled Hours for Group C would be...
=IF(AND(A2>0,B2>0),6,IF(AND(A2<=0,B2<=0),0,IF(AND(A2=0,B2>0),12,0)))


-- 
Hope this helps.  
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Susie (SFAngelgirl)" wrote:

> Need Formula or macro to allocate the "Scheduled hours" based on Group Counts:
> 
> If Group "A" and "C" are both greater than 1, then Scheduled "A" and "C" 
> should both be 6 hours			
> If Group "A" and "C" are equal to or less than 0, then Scheduled "A" and "C" 
> should both be 0 hours			
> If Group "A" greater than 1, and Group "C" is equal to 0, then Scheduled "A" 
> should 12 hours			
> If Group "C" greater than 1, and Group "A" is equal to 0, then Scheduled "C" 
> should 12 hours			
> 
> 		What should be returned for Scheduled Hours	
> Group Count 	Scheduled Hours	
> A	B	A	B
> 1047	1192	6	6
> 0	995	0	12
> 1752	0	12	0
> 0	0	0	0
> 0	0	0	0
> 
0
Utf
3/16/2010 6:09:01 PM
Scheduled Hours for Group A
=MAX((A2>1)*12-(B2>1)*6,0)


Scheduled Hours for Group C
=MAX((B2>1)*12-(A2>1)*6,0)



"Susie (SFAngelgirl)" wrote:

> Need Formula or macro to allocate the "Scheduled hours" based on Group Counts:
> 
> If Group "A" and "C" are both greater than 1, then Scheduled "A" and "C" 
> should both be 6 hours			
> If Group "A" and "C" are equal to or less than 0, then Scheduled "A" and "C" 
> should both be 0 hours			
> If Group "A" greater than 1, and Group "C" is equal to 0, then Scheduled "A" 
> should 12 hours			
> If Group "C" greater than 1, and Group "A" is equal to 0, then Scheduled "C" 
> should 12 hours			
> 
> 		What should be returned for Scheduled Hours	
> Group Count 	Scheduled Hours	
> A	B	A	B
> 1047	1192	6	6
> 0	995	0	12
> 1752	0	12	0
> 0	0	0	0
> 0	0	0	0
> 
0
Utf
3/16/2010 7:45:01 PM
Teethless Mama provided a MAX function that worked great!  I couldn't get 
your answer to work just right - but it helped me with anohter nested IF AND 
issue.  THank you Gary
-- 
Thanks - Suze


"Gary Brown" wrote:

> - Assume Group A is in column A
> - Assume Group C is in column B
> - Assume data begins in row 2
> 
> - Scheduled Hours for Group A would be...
> =IF(AND(A2>0,B2>0),6,IF(AND(A2<=0,B2<=0),0,IF(AND(A2>0,B2=0),12,0)))
> 
> -Scheduled Hours for Group C would be...
> =IF(AND(A2>0,B2>0),6,IF(AND(A2<=0,B2<=0),0,IF(AND(A2=0,B2>0),12,0)))
> 
> 
> -- 
> Hope this helps.  
> If it does, please click the Yes button.
> Thanks in advance for your feedback.
> Gary Brown
> 
> 
> 
> "Susie (SFAngelgirl)" wrote:
> 
> > Need Formula or macro to allocate the "Scheduled hours" based on Group Counts:
> > 
> > If Group "A" and "C" are both greater than 1, then Scheduled "A" and "C" 
> > should both be 6 hours			
> > If Group "A" and "C" are equal to or less than 0, then Scheduled "A" and "C" 
> > should both be 0 hours			
> > If Group "A" greater than 1, and Group "C" is equal to 0, then Scheduled "A" 
> > should 12 hours			
> > If Group "C" greater than 1, and Group "A" is equal to 0, then Scheduled "C" 
> > should 12 hours			
> > 
> > 		What should be returned for Scheduled Hours	
> > Group Count 	Scheduled Hours	
> > A	B	A	B
> > 1047	1192	6	6
> > 0	995	0	12
> > 1752	0	12	0
> > 0	0	0	0
> > 0	0	0	0
> > 
0
Utf
3/16/2010 9:31:01 PM
Worked on the first try - thank you - It saved me hours of work 
-- 
Thanks - Suze


"Teethless mama" wrote:

> Scheduled Hours for Group A
> =MAX((A2>1)*12-(B2>1)*6,0)
> 
> 
> Scheduled Hours for Group C
> =MAX((B2>1)*12-(A2>1)*6,0)
> 
> 
> 
> "Susie (SFAngelgirl)" wrote:
> 
> > Need Formula or macro to allocate the "Scheduled hours" based on Group Counts:
> > 
> > If Group "A" and "C" are both greater than 1, then Scheduled "A" and "C" 
> > should both be 6 hours			
> > If Group "A" and "C" are equal to or less than 0, then Scheduled "A" and "C" 
> > should both be 0 hours			
> > If Group "A" greater than 1, and Group "C" is equal to 0, then Scheduled "A" 
> > should 12 hours			
> > If Group "C" greater than 1, and Group "A" is equal to 0, then Scheduled "C" 
> > should 12 hours			
> > 
> > 		What should be returned for Scheduled Hours	
> > Group Count 	Scheduled Hours	
> > A	B	A	B
> > 1047	1192	6	6
> > 0	995	0	12
> > 1752	0	12	0
> > 0	0	0	0
> > 0	0	0	0
> > 
0
Utf
3/16/2010 9:31:01 PM
Reply:

Similar Artilces:

Can a CRM 3.0 Outlook Client connect to a CRM 4.0 server?
As I am planning to upgrade our CRM 3.0 server to CRM 4.0 I am curious if my current CRM 3.0 Outlook Clinet installs will be able to connect to the CRM 4.0 server after I upgrade it. Good question - I have the same problem. I'm just posting to get the notification ;-) But I fear that it is not possible - in my corp-deployment we had several 3.0 clients and if it works it's not a out of the box solution. "Chris" wrote: > As I am planning to upgrade our CRM 3.0 server to CRM 4.0 I am curious if my > current CRM 3.0 Outlook Clinet installs will be able to connec...

Stacked chart #4
Hi. I'm hoping this can be done in Excel and that the fabulous discussion group can show me how... I need a "thermometer" type chart and think a Stacked chart might work. Basically, I need to set a goal and show how I'm meeting it. I'm trying to meet an ultimate goal of 57, so 60 could be the max. I've already completed 28. Each week I set a weekly goal, say for this week, a goal of 3 but only complete 2. I'd like the stacked chart to show 28 in one color, a line 3 above that and have only 2 of the 3 colored in. Next week, the goal may be 6 with 7 complet...

CFileDialog::DoModal never return
I have a CDialog based application (App1) that run on 2000. When a user selects "Browse", the application will launch a CFileDialog. When I call CFileDialog::DoModal, the method is never return. I tried to step into the the DoModal method, I found out the pointer never return from nResult = ::GetOpenFileName(&m_ofn); .. And, I am sure that I setup the CFileDialog correctly. Because I wrote another test application (App2) using the same code, the CFileDialog pop up fine. So, this makes me think the CFileDialog didn't pop up on App1 because of my application sett...

Text Values
Hello, Can anybody help, I'm after making a spreadsheet in Excel to record times for individuals, for example if I typed in 'early shift' with the value of 10 hours, after 'noon shift' 8 hours as well as 'late' shift at 12 hours...etc, the total values would all show in a totals cell for that person. I would appreciate any help with the above. Love, Susan Hi Susan see your response in public.Excel -- Regards Frank Kabel Frankfurt, Germany Susan wrote: > Hello, > > Can anybody help, I'm after making a spreadsheet in Excel to record > times ...

Open form with 2 criteria
I need to open a form which need to satisfy 2 conditions. I know how to specify 1 condition but no idea how to specify 2. You can combine multiple conditions in the WhereCondition of OpenForm. Essentially the WhereCondition has to end up like the WHERE clause of a query, including the correct delimiters. This example shows how to use criteria on a numeric field, a Text field, and a Date field: Dim strWhere As String strWhere = "(MyNumber = 1) AND (MyText = ""dog"") AND (MyDate = #1/1/2007#") DoCmd.OpenForm "Form1", WhereCondition:=str...

subtract amounts from different columns?
I have 3 columns with, income, expenditure & balance. How can I get the balance to auto fill using the other two columns? Also want the total at the bottom of the sheet for each column, after deductions have been made. Take a look at one of Microsoft's hundereds of templates offered on-line (freely downloadable): http://office.microsoft.com/en- us/templates/CT010317261033.aspx Look for "checkbook register". HTH Jason Atlanta, GA >-----Original Message----- >I have 3 columns with, income, expenditure & balance. How can I get the >balance to auto fill us...

value types and automaticaly initialized
Hi! The variable myInstanceInt in class Foo below is automaticaly initialized to 0 because the implicit constructor that is called. class Foo { int myInstanceInt; public void MyMethod() { int myLocalInt; Console.WriteLine(myLocalInt); } } The local variable myLocalInt in method MyMethod is not automaticaly initialized becuase if this valiable is not exlicit initialized I get compile error when I try to write it's value as in this statement Console.WriteLine(myLocalInt); I mean when instance value type like myInstanceInt above is auto...

Cannot add users after Rollup 1 install (Dynamics CRM 4.0 Pro)
Hi, Rollup 1 seems to have caused my install to stop adding new users to the system - the error I get is the generic 'An error has occured' message, but the relevant trace log is below (I have removed the url and business name only): >Crm Exception: Message: , ErrorCode: -2147023570, InnerException: System.DirectoryServices.DirectoryServicesCOMException (0x8007052E): Logon failure: unknown user name or bad password. at System.DirectoryServices.DirectoryEntry.Bind(Boolean throwIfFail) at System.DirectoryServices.DirectoryEntry.Bind() at System.DirectoryServices.DirectoryE...

Adding items in a column if value in a second column is X.
Hi, This one is getting the better of me, even though I know I should be able to figure it out myself!: I have two columns of data: B and C. I have one column of ID#: A I would like to subtract C from B in all rows where ID# is X and display the sum of these values in a single cell. I would then like to do the same for all rows where ID# is Y. Please help! Many thanks for your time, Paul Try these: =3DSUMPRODUCT((A1:A100=3D"X")*(B1:B100-C1:C100)) =3DSUMPRODUCT((A1:A100=3D"Y")*(B1:B100-C1:C100)) Adjust column ranges to suit, but you can't have a complete column ...

Publisher 2000 #4
I am trying to create a brochure with a colored background. I can't get the color to print to the edge of the 8.5 x 11 page.....is there some trick I don't know or is there always a 1/2 inch margin around the background color? The printing width is a function of the printer. Besides it'll take a lot of ink to change the color of the paper. How about using colored paper? -- Don Vancouver, USA, a great city in one of the 45+ countries in America! "kim bell" <kimbell@nbnet.nb.ca> wrote in message news:032101c37961$c6746010$a301280a@phx.gbl... > I am tryin...

filter #4
I have a worksheet with 30,000 rows. When I apply freeze panes and then try to scroll down the sheet does not scroll. To do scrolling I have to undo the freeze panes command of the selection. Any idea as to why will be greatly appreciated AFD Where is the cursor when you are applying the freeze panes? -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "afdmello" <afdmello@hotmail.com> wrote in message news:utYk2mRFJHA.3996@TK2MSFTNGP03.phx.gbl... >I have a worksheet with 30,000 rows. When I apply freeze panes and then try >to scro...

Formatting #4
Can I copy and paste data in a way that the column width/row height is also copied? If so, how? Sincerely Steven Shelton "Those who hammer their guns into plows will plow for those who do not!" --Thomas Jefferson You can paste the column width, but it is a separate task. Paste Special -> Column Widths I don't know how to do it for heights. Scott Steven wrote: > Can I copy and paste data in a way that the column width/row height is also > copied? If so, how? > > Sincerely > > Steven Shelton > > "Those who hammer...

BLANK FORM BASED ON A QUERY
I Have Created a Dialog Form With a Button That Opens Another form, the Form That Gets Opened By The Dialog form is Based On a Query, and The Creteria Is Set To A value Inputed on the Dialog Form, Which Works Great When I Have Results Turned Up, If No Results The Form is Blank, Is Their A Code i can put in the CLick Event of the Dialog form that Opens the form, if there are no Results Wont Allow me to Open the form. and a Message.. Ive Tried The Following DoCmd.OpenForm "Project_Results_ByRecievedDate" If Me.RecordsetClone.RecordCount = 0 Then MsgBox "No records&qu...

Sum cells in columns based on condition
Dear all, I would like to multiply and add two columns together using an appropriate function - best if i could avoid anything too complicated like vba :-P The criteria for this is to multiply and add rows in the columns following the A's Col1 ... Col5 A 1 2000 H 2 1000 A 2 2000 So the sum should read 1x2000+2x2000 = 6000 (Col2 to Col4 also contain values as Col1.) I'm not sure how to do this... - I can't put the A's in any other place other than above the numbers, so the sum product doesn't work - I've put in a conditional format to colour the ce...

crm 4 onload event
I just upgraded a client to crm 4.0. Certain users are now getting an error when opening an Account record. The error is a permission denied error relating to the onload event. One of the users receiving this error has the system administor role. I am able to open these records on the server without any error. Any thoughts on this? Thanks, -Rick M. Hi Rick, In your onload event of your account record. Do you have any JavaScript calling the crm service? Darren Liu, Microsoft CRM MVP Crowe http://www.crowecrm.com On Apr 28, 11:19=A0am, Rick M <Ri...@discussions.microsoft.com>...

Calculate difference in time spanning a day, during office hours o
i'm trying to get the difference in times spanning a day during office hours ie.from 6/9/2007 10:35am till 7/9/2007 9:45am, excluding time between 6/9/2007 5:00pm and 7/9/2007 7:30 am.Here is where i'm stuck... if the start time is after 5:00pm 6/9/2007 only calculate from 7/9/2007 7:30am till 9:45am this is what i have so far, replace "date/time" with cell number "Logical if" if ("date out"-"date in")>=1, "value if true" (time(17,0,0)-"time in")+("time out"-time(7,30,0), "value if false&qu...

hard return
Hi, New to using Excel version 2000, have setup a form and after entering data in the frist row I want to do a hard carriage return in the last data column to go down to the next row and to the left. I hope I made that clear. falcon If you select your range first (say A2:E99) and have Tools|Options|Edit|Move Selection after Enter set to go to the right it may work nicely for you. (I like to use the tab key and I can keep my MSAE the way I normally like--off.) alphadog47 wrote: > > Hi, > New to using Excel version 2000, have setup a form and after entering data > in the fr...

Access CRM 4.0 on Windows 2008
Hello, We seem to having issues when connecting to CRM when using the IP address versus the server name. Here's what's going on: We have CRM 4.0 installed on Windows 2008. All users in the office have no issues when access CRM using Outlook or Web when they are connected via the LAN. The CRM web address is http://crmserver:5555. This is what is configured within the Outlook connection. When users are from home or outside the office, they use VPN to connect to the office. They cannot connect to CRM using Outlook or Web. However, if they typed the IP address http://192.168.16....

sumif two criteria
How can I combine the following two SUMIF formulas into one? =SUMIF(C$5:C$46,C62,F$5:F$46) =SUMIF(F$5:F$46,">1",F$5:F$46) Regards, Gary try =sumproduct((C$5:C$46=c62)*(F$5:F$46>1)*F$5:F$46) -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Gary" <allge@cox.net> wrote in message news:uPdFhfnlHHA.1340@TK2MSFTNGP04.phx.gbl... > How can I combine the following two SUMIF formulas into one? > > > > =SUMIF(C$5:C$46,C62,F$5:F$46) > > > > =SUMIF(F$5:F$46,">1",F$5:F$46) > > > > Regards, > >...

sum negative and positive values separately #2
Hi, I have a time series like this: 1995, -.05 1996, -2.1 1997, 3.6 1998, 4.3 1999, -0.7 2000, -0.08 and I would like to sum just the positive and negative value separately in this way 1995, -2.15 1997, 7.9 1999, -0.78 And I don't know if it is possible, thanks in advance. ...

To set the whole row in a particular colo. (based on some conditio
Hi, I was to set a particular color to a row based on some condition. ForEx: if the value of a cell E5 is 'closed', then set that entire row to 'grey' color. How do I do that? Use conditional formatting. Select all the rows (let's assume it is E-M in this case). Go to CF. Change Condition 1 to Formula Is Add a formula of =$E5="closed" Click Format button and format as desired OK out -- HTH RP (remove nothere from the email address if mailing direct) "Venkatesh V" <VenkateshV@discussions.microsoft.com> wrote in messag...

compute one of two missing value?
I want to create a table like this: column A is the name of the person column B is height in feet column C is height in meters How do I set it up so that if I enter a number in the feet column, excel would calculate and fill in the meter column, and if I fill in the meter column, excel would calculate and fill in the feet column? use a worksheet_change event if target.column=2 then target.offset(,1)=target*_____ if target.column=3 then target.offset(,-1)=target/_____ -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "james" <nospa...

count how many different numbers in the column and return to the n
I have a sheet Amount Item $12 1 $2 1 $8 3 $98 2 $23 2 the thing I would like to do is item amount 1 total(amount part is easy, I can use sumif()) 2 *** 3 *** the item part is hard, I tried if(countif()>0,item #), but I have 7 items in total, if I have only 3 items in the colume, I will have 4 empty rows. A PivotTable would work very nicely here because it would automatically condense/expand to include the number of different items you need, as well as summ...

Listbox Value
I have a main form frmPolicyEntry where new records are added using a combobox with TaxID. My problem is not all of our paperwork will have SSN or EIN so the user would need to take time to find it. I currently have a search form where the user can search for existing records, and was wanting to use a copy of the search form to find the name, click the record in the listbox and carry the TaxID value over thereby creaeting a new record. This sounds awful easy, but I seem to be (well I am no doubt) screwing things up. My code on the afterupdate() of search form: DoCmd.OpenForm "fr...

Page Break if next value of field in previous record is not consec
I have a report used to display box numbers by category (record series). I have the report grouped by RSNo, then sorted by BoxNo with a page break forced when a new RSNo occurs. Additionally, however, there should be a new page if the box numbers within the RS are not consecutive. For example, if the box number in RS1 are 1235, 1236, 1237, 1240, 1241, and 1242 I should end up with two pages. The first page should have 1235, 1236 and 1237 and the second page should have 1240, 1241, and 1242. I thought I could do this using VB and an if...then...else statement such that If BoxNo.Curr...