Return 1 of 3 different values based on multiple cells

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


-- 
Thanks - Suze
0
Utf
3/16/2010 5:14:01 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
706 Views

Similar Articles

[PageSpeed] 28

Not very clear on your desires
      If desired, send your file to my address below. I will only look if:
      1. You send a copy of this message on an inserted sheet
      2. You give me the newsgroup and the subject line
      3. You send a clear explanation of what you want
      4. You send before/after examples and expected results.


-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Susan in Sacramento" <SusaninSacramento@discussions.microsoft.com> wrote in 
message news:3AF4BC13-3E91-4FB7-8694-4F1F2D69C2C2@microsoft.com...
> 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
>
>
> -- 
> Thanks - Suze 

0
Don
3/16/2010 5:24:09 PM
Answered in the 'Worksheet Functions' forum
-- 
Gary Brown



"Susan in Sacramento" 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
> 
> 
> -- 
> Thanks - Suze
0
Utf
3/16/2010 7:34:09 PM
Reply:

Similar Artilces:

SP 1 & 2 Updates
I noticed that when I installed the SP 1 & 2 Updates, I could no longer get .zip, .doc, .htm, .exe attachments and anything like that (.jpg and .gif are no problem). Is there a way to enable attachments of other types to come through? I would like to install the Updates, but need to be able to receive attachments. Thanks for your help. ...

Zero-value Budget Column in FRx SP11
Hello all, We have just resolved an interesting problem that one of our customers was encountering after upgrading from FRx SP9 to SP11, that I'd like to share with you all, along with our resolution. Bear in mind, I am not recommending this resolution to you, so much as providing it to assist you with your own enquiries. The symptoms of the problem are as follows: A report contains a two columns, a budget column and an actuals column. Running the report on FRx SP9 produces the correct figures. Running the report on FRx SP11 produces correct actuals, but the budget figu...

Undeliverable Messages #3
Undeliverable messages always get forwarded to me (the administrator). Is there any way to not have them forwarded to me and to send a "delivery refused" message to the sender. Thanks Messages sent to invalid recpient addresses can be rejected immidiately. In this case the sender gets an NDR generated by his own server. Just enable recipient filtering (and have a look at tar pitting) from here: http://www.exchangeinbox.com/articles/020/dirharvest.htm -- Alexander Zammit WinDeveloper Software IMF Tune - Unleash the Full Intelligent Message Filter Power http://www.windeveloper.c...

CRM 1.2 & 3 on the sae server
Is it possible to install a CRM 3 on the same server (and not to upgrade) where the 1.2 CRM is already installed? I dont believe you can do that, unless you are using server virtualization. "David Massard" wrote: > Is it possible to install a CRM 3 on the same server (and not to upgrade) > where the 1.2 CRM is already installed? Both versions use the same registry hive, so they can not run on the same machine. -- Matt Parks MVP - Microsoft CRM "chad.buser@ncsi.cc" <chadbuserncsicc@discussions.microsoft.com> wrote in message news:885DC6E8-3226-4D1F-9...

Dynamic unlock a cell (under condition)
Hello guys, lets see if anyone could help me out on how to do this: I have a sheet protected with a password, the reason of this (you know) is that there are some cells that are locked (to not see their formulas or 'cause I don't want them to be changed under any circustances). This is working fine, but now I have the need of some cells (two ranges actually, these are E7:E56 & N7:N56) that they come locked by default but under certain conditions I would need them to get unlocked. Taking an E7 as example, let's say that depending on what we have in B7 this E7 will chang...

Customer Returns
I'd like to know if there someone has written a RMS report that shows the customer account number, customer's name (last, first), and other customer statistics that INCLUDE pieces purchased and pieces returned. Or, if someone with more knowledge of SQL would care to share their coding expertise to accomplish the same, it would be appreciated. ...

Remove Cell Auto-Format (General) on Paste / Replace
Good morning all, Just curious if this is able to be done. I have a series o spreadsheets, all of them do different things, but the common factor i that I paste claim numbers into them. The problem is, Excel seems t think our claim numbers are dates. (Example. 2146/04 or 04/262 (different formats for different sections). Now, I have formatted AL cells on these spreadsheets as text. (Ctrl-A, Format-Cells-Text However, when pasting, Excel overrides whatever you've got, and choose it's own way to paste data. The other problem is with Find-Replace or Find-Replace All. A lot o the time, ...

Return-Path: <> from Postmater
Hi, I got Exchange 2003 SP1 on Windows 2003 in Native Mode and when I receive bad mail the response is made from postmaster@mydomain.com but the return-path field is blank. With some anti-spam filter this Email is Junk. Return-Path: <> Anybody know how to specifeid a return-path to the NDR mail? _____________ Thank you Sebastien On Fri, 4 Nov 2005 12:48:04 -0800, "Sebas" <Sebas@discussions.microsoft.com> wrote: >Hi, > >I got Exchange 2003 SP1 on Windows 2003 in Native Mode and when I receive >bad mail the response is made from postmaster@mydomain.c...

Query regarding A,B,C char. width values returned in 16-bit and 32-bit OS
I am trying to retrieve individual character widths of a string (from a TrueType font) and I tried using GetCharWidth (which weirdly works for TrueType fonts too tho' MSDN says otherwise) and/or GetCharABCWidths to retrieve the A,B and C spacings. Please note that I have selected the TrueType font (hFont) into the Device Context (hDC). I used GetCharABCWidths. Problem is this: GetCharABCWidths returns correct A,B and C widths in Win 98 and incorrect (much lesser A,B and C widths such that the characters OVERLAP) in other 32-bit OS (NT, XP). The effect is seen across all TrueType fonts, th...

How do you sort in Excel by 1, 1a, 1b, 1c, ...2, 2a?
I want to sort a list of numbers in excel but I want 1A to be in between 1 & 2. Hos do I do that? I tried setting up a custom list, but no luck. To get the sort order you want all the items must be text. For example, select A1:A3. Use Format, Cells and select Text as the number format. Enter 1, 2, 1a in the cells and then click the Sort button on the Standard toolbar. The order should sort to 1, 1a, 2. -- Jim Rech Excel MVP "MChapAcct" <MChapAcct@discussions.microsoft.com> wrote in message news:2A7B3324-66D0-4DA0-A339-55FA29F3FD1A@microsoft.com... |I want to so...

.NET equivalent to XSLT value-of select
This seems like it should be really easy, but I cannot seem to make it work. I am trying to retrieve the text value of an element named "child2Element" from an XML file in a .NET (v 1.1) with an XPath expression. In an XSLT document I would use <xsl:value-of select="rootElement/child1Element[@childId='110']/child2Element"/> (and this does work fine in a transform). But I cannot seem to find the right method or object in .NET. There seem to be a number of classes that can use XPath, but I can't figure out which one I have to use to get just the te...

Site for SOP Returns
Is it possible to have a different default Site ID for Returns than Standard Orders? Yes. You must make sure to change the site when performing a Return Transaction. -- Richard L. Whaley Author / Consultant / MVP 2006-2008 Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our books at http://www.AccoladePublications.com "Everton Raymond" wrote: > Is it possible to have a different default Site ID for Returns than Standard > Orders? Sorry, I did not see the word "default" in your post until after I r...

Report for Returns
Anyone know a why to report on frequency of customer returns -- so we can see if any customers are making habit of this? Thanks and regards, Rich hitman, Try running a detailed sales report filtered to only negative sales displayed. Good luck, Matt "hitman" <hitman@discussions.microsoft.com> wrote in message news:0B3EE780-2655-45B4-B3C6-1F5D35FFA2A7@microsoft.com... > Anyone know a why to report on frequency of customer returns -- so we can > see > if any customers are making habit of this? > > Thanks and regards, > > Rich ...

Linking Dialog box content with cells in "regular" sheet
How to link data in Edit box (from Dialog caption) with exact cells in "regulal" Sheets? Or is there another way to link exact content from Dialog sheet to normal sheet? For example, if I have number 200 in Dialog, I need that exact number 200 on another ("regular") sheet to preform calculations with it. thnx ...

Returning the Beginning of the week
Returning the beginning of the week. e.g. if the date == 02/03/04 (Friday 2nd March 2004) the beginning week for that date == 01/03/04 (Thursday 1st March 2004) Hows does one go about achieving this? Regards -- Rizitsu ------------------------------------------------------------------------ Rizitsu's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15840 View this thread: http://www.excelforum.com/showthread.php?threadid=314968 This formula will take a date in cell A1 and give the start of the wee in B1 * assumes that monday is the start of the week, so i...

Updates after CRM 3.0 Update 2
Are there any important updates that came after CRM 3.0 Update 2? Thanks in advance, Eric Sabo Anyone ? Is there another rollup update planned for CRM 3.0? "Sabo, Eric" <sabo_e@cup.edu> wrote in message news:OkpG2HmPIHA.5980@TK2MSFTNGP04.phx.gbl... > Are there any important updates that came after CRM 3.0 Update 2? > > Thanks in advance, > Eric Sabo > see http://support.microsoft.com/kb/908951 "Sabo, Eric" <sabo_e@cup.edu> wrote in message news:eTpw0zxQIHA.1168@TK2MSFTNGP02.phx.gbl... > Anyone ? > > Is there another rollup...

printing a 50:1 scale plan
I'm trying to print a 50:1 scale plan of a large room on a sheet of A3 paper. If A3 is too small I can move up to a plotter if required. What I need to end up with is a physical printout that measures 2cm for every metre. I'm finding the number of different settings confusing - please could someone suggest the correct settings to me? ...

Trying to understand quoting with CRM 3.0
We just recently implemented CRM 3.0 into our business. Previously we had been using Quick Books Pro to send out quotes. I am having a bear of a time understanding the logic behind preparing a quote. I understand the form, but where does this form go to? That is, if I wanted to email a client a quote how would I do this? Do I have to program a quote template? Am I supposed use Excel for this? Word? Further, what is the logic behind quoting several products? I can't seem to find information anywhere that talks about this!! The new CRM book "Working with MS Dynamics CR...

return
Capability of the system to automatically change the status of the PO to change order, and add line items that have been returned. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.ms...

Reference cell values from other sheets in a function
I am trying to do something that seems like it would be quite simple, and yet I cannot get it to work. I have some data in separate work sheets (one sheet for each month), and a yearly summary sheet. I have a DAVERAGE function in each month. I am trying to take that DAVERAGE on each monthly sheet and make an AVERAGE function in the year summary page referencing each DAVERAGE cell on the monthly sheets. I enter =AVERAGE( into the cell and click over to the other sheets where I need to pull the info from. I click on the DAVERAGE cell that I need to reference in the yearly AVERAGE fu...

multiple calculations in 1 cell
In cell C 13 is the quantity of units. In cell G 13 is the total cost. In cell G 6 is the percent of overhead and in cell G 8 is the percent of profit. I want to enter a formula in cell H 13 that will give me a unit price which is (cost plus overhead) + profit. Try this: =3D(G13*(1+G6)*(1+G8))/C13 Hope this helps. Pete On Mar 5, 9:10=A0pm, Pulling Hair <PullingH...@discussions.microsoft.com> wrote: > In cell C 13 is the quantity of units. =A0In cell G 13 is the total cost.= =A0In > cell G 6 is the percent of overhead and in cell G 8 is the percent of pro...

GST Change July 1
Is it as simple as changing the value in the sales tax field to 6 from 7? What about all prior transactions or running reports, will they reflect the appropriate tax value based on the date they were entered? ...

view of cell contents changes to #### when cursor leaves cell, ev.
In Excel, view of cell contents shifts to ###### when cursor leaves cell, even when auto-fit, wrap text, shrink to fit, have been tried, and even when row and column have been enlarged to allow adequate room. I found the earlier posts (sorry not to have scanned) and the problem was not about size but about Number. When I selected "general" under "format cells", the text reappeared as entered. Thank you to earlier questors! "OTadjprof" wrote: > In Excel, view of cell contents shifts to ###### when cursor leaves cell, > even when auto-fit, wrap text...

form creating new row in table1, looking for field value in table
Please can someone help as I have been battling all week with this problem, and haven't had a response. I have a form which uses some data from table1 to create an entry in table2 (i know duplication is bad, but as this is a live database, and i am working on a new section, this makes this complicated task easier), which works fine. however, i have a third table that has the unique ID from table1 and table2 in order to show when a table1 row of data is needed in table2 via a checkbox. This third table3 is created when elsewhere. The problem lies with bringing in the unique ID value...

Protecting Cells #4
I want to protect a few cells in a worksheet - not the whole worksheet. How do I do it? Sorry, you have to protect the whole worksheet, then UNprotect all of the cells except those that you wish to secure. Alternative may be to provide data validation on the cells that you wish to protect, but this is not particularly secure (copy/paste will override, for example). For that matter, nothing is secure, I guess. "Paul" <Paul@discussions.microsoft.com> wrote in message news:23C06FEE-5808-49CF-A329-42C380B51D03@microsoft.com... > I want to protect a few cells in a worksheet ...