#### Help with Formula using Analysis ToolPack Function ISODD #4

```Hi Peo,

Thank you for all your time and help: the formula works perfectly.

Tin�

Peo Sjoblom Wrote:
>
> This works for me
> =SUMPRODUCT(--(MOD(INDEX(ROOMS,ROW(1:1),),2)=1))
>
> copied down, if you copy accross replace
> ROW(1:1) with COLUMN(A:A)
>
> Regards,
> Peo Sjoblom
>
> "Tin�" <Tin.1eji6m@excelforum-nospam.com> wrote in message
> news:Tin.1eji6m@excelforum-nospam.com...
> >
> > Hi Peo,
> >
> > Thank you for assistance.  The formula is fine -  but is there a wa
> I
> > can avoid manually changing the Row Index number (after ROOMS) fo
> each
> > Row so that I can just copy / fill the formula down.  I tried th
> ROW
> > function but did not get the correct answer.
> >
> > Further assistance appreciated.
> >
> > Thanks
> > Tin�
> >
> >
> > Peo Sjoblom Wrote:
> > >
> > > Sure, just use index as well, for the first row you can use
> > >
> > > =SUMPRODUCT(--(MOD(INDEX(ROOMS,1,),2)=1))
> > >
> > > just change the 1 after ROOMS to whatever row you want to count
> > >
> > > Regards,
> > > Peo Sjoblom
> > >
> > >
> > > "Tin�" <Tin.1eg77b@excelforum-nospam.com> wrote in message
> > > news:Tin.1eg77b@excelforum-nospam.com...
> > > >
> > > > Hi Peo,
> > > >
> > > > Thank you, but this workaround gives me the* total * number o
> Odd
> > > > numbers for the complete Dynamic Named Range ROOMS.
> > > >
> > > > =SUMPRODUCT(--(MOD(ROOMS,2)=1))
> > > >
> > > > Is it possible to get the *count of Odd numbers per/ for each
> > > > individual Row of 9 Columns* using the Dynamic Named Rang
> ROOMS.
> > > >
> > > > Thanks
> > > > Tin�
> > > >
> > > > Peo Sjoblom Wrote:
> > > > > One workaround
> > > > > =SUMPRODUCT(--(MOD(ROOMS,2)=1))
> > > > >
> > > > > Regards,
> > > > > Peo Sjoblom
> > > > >
> > > > > "Tin�" <Tin.1eftrz@excelforum-nospam.com> wrote in message
> > > > > news:Tin.1eftrz@excelforum-nospam.com...[color=darkred]
> > > > > >
> > > > > > Hi,
> > > > > >
> > > > > > Is this feasible using the Analysis ToolPack function ISOD
> -
> > > > > >
> > > > > > Can I avoid using individual column/cell references to coun
> the
> > > > > > instances of ODD numbers: I have a Dynamic named Rang
> called
> > > ROOMS
> > > > > > that spans 9 columns (and many rows) with an OFFSET of 1 fo
> a
> > > > > column
> > > > > > heading.
> > > > > >
> > > > > > The Define Name Refers To Box for ROOMS is:
> > > > > > =OFFSET(LOCATION!\$C\$18,1,0,COUNTA(LOCATION!\$C:\$K),9)
> > > > > >
> > > > > > Rather than using individual cell references , can I us
> this
> > > > > Dynamic
> > > > > > Named Range ROOMS to find the total count of Odd number
> within
> > > each
> > > > > > row of 9 columns - can this be done?
> > > > > >
> > > > > > Thanks
> > > > > > Tin�
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Tin�
> > > > > >
> > > > >
> >
> ------------------------------------------------------------------------
> > > > >
> > > >
> > > >
> > > > --
> > > > Tin�
> > > >
> >
> ------------------------------------------------------------------------
> > > > Tin�'s Profile:
> > > http://www.excelforum.com/member.php?action=getinfo&userid=15410
> > > > View this thread:
> > > >
> >
> >
> > --
> > Tin�
>
> ------------------------------------------------------------------------
> > Tin�'s Profile:
> http://www.excelforum.com/member.php?action=getinfo&userid=15410
>

--
Tin
-----------------------------------------------------------------------
Tin�'s Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1541

```
 0
10/22/2004 9:40:45 PM
excel.misc 78881 articles. 5 followers.

0 Replies
351 Views

Similar Articles

[PageSpeed] 27

Similar Artilces:

Help with Date/Time Function
I am working with date/time functions trying to figure out how many hours were spent doing a job. I have a start time and a finish time. But I need to subtract out any time between five pm and six am and weekends from the times. These would be times that no one was actually working on the job. For example I have 4/5/2005 18:23 4/6/2005 17:04 Could someone give me a clue as to how this might be done? Thanks, Steve Monczka smonczka@hotmail.com It's not a trivial thing, here's an example http://www.cpearson.com/excel/DateTimeWS.htm or here http://tinyurl.com/cbphn -- R...

IF function 05-21-10
I have a list of numbers in column A. Some are formated in 'red' color and the others are 'black'. How do i format column B so there is an 'X' next to the numbers that are 'red' and nothing next to numbers that are 'black.' Thank you I would take the opposite approach. I'd put an X in the adjacent column or leave it blank, then use Data|Validation to color the cell near it. gudencough wrote: > > I have a list of numbers in column A. Some are formated in 'red' color and > the others are 'black'. How do ...

"Remove access" to certain accounts in CRM 4.0
Is there a way in CRM 4.0 to remove access from specific accounts for specific users? Accounts they would otherwise have access to? I'm only talking about a very specific subset of the regular accounts in the business unit for which the person resides. Anyone have any experience with that? The simplest way is to move this accounts "VIP accounts?" into another business unit and set the access rights accordingly. Or you can use the team approach. Regards, Robert "Adam Pinilla" wrote: > Is there a way in CRM 4.0 to remove access from specific accounts for...

count if #4
=COUNTIF(B4:B17,"X") this works great with X, but I would like to include L and V in this formula. How would I do this??? Thanks You could do the brute force version: =COUNTIF(B4:B17,"X")+COUNTIF(B4:B17,"L")+COUNTIF(B4:B17,"V") or you could use something like: =SUM(COUNTIF(B4:B17,{"X","L","V"})) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Kevin wrote: > > =COUNTIF(B4:B17,"...

What is the problem with the array formula (see cells in column R, which are highlighted coloured yellow) ? {=IF(SUM(IF(ISERROR(LOOKUP(N(OFFSET(\$B2,,{0,2,4,6,8,10,12,14})),IF(\$A2="ABC",A!\$A\$2:\$G\$140,B!\$A\$2:\$G\$210))),0,IF(LOOKUP(N(OFFSET(\$B2,,{0,2,4,6,8,10,12,14})),IF(\$A2="ABC",A!\$A\$2:\$G\$140,B!\$A\$2:\$G\$210))="Y",1,0)),IF(ISERROR(LOOKUP(T(OFFSET(\$B2,,{0,2,4,6,8,10,12,14})),IF(\$A2="ABC",A!\$A\$2:\$G\$140,B!\$A\$2:\$G\$210))),0,IF(LOOKUP(T(OFFSET(\$B2,,{0,2,4,6,8,10,12,14})),IF(\$A2="ABC",A!\$A\$2:\$G\$140,B!\$A\$2:\$G\$210))="Y",1,0))) >0,"Y...

Money 2002 opens very slowly! Started few days ago!!! HELP
Hi to all, I have problem with Money2002. It is full with data from last year and until few days ago everything was ok. In average it would took about 3-5sec for MsMoney to open all the data, but since yesterday I need to wait app 10- 15sec for MsMoney to open. I tried everything but no luck. Anybody have a clue what this could be. Thanks in advance Is 15 seconds really that long to wait? What other programs and data have you installes/saved in the last year. Is your drives getting full? In microsoft.public.money, John wrote: > >I have problem with Money2002. It is full with...

Using Building Blocks, Content Controls and XMLMappings to generate multi-page template-generated sections in a Word Document
Dear Readers, I would like some of your insight and feedback regarding a document I am creating. Here is the scenario. The document is a package that describes a toy collection. There is supposed to be a section in the document which consists of a sequence of pages that repeat an identical one-page template for each item in the collection. I have an XML document (Excel Table mapped export) which describes each item in the collection, and I would like to programmatically generate these pages of the document from the content in the XML file and the presentation in the template. S...

help with range lookup and date criteria
Hello, I have a list of doctors (column A) that are each on-cal through a number of days. My argument is: if the date value of B1 an C1 is within the current date, then repeat the value of A1 -- James Spaldin ----------------------------------------------------------------------- James Spalding's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2567 View this thread: http://www.excelforum.com/showthread.php?threadid=39088 hi, James ! > ... a list of doctors (column A) that are each on-call through a number of days. > ... argument is: if the date value ...

Mail Security
I am running Exchange 5.5 on Windows advanced server2K. From my Exchange server, logged in as Administrator, you can do the following: start->settings->control panel->mail....then change the mailbox user to anyone in the company......open MS Outlook and read their email... Is there a way I can prevent this from happening (even if only for my own mailbox) Thank you! If Administrator has the role of Service Account Admin on the site object, then this can happen. You wouldn't be the first person who used this account as the service account. You could change the service ac...

Help with complex query 05-09-07
Hi all, I've got a query that I'm not sure how to develop. My tables: Quotes - QuoteNo, RaisedBy, Customer QuoteItems - RecordID, QuoteNo, PartNo, Lifecycle, Value There's a one-to-many relationship between Quotes and QuoteItems, i.e. one quote can have many items. I need to run a query to show a list of quotes with totals from the QuoteItems table i.e. QuoteNo, RaisedBy, Customer, List of PartNos, List of Lifecycles, TotalValue I haven't got a clue how to start this, I know it needs to be nested queries, but the listing of parts and lifecycles is particularly stumpin...

Search folder criteria
I am using Outlook 2007. How can I customise my search folder to filter messages that contain "KSC" in subject OR belong to "Sports" category? Is there a reason why "KSC" cannot not be assigned "Sports" catagory? Melissa wrote: > I am using Outlook 2007. > How can I customise my search folder to filter messages that contain "KSC" > in subject OR belong to "Sports" category? ...

Why does this confuse me?? -- ? on Offset function
I'm trying to understand how/why this works... =OFFSET(A12,MATCH(MAX(E12:E300),E12:E300,0)-1,) So the reference cell is a12 - got that Understand the MATCH/MAX functions. I'm confused about the -1 aspect of the OFFSET function. If I understand this it's referencing one row below the reference A12... however it's referencing the same row.... the formula works, though I'm confused as to why... MATCH returns a number from 1 to n (or, possibly an error). So, if you want to include the value of cell A12 in the calculation then you have to correct the MATCH of...

What does the \$ symbol mean in a formula?
What does the \$ symbol mean in a formula? example: =G8*(\$A\$14*2)*100 thanx The \$ is used for absolut referencing. For e.g lets take your formula =G8*(\$A\$14*2)*100 suppose you enter this formula in cell B2, now simply copy this formula one cell to the right (C2) by and one cell down (B3). C2: =H8*(\$A\$14*2)*100 B3: =G9*(\$A\$14*2)*100 So you see that The original G8 has changed, whereas \$A\$1 remains the same in both the new formulae because of the dollar signs. Mangesh "S" <S@discussions.microsoft.com> wrote in message news:D86597A4-C0A1-46AF-9DA6-FE06AB9C1FA0@mi...

Help on displaying recession periods in a financial/ stock chart
Using excel version (10.6501.6626)sp3 How do you shade the chart background to show economic recession periods in a financial/ stock chart. I've fiddled with the drawing function-rectangle box, transparency, etc- but it isn't very satisfactory since it corrupts the column/ line colors and usually washes out when printed. So that doesn't appear to be the solution. And after checking thru Microsoft help and the discussion group messages I haven't found a message addressing my problem. Since we see charts all the time with the recession periods shown I know there is a way to acco...

This problem is about recordset functionality. Actually, When using RFX_Int, value 32484 is being treated as null and can't be entered. So converting RFX_Int into RFX_Long and type casting the variable to long can solve this problem. Look at the link: http://support.microsoft.com/kb/170257/en-us But again, value 1246576928 is being treated as null in case of RFX_Long. And when tried to type cast the variable to double, it shows the fractional truncation error and numeric out of range error. So wouid anyone please let me know: 1)How to resolve the issue? 2) How value can be entered in data...

Determine if another cell is hidden in a Formula
Hi! I would like to display a special message in a cell when another cell is hidden. Is it possible? Something like (in cell A2): =If(IsHidden(A1), A1, "") I guess I could do it with a user function... but if I use that in a lot of cells (hundreds) it might significantly slow down the refresh speed... Any thoughts ? J Whales ...

Can you use the old Lotus "/ commands" in Excel 2003?
In Excel 2000 you could use a demo mode to activate the old Lotus menu commands. Can you do this in Excel 2003? Look at Tools>Option>Transition -- Kind regards, Niek Otten "DAW" <DAW@discussions.microsoft.com> wrote in message news:B5590E6E-DFE1-4A9A-8651-387B7F49D112@microsoft.com... > In Excel 2000 you could use a demo mode to activate the old Lotus menu > commands. Can you do this in Excel 2003? > It's gone from xl2003. DAW wrote: > > In Excel 2000 you could use a demo mode to activate the old Lotus menu > commands. Can you do thi...

Does anyone have a formula to calculate the car lease payments
Hi, I am just looking for a formula that will allow me to calculate the monthly repayments for a car lease. Thanks in advance The PMT function may help you out. Look at Excels help for details regarding its arguments and use. "Captain Jack" wrote: > Hi, > > I am just looking for a formula that will allow me to calculate the monthly > repayments for a car lease. Thanks in advance see if this helps http://tinyurl.com/dxnq4 -- Don Guillett SalesAid Software donaldb@281.com "Captain Jack" <Captain Jack@discussions.microsoft.com> wrote in message ...

Need help removing characters in multiple cells
I have an excel 2007 sheet that has over 300 cells in a column that have a name of a store plus a number between paranethesis. Is there a one step way I can remove the paranethesis? Here is an example: K-Mart (ABC#73846365) and I want to have just the following K-Mart Each cell has a different store name and set of numbers within the paranethesis. Thanks Select the range to fix. Edit|replace what: _(* (spacebar, open paren, asterisk) with: (leave blank) replace all It looked like you'd want to remove the space character after the t in k-mart. "<----- Mardm...

percent formula
Anyone please! How do I write a formula that gives me the total after deducting %. Example if I have \$25 and I need to deduct 14% and I need to show the total which would be 21.50 -- smile Try this: =25*(1-14%) A1 = 25 B1 = 14% =A1*(1-B1) Format as General or Number -- Biff Microsoft Excel MVP "israel" <israel@discussions.microsoft.com> wrote in message news:E5C67430-5992-46A1-B9EC-40C0DC443CDA@microsoft.com... > Anyone please! > > How do I write a formula that gives me the total after deducting %. > Example if I have \$25 and I need to deduct 14% and I...

Help Understanding Microsoft Server Products
Hi all We are looking to upgrade servers (currently running WinServer 2003) - new hardware and software. The Essential Business Server Premium pack appears to be ideal in terms of the software and package offered. BUT it appears that there will not be a version of this supporting Exchange 2010 for some months. This means we put a significant investment into 3 year old software, rather than catching the up-to-date flavour. So what's the difference between buying the EBS package and buying individual software applications separately? Also, is there additional software i...

excel formula problem #3
i want to set up a system that counts a series of dates but only say how many cells are filled, i can get this to work generally but section needs to count 3 seperate dates but only add on any one of the (eg) cell A counts the number of dates in cells B-Z but with cells D,E, i dont want all to count, only one of the three even if all are filled but it must count if any of the three are filled hope this makes sens -- Message posted from http://www.ExcelForum.com Hi try: =COUNT(B1:C1,G1:Z1)+(COUNT(D1:F1)>0) -- Regards Frank Kabel Frankfurt, Germany > i want to set up a system that c...

Sum formula is not adding up properly
I am summing up hours in Excel and the sum formula is not working Properly. For 2 of my 5 cells are adding correctly, but the other 2 when added to the formula throw the entire thing off. They are all formatted the same in 13:33 format to measure the # of hours spent on an activity. What would the reason be that two of them are not working? (it is almost like exel is substracting hours when these 2 cells are added) No real problem. Your math is probably fine; formatting needs fixing. Select the cell with the sum in it. Format this cell as Time 37:30:55 -- Gary's Student &quo...

timeclock report help
We use the timeclock report to print out total hours worked for any given two-week pay period. I am trying to modify the timeclock report by adding an additional column that will tell me whether the date on the report was in week 1 of the pay period, or week 2. I would like to return the string 'Week1' if the TimeIn value for that row is less than or equal to the FilterLoLim value + 7, but this does not work, i can't refer to this in my SQL formula. Any ideas?? Thank you, Kevin here is a simple select statement to demonstrate what i'm trying to accomplish: select cas...

SMTP Authentication #4
This is regarding an Exchange 2003 SP2 server on Windows 2003 SP1. The issue I am having is not being able to relay through the SMTP Virtual Server from a device that is not a Domain member. The SMTP VS has the default properties, where Relay is restricted, but all authenticated users should be able to relay. From inside the domain, I am able to relay and am able to telnet to port 25 of the Exchange server. From outside the domain (but within the same LAN subnet), I am unable to do either. A telnet attempt will fail (although I can telnet to port 110), and a POP3 accout using Outlook ...