I am trying to create a simple series formula in cell B1. The series has an input from cell A1. For example, if A1 = 4 then B1 should have value = 10 (1+2+3+4). If A1 = 5 then B1 = 15 (1+2+3+4+5). Does anyone know the formula I should put in B1? Thanks Try: =SUMPRODUCT(ROW(INDIRECT("1:"&A1))) HTH Jason Atlanta, GA >-----Original Message----- > >I am trying to create a simple series formula in cell B1. >The series has an input from cell A1. For example, if A1 = >4 then B1 should have value = 10 (1+2+3+4). If A1 = 5 then >B1 = 15 (1+2+3+4+5). Doe...

Hi, now I'm meet a problem as follow on Excel 2000 and 2003 I have define one function in one Excel file named "file1.xls" in another excel file(file2.xls) I wanna use the function defined in file1.xls, so I write the formula as following, =file1.xls!function1(A1,A2) in my VBA code, I can sure file2.xls always work with file1.xls together ( that's means when file2.xls reflash, file1.xls always be opened) my problem is: when I copy the file2.xls to another folder and file1.xls still in current folder(for example: D:\test\test1\file1.xls, and file2.xls move to C:\test1\f...

Hello How can i round float to 2 digits after '.' ? Thanx Quick and Dirty: Multiply by 100, add 0.5 (to round up correctly). convert to long, then back to float, then divide by 100. This is limited to values that will fit within a long, and is not efficient. It is reliable. also: float val; val = val - fmod(val, .01); Or, the real thing: char *_fcvt( double value, int count, int *dec, int *sign ); Which is a char string, which you can then feed to atof() and get your rounded value back. Alas, beware errors in precision limits! Balboos user@domain.invalid wrote: >...

I am using the formula below which works good. =COUNTIF(H4:H26,">0.00") I now want to add in cells H31:H55 and H60:H81. When I try to do this I get too few or not enough arguments. I have tried various permutations but cant get it. (lack of knowledge) How would I construct this formula to include the additional cells. thank you "SS" <nonense50@blueyonder.co.uk> wrote in message news:yTU%n.200359$tH4.161127@hurricane... >I am using the formula below which works good. > > =COUNTIF(H4:H26,">0.00") > > I now want to add in cells H3...

I'm not sure why this isn't working ... please help. A B 0.999 1 0.799 2 0.699 3 0.599 4 0.499 5 0.399 6 0.299 7 0.199 8 0.99 9 I just need to lookup a value in ,say, C1 and compare to colA and return a value in colB. Using: =IF(M41=0,10,IF(M41>1,0,VLOOKUP(M41,$C$60:$D$68,2))) yields #N/A for any value not 0 or >1. What gives? You did not include a 4th argument in your VLOOKUP call. That means that the table has to be sorted ascending. Bit it isn't. Sort your table or use FALSE as 4th argument (which is probably what you require). Make sure the underlying ...

I need to check two conditions in a cell and then return the value if true or false. I am using the IF function, but not getting thru the second logical condition to be applied. Thanks in advance Mustafa Post your formula, pl. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Mustafa S N" <MustafaSN@discussions.microsoft.com> wrote in message news:415212E3-9E12-4990-8527-579009B7AEAB@microsoft.com... >I need to check two conditions in a cell and then return the value if true >or > false. > > I am using the IF function, but not gettin...

What is the formula to convert numbers to words? Example: Number Words 568 Five Hundred Sixty Eight There is no direct functions to convert this. For a VBA solution check out the below links http://support.microsoft.com/kb/213360 http://www.ozgrid.com/VBA/ValueToWords.htm http://www.xldynamic.com/source/xld.xlFAQ0004.html -- Jacob (MVP - Excel) "Kiley" wrote: > What is the formula to convert numbers to words? > > Example: > > Number Words > 568 Five Hundred Si...

hi, I'm having trouble finding a way to formulate a cell so when colleague enters a Letter a specifiic cell would display a number valu relating to that letter. For example is the Letter M = 5, then when a colleague enters a M i say A1, a number 5 would automatical be added to A2 I hope I'm clear enough!!! Cheer -- wildoma ----------------------------------------------------------------------- wildomac's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1681 View this thread: http://www.excelforum.com/showthread.php?threadid=32012 Hi as a starting poi...

is there a way to have a column round automatically instead of having to round onto another column? I don't want to add another column to just be able to round. -- denhar ------------------------------------------------------------------------ denhar's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24089 View this thread: http://www.excelforum.com/showthread.php?threadid=378356 If the column contains formulas, the formulas can incorporate the Round() function within themselves. For example, if the formula is: =A1*B1 it could be revised to: =ROUND(A1*...

I'm using Excel 2007 but 'save as' 97-2003 as colleagues have different versions. All number formats are set to 2 decimal places. I'm finding that percentage calculations are rounding up to 2 decimal places but when the result in currency is subtracted from another figure the sum is rounded down. This gives results such as 6 - 3 = 4. I'm sure there must be a simple answer but I can't find it. Hoping someone here knows, as checking all simple calcuations is getting ridiculous ! Carrie "Carrie" <pantscarolyn.smith2@ntlworld.com> w...

Hi, I am using the Sales Global Procedure SOP_Calculate_Trade_Discount_Split. This procedure returns the line trade discount. The line trade discount returned is rounded off. eg: if value is 6.76 then it returns 7.00. Is there some place where i can specify to turn off the rounding off? The output type is Currency. Any help is greatly appreciated. Thanks & Regards, Sup ...

I have experienced this a few times now, when I enter a formula into a cell (could be something as simple as a SUM, when I press enter or tab, the formula just sists there rather than displaying the result. I have also had this happen to a cell that is displaying the result, but when I edit the formula a;; I see is the formula. This is not something that happens all the time, but when it does it is annoying as I end up typing the formula into a cell a few columns to the right and then dragging it to the cell I need it in. I am using 2003 but have had the same thing happen in 2002 and 9...

How can I automatically paste a formula into existing rows. I need to add formulas to 20,000 rows. Help! Put it in the first cell, copy the cell, select the next 19,999 and use Edit / Paste. What is your formula, where is it, and where do you want it, and what do you want it to be when you copy it - Give us an example. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------�------------------------------�---------------- It's easier to beg forgiveness than ask permission :-) ---...

I'm trying to find the formula that will calculate the percentage gain or loss, between two numbers One way: =B1/A1 - 1 (which is a bit more efficent than (B1-A1)/A1, since A1 only has to be looked up once). Format as a percentage. In article <03bd01c378a3$e0e0b110$a401280a@phx.gbl>, "Bill" <spencemi@hotmail.com> wrote: > I'm trying to find the formula that will calculate the > percentage gain or loss, between two numbers ...

I have a spreadsheet that calculates totals and has formulas in certain columns. Is is possible to have the formulas work, but not have any numbers in the cells before I input them? For example, in column D, I have the simple formula =D9+E9, and so on and so forth down to =D25+E25. I may only put itmes in until D11. So in D12 thru D25, I have $0.00. Can I rewrite this formula, possible an IF/THEN statement to add the two columns, but not show the $0.00 in D12 thru D25, since I did not have to do any addition? My main goal is to not have this info in D12 thru D25 print to make the sheet ...

Good Morning & a Happy New Year to all subscribers in this group. I want to round up Start times and round down Finish times to the next or previous 15 minutes in my spreadsheet. I have found MROUND but this does not work for me in all cases. There does not seem to be MROUNDUP or MROUNDDOWN available. I can get the results I want using a VLOOKUP table but is there another way? Regards to all, Dave Moore =FLOOR(A1,TIME(,15,)) =CEILING(A1,TIME(,15,)) -- David Biddulph DaveMoore wrote: > Good Morning & a Happy New Year to all subscribers in this group. > >...

hi i have a worksheet with a lot of data on it column A contains the date column B contains an area column C contains the fault column D contain a quantity A B C D 1/4/04 stores damaged 10 7/4/04 bins rust 60 31/12/04 floor wet 100 what i want to do is return quantity for a date range(one week) once the week range is checked the area and fault are checked i could have 12 faults for one area there...

How can I Print formulas instead of values? hit the following keys... CTRL and the (~) sign. (CTRL+~) >-----Original Message----- >How can I Print formulas instead of values? >. > >-----Original Message----- >How can I Print formulas instead of values? >. > Hi you go in to tools, options, view and then tick on the formula box. You then repweat this process to show the vaules. Donna x ...

I have several row that are linked to other rows =A1 =A2 etc. And so they look like this Smith, Alice Jones, Fred etc. How can I sort the rows containing the formulas so that I see the names in alphabetical order? It appears to sor the formulas themselves, giving, in this case, no change. MS has a knowedgebase article that says how to do the opposite, how to sort the original data without affecting the data shown by the formulas, but that doesn't help me. jeff.seigle@NOSPAMcox.net Do you still need the formulas or can you copy them and paste special as values in place, then ...

Hi all, I would like to create a chart showing spefici numbers regarding a specific count made on a column. To illustrate my need, please see hereunder: Considering I have a table composed of 3 columns: Column A : Device names Column B : Test Scenarii numbers Column C : Test Scripts numbers Devices Test Scenarii # Test Scripts # A 1 1 A 1 2 B 1 3 A 1 4 A 2 2 B ...

I am creating a spreadsheet that has formulas I do not want people to see. This spreadsheet has outlines and groups. How can I hide the formulas without protecting the sheets and still show the formula bar. The people I am sending the file to are not Excel literate enough to reset a macro when they open the file, so it needs to open with no problems. -- Jeff Without Protecting the sheet cells - the only idea I can come up with is to use TWO Sheets. In the FIRST put your formulas and calculations and make the SECOND Sheet to be a "reflection" of the FIRST Sheet b...

Hi, Can someone help me with a simple date formula? I need to calculate the number of months an employee has been on the job. If they started on the 15th of the month or after, we don't count that month as a month worked. If they leave on the 15th or later, we DO count that as a month worked. My result needs to be a whole number and I want to enter the start date in one cell, the term date in another and have number of months worked appear in another cell. Hi try using DATEDIF. See: http://www.cpearson.com/excel/datedif.htm -- Regards Frank Kabel Frankfurt, Germany Mark wrote: ...

I am using Money 99 and, only on investments section, when I try enter a price or quantity with a decimal part, it simply round it to an integer number automatically when I exit the edit box. Do you know how can I make it work correctly. My Contorl Panel / Currency is already configured to 2 decimal digits and it works perfectly in the other fields where I have to put a value. Thanks for your help. Fabio Wasn't Money 99 around before they decimalized stocks? May not be possible to fix it... Fabio Pires wrote: > I am using Money 99 and, only on investments section, > when...

trying to construct a formula for the following for the same cell: if a2 > 6, then e2 = 0 if a2 = 6, then e2 = 1 if a2 = 5, then e2 = 2 if a2 = 4, then e2 = 3 if a2 = 3, then e2 =5 this is for a golf scoring system. a2 values are hole scores and results are "handicap" point scores. Any help greatly appreciated Try this: =3DIF(A2>6,0,IF(A2<=3D3,5,7-A2)) Hope this helps. Pete On May 12, 4:14=A0pm, desmond1412 <desmond1...@discussions.microsoft.com> wrote: > trying to construct a formula for the following for the same cell: > if a2 > ...

Hello, I'm having problems with a formula to do the following, If column M =>0 then subtract column j from column m and give me the result. Thanx!! ~Julz Hi try =IF(M1>=0,M1-J1,"") -- Regards Frank Kabel Frankfurt, Germany Julz wrote: > Hello, > > I'm having problems with a formula to do the following, > > If column M =>0 then subtract column j from column m and give me the > result. > > > Thanx!! > ~Julz Works like a champ, but it's also giving me M-J when M is blank. When M is blank then my result should be blank. ...