I connection with some (simple) interest calculation (where interest rate is fixed every 1st July and 1st January and where interest calculation is based on 360/360) I need to know how many days at which interest rate. Let's say I have an amount which was due on 1st September 2008. Let's further say that in A1:A5 I have: A1: 1-Jul-2008 A2: 1-Jan-2009 A3: 1-Jul-2009 A4: 1-Jan-2010 A5: =Today() E2: First day of the interest period I have the following formula in B1: =IF(A1>=$E$2;DAYS360($E$2;A1;1);0) (I use semicolon as separator) Copied down to B5 this gives me: B1: 0 B2: 120 B3: 300 B4: 480 B5: 520 These numbers are the accumulated days, but what I need is an array: 0, 120, 180, 180, 40. The 120 because (according to 360/360) there are 120 days from the date in E2 to the end of the first half-year, that is from 1-Sep 2008 to 1-Jan-2009, 180 because (acc. to 360/360) there are 180 days from 1-Jan-2009 to 1-Jul-2009, and so on. In other words, how can I change the formulas i B1:B5 to a single formula which returns an array identical to the one I get by the array formula =B2:B5-B1:B4 Hans Knudsen

0 |

2/11/2010 12:19:15 PM

This will create an array that you can include in another function, but array enter it (Ctrl-Shift-Enter) IF(A1:A5>=$E$2;DAYS360($E$2;A1:A5;1);0) HTH Bob "HK" <a@b.com> wrote in message news:u8EstRxqKHA.3908@TK2MSFTNGP05.phx.gbl... >I connection with some (simple) interest calculation (where interest rate >is fixed every 1st July and 1st January and where interest calculation is >based on 360/360) I need to know how many days at which interest rate. > Let's say I have an amount which was due on 1st September 2008. Let's > further say that in A1:A5 I have: > > A1: 1-Jul-2008 > A2: 1-Jan-2009 > A3: 1-Jul-2009 > A4: 1-Jan-2010 > A5: =Today() > E2: First day of the interest period > > I have the following formula in B1: =IF(A1>=$E$2;DAYS360($E$2;A1;1);0) > (I use semicolon as separator) > Copied down to B5 this gives me: > B1: 0 > B2: 120 > B3: 300 > B4: 480 > B5: 520 > > These numbers are the accumulated days, but what I need is an array: 0, > 120, 180, 180, 40. > The 120 because (according to 360/360) there are 120 days from the date in > E2 to the end of the first half-year, that is from 1-Sep 2008 to > 1-Jan-2009, 180 because (acc. to 360/360) there are 180 days from > 1-Jan-2009 to 1-Jul-2009, and so on. > > In other words, how can I change the formulas i B1:B5 to a single formula > which returns an array identical to the one I get by the array formula > =B2:B5-B1:B4 > > Hans Knudsen

0 |

2/11/2010 12:35:41 PM

To Bob Phillips English is my second language so please bear with me if I haven't expressed myself clearly. Your array formula is not exactly what I wanted, so I will try to explain again. If I have: A B 1 01-Jul-08 =IF(A1>=$E$2;DAYS360($E$2;A1;1);0) 2 01-Jan-09 =IF(A2>=$E$2;DAYS360($E$2;A2;1);0) 3 01-Jul-09 =IF(A3>=$E$2;DAYS360($E$2;A3;1);0) 4 01-Jan-10 =IF(A4>=$E$2;DAYS360($E$2;A4;1);0) 5 =TODAY() =IF(A5>=$E$2;DAYS360($E$2;A5;1);0) then B1:B5 shows: B1: 0 B2: 120 B3: 300 B4: 480 B5: 520 which is a range with exactly the same results as your array formula, {0;120;300;480;520}, that is the accumulated days. What I want is an array {0; 120;180;180;40} (days in each half-year period) I can almost (apart from the first element) get this by the array formula: = B2:B5-B1:B4. It returns the array {120;180;180;40}. Hans Knudsen "Bob Phillips" <bob.phillips@somewhere.com> skrev i meddelelsen news:#Cr18axqKHA.4636@TK2MSFTNGP06.phx.gbl... > This will create an array that you can include in another function, but > array enter it (Ctrl-Shift-Enter) > > IF(A1:A5>=$E$2;DAYS360($E$2;A1:A5;1);0) > > HTH > > Bob > > "HK" <a@b.com> wrote in message > news:u8EstRxqKHA.3908@TK2MSFTNGP05.phx.gbl... >>I connection with some (simple) interest calculation (where interest rate >>is fixed every 1st July and 1st January and where interest calculation is >>based on 360/360) I need to know how many days at which interest rate. >> Let's say I have an amount which was due on 1st September 2008. Let's >> further say that in A1:A5 I have: >> >> A1: 1-Jul-2008 >> A2: 1-Jan-2009 >> A3: 1-Jul-2009 >> A4: 1-Jan-2010 >> A5: =Today() >> E2: First day of the interest period >> >> I have the following formula in B1: =IF(A1>=$E$2;DAYS360($E$2;A1;1);0) >> (I use semicolon as separator) >> Copied down to B5 this gives me: >> B1: 0 >> B2: 120 >> B3: 300 >> B4: 480 >> B5: 520 >> >> These numbers are the accumulated days, but what I need is an array: 0, >> 120, 180, 180, 40. >> The 120 because (according to 360/360) there are 120 days from the date >> in E2 to the end of the first half-year, that is from 1-Sep 2008 to >> 1-Jan-2009, 180 because (acc. to 360/360) there are 180 days from >> 1-Jan-2009 to 1-Jul-2009, and so on. >> >> In other words, how can I change the formulas i B1:B5 to a single formula >> which returns an array identical to the one I get by the array formula >> =B2:B5-B1:B4 >> >> Hans Knudsen > >

0 |

2/11/2010 2:44:27 PM

Can I enter numbers in a cross functional flowchart? On Mon, 24 Aug 2009 12:07:02 -0700, carosaam <carosaam@discussions.microsoft.com> wrote: >Can I enter numbers in a cross functional flowchart? Yes. Do you mean for the process names or function names? Yes. -- Regards, Paul Herber, Sandrila Ltd. Electronics for Visio http://www.electronics.sandrila.co.uk/ Electrical for Visio http://www.electrical.sandrila.co.uk/ Electronics Packages for Visio http://www.electronics-packages.sandrila.co.uk/ ...

How can I change a negative number to positive number Multiply by -1 or use the ABS() function. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "fgiord" <fgiord@discussions.microsoft.com> wrote in message news:62C2A6DA-7AC2-4379-AEFC-B3D6F3698E58@microsoft.com... > How can I change a negative number to positive number =ABS(##) or mult it by -1 -- Regards Rob "fgiord" <fgiord@discussions.microsoft.com> wrote in message news:62C2A6DA-7AC2-4379-AEFC-B3D6F3698E58@microsoft.com... > How can I chan...

I am using Access 2007 and want to be able to include more than 250 characters in a "Comment" box. Hi, You could try changing your table's field type to Memo. Assuming that your are talking about a field in a table, that maybe shows on a form. If that is not the case, please describe precisely what comment box your are attempting to use. Clifford Bass Pearlene wrote: >I am using Access 2007 and want to be able to include more than 250 >characters in a "Comment" box. -- Message posted via http://www.accessmonster.com ...

Dear Excel, I have the ultimate challenge: to do job books for 8,000 employees. The form is completed, but the request is to make booklets of 52 pages for each employee. Each page of the booklet must also have each employee's number on it (0001 to 8000). Each page o fthe booklet must be numbered with the week number from 01 to 52. Therefore I need to auto generate 416,000 pages. Ridiculous I know but nobody listens to us! How can I export the above scenario to PDF from Excel? Thanks On Apr 19, 2:17=A0pm, gatecrasherg13 gatecrasherg13 <gatecrasher...@gmail.com> wr...

Hi: I need to hide the page numbers for levels 1 and 7 in my TOC. I used the \n switch to hide level 1, but cannot find a way to specify multiple ranges for this switch. Is there a way to do this? I am using Word 2003. Phil You have to use a trick. See the "Omitting page numbers for noncontiguous levels" section of http://sbarnhill.mvps.org/WordFAQs/TOCTips.htm#OmitPageNumbers -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Phillip Llacuna" <phillip.v.llacuna@lmco.com> wrote in me...

When I am entering transactions manually, I will put "Debit" or "VISA" to indicate how the withdrawl was made. When I download my account information, Money05 overwrites this with a sequential number. I have to go into the entry a second time to redo my change. Does anyone know how to turn this "feature" off? I want my values used - sequential numbering does not help me in the slightest. This is not a "feature" you can turn off. It's a reflection of the basic premise of downloaded transaction data. The presumption with downloading data...

I have WindowsXP SP3, For some reason I get only one restore point with System Restore. I have the following drive settings: C: 6% D: 4% E 4% In the past I could automatically get several day's restore points. Why do I get only one now? Please help, Frank On Apr 11, 9:32=A0pm, "Frank Martin" <f...@general.com.au> wrote: > I have WindowsXP SP3, > > For some reason I get only one restore point with System > Restore. > > I have the following drive settings: > > C: 6% > D: 4% > E =A04% > > In the p...

Excel 2003 on XP I have a collegue who's Excel setting for General number format displays as 0.1 instead of 1 in the sample box. ie if you type in "1" you get "0.1", if you type in ".1" (as in 0.1) you get "1". Where in the settings can this General format be returned to standard? Many thanks DeanH Sorry that should have been: ie if you type in "1" you get "0.1", if you type in "1." (as in 1.0) you get "1". "DeanH" wrote: > Excel 2003 on XP > I have a collegue who's Excel setting for G...

Hi, I want to add this formula in my ESS. COUNTIF(sheet1!S2:S43,"*Yes*") to find out the number of value containing "YES". My concern is I don't have fixed count from S2 to S43. It could be something else. What can I do to have this code reusable? I thought something like COUNTIF(Sheet!S2:Count($S2),"*Yes*") \But it doesnt work Thanks for your help Jack Try this if there is nothing else in that column:- =COUNTIF(sheet1!S:S,"*Yes*") -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - ...

Hi, How could I format cells in excel so that only numbers can be filled The input should be integer e.g. between 0...99 and cells shoud no accept any other marks, such as space, letters... simply nothing els but integers. Thanks -- Message posted from http://www.ExcelForum.com Hi Juha! Try: Data > Validation You'll find it pretty intuitive and very flexible as to what you can do as far a restricting input to a cell. -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available fre...

Hi, Is there a way to limit the number of characters that a user can key into a cell. I want to get a list of names and addresses imputted by user, but I don't want them to be more than 35 characters. Thanks for the help Dr. Senji Take a look at Data|Validation. You can have excel yell at the user when they hit enter after typing in a too-long string. Dr Senji wrote: > > Hi, > > Is there a way to limit the number of characters that a user can key into a > cell. > > I want to get a list of names and addresses imputted by user, but I don't > want the...

Is there any way to manage the fields that should go to PC Charge while tendering credit cards in RMS. The problem is that transaction number ( called Ticket in PC Charge) doesn't appear in PC Charge and that cost as extra money. Regards, Ewa ...

While there are already a lot of posts about numbering, I haven't been able to find the solutions for my specific quandry... [BTW, I'm posting this under "General" even though ultimately I'd like to make macros to support the answer -- since a manual solution needs to be the first step! :) ] Background: My workgroup is preparing to migrate to Word 2007 very soon. We create/maintain hundreds of large procedure manuals, each containing multiple chapters (sometimes up to 40-50 per manual). Each chapter consists of steps, using multi-level numbered lists...

We should have the ability to number the cases without having a suffix. If we do have a suffix, we should be able to define the suffix. ---------------- 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/defa...

Simple question (i hope) Is there a simple way i can round numbers to the nearest thousand by using the format cells command. For example i would like to display 1,234.56 as 12 I don't want to be diving by 1000 and rounding to zero decimal places because i still want the whole number displayed in the formula bar when selecting it. i use excel 2003 thanx Mark Format the number as #,##0,;-#,##0, or similar. Not the comma at the end - this tells Excel to display as thousands (two commas here will display as millions). Note that 1,234.56 will display as 1 (thousand) not 12. "...

A column contains both strictly numbers and also numbers that are followed by text (e.g., row 1: 1000, row 2: 1500; row 3: 1000a; row 4: 1500a) Identical numbers are related documents, with the text suffixes referring to addenda documents; thus, document 1000 has an addendum document 1000a; How can I sort the column so in the following order: row 1 (1000), row 3 (1000a), row 2 (1500), row 4 (1500a)? Thank you -- MZ =TEXT(A1,"0") will turn each into text, then sort by that helper column (and don't accept Excel's suggestion to treat text that looks like number...

I am using Blackberry's Desktop Software to syncronize with my MS Outlook Contacts list. The onboard caller-id function of the phone that is supposed to correlate the incoming phone number to a name of a contact (should the number be in my contacts list) isn't working. According to help from Blackberry the problem is the "form" of the numbers as they're being loaded on the Blackberry from MS Outlook. Specifically, apparently the fact that the numbers are stored in the form of (###) ###-#### instead of something like ###-###-#### is keeping the phone from realizi...

How do I link two speadsheets in order to update both at the same time? "Duma" wrote: > How do I link two speadsheets in order to update both at the same time? Probably something like this In Sheet2, In A1: =IF(Sheet1!A1="","",Sheet1!A1) Copy A1 across/down to cover the extent Sheet2 will then reflect entries/updates in Sheet1 for the formulated range -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- If both spreadsheets are the same, headers, data ranges, titles etc, basically same architecture on both sheets, then... While on Sheet1...

I have change my office 2003 to 2007. What I want to know how to change format number in English to arabic number. Hi Ashraf, Assuming the number in in a field, simple: add an 'Arabic picture switch to the filed containing the number. For example, with page numbering- {PAGE \* Arabic } -- Cheers macropod [Microsoft MVP - Word] "Ashraf" <Ashraf@discussions.microsoft.com> wrote in message news:3080821A-C676-4FA2-88C4-D3CDCECF0193@microsoft.com... >I have change my office 2003 to 2007. What I want to know how to change > format number in English...

Can I add a suffix to a page number such as "1a" or "1.1"? Yes, View, Master page or Background, depending on the version. The numbering will have a # symbol. Put the text either before the # or after. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Banker" <Banker@discussions.microsoft.com> wrote in message news:C0708E7D-BAAB-4BD4-A940-3E7BF2C8A938@microsoft.com... > Can I add a suffix to a page number such as "1a" or "1.1"? "Mary Sauer" wrote: > Yes,...

I have a list of numbers on a spread sheet. 1-100. I would like to randomly mix them up. Is there a function to do this? 1 2 3 4 .. .. .. 100 Like 33 91 4 16 .. .. .. 1 Thanks! Jeff Thu, 24 Jan 2008 03:34:01 -0800 from Jeff <Jeff@discussions.microsoft.com>: > I have a list of numbers on a spread sheet. 1-100. I would like to randomly > mix them up. Is there a function to do this? I don't believe there's a one-step way, but here's what I would do. I assume your numbers are in A1:A100; make appropriate adjustments for their actual location. 1. In C1, type =RAND...

I have a long list (column) of whole numbers (dollar amounts) and would like to add a decimal point on each number. example change the numbers from 2927568021 to 29275680.21. I have tried changing the format, but it keeps adding 2 zeros on the end. thanks You mean that you want to divide every number in that column by 100 (as in your example)? If so, then enter 100 into a blank cell somewhere, select that cell and click <copy>. Then select all the cells in the column of interest and Edit | Paste Special | Values (check) | Divide (check) | OK then <Esc>. Then you can delete t...

Hi, I am new to Publisher and my first project was to create a newsletter. I chose one of the existing publication designs, but now I can't modify it as I want to. Specifically, the page numbers keep re-justifying themselves. Can anyone help? It's driving me NUTS! Cheers, Jean Delete the existing Text Frames and make new Text Frames and insert Page Numbers. Remember, there is a thing called a HELP file!!! - USE IT -- "If you don't know where you are going, any road will take you there!" This is a multi-part message in MIME format. ------=_NextPart_000...

I have a column (say Column A) with 300 values (basically it is a DDE link with data I update). I also have a cell I would like to use (say B1). In cell B1 I would like to be able to enter a value (say 20) where I will be able to use this value in another cell to "observe" the top 20 of the cell from column A. So, If I put 20 in cell B1, in cell (say C1) I would like to put some formula where it can reference the "how many observation" cell B1, and say sum A1:AXX where xx is the value in cell B1. I know in this form it cannot be done, but was wondering if someone ...

> Hi. I was woundering how I can add trailing zero's to numbers. I have > a colunm with number lemgths between 1 and 7 digets and need them all > to be 10 diget's long. I have no trouble with adding the zero's as > headers but needt hem as trailers. Thanks. Thanks all for the replys. I was hoping I could do it through the - "Format Cell, number, custom" tools built into Excel but can only put leading zeros and not trailing ones. Thanks again ...