hi just wondering if someone can help with the following nested ISERR formula. I want to be able to show in J3 that if the value in G3 is "0" then show as "-100%" OR if the value is "NULL" then show as "0" but if the value is >0 then I3/G3*100 it is working to a degree but if value in G3 is NULL then still shows as -100% I want it to show as 0% if NULL and -100% if 0 - hope it makes sense.... currently have following in J3 =IF(ISERR(I3/G3),-100,I3/G3*100) G3 = Order I3 = Profit thanks in advance Depending on how I interpret NULL =IF(ISBLANK(G3)...

Hi all, I have a relative who send me some Excel spreadsheet (97, 2000 or XP that contains formulas and macros written in english. However, I have a french version of Excel. Because of this, I can' open these spreadsheets. My question is quite simple. Is there a tool or an easy way to convert all formulas & macros withi a spreadsheet from english to french? Any help will be appreciated. Thank' -- micto ----------------------------------------------------------------------- mictou's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1637 View this th...

I have the formula =10/counta(E6:E15 ). This gives me 10/10 if E6 through E15 are filled. How do I make this formula: =10/counta( E6:E15 but exclude the cells from this range that equal any of the numbers in B5:B8) Example: =10/counta(E6:E15) would be 10/10 if E6:E15 are filled but if E12 = B5, excel would give me 10/9 because E12 would be excluded This is an array formula, confirm it by pressing CTRL-SHIFT-ENTER: =10/COUNTIF(E6:E15, "<>" & B5:B8) Does that help? -- "Actually, I *am* a rocket scientist." -- JB (www.MadRocketScien...

Does it matter what order the conditions are in? Some of them worked some didn't -- bludovic ----------------------------------------------------------------------- bludovico's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1480 View this thread: http://www.excelforum.com/showthread.php?threadid=26434 If you have 3 conditions, the order does not matter. What matters is the content of the cell. Not case-sensitive, but do you have any extra spaces if text? If looking for numbers, are they real numbers or text that looks like numbers? You used Joe Blow and...

OK, so I have a list of orders. Some orders have just one row of data, some have two rows of data, some have three, some have four, etc. Each row represents either a delay or an activity. (So, obviously, an order can have more than one of either.) I need to aggregate into one row: Order | Type of Order | Num. of Delays | Total Length of Delays HERE'S THE MANUAL WAY I'M DOING THIS NOW: ====================================== My initial columns are: Order Number (A) | Type of Order (B) | Action Code (C) | Action Length (D) I added a "marker column" - Dela...

If I use the fill handle to copy a formula, the formulas are well copied, but the results are not. Exemple: The formula in A3 is =A1+A2. If I copy the formula to B3 it reads =B1+B2, which is correct. The result given in B3 however is not B1+B2 but the same result as in A3. I have to recalculate the formule in B3 for the correct result. In previous versions Excel did this automatically. How can I fix this problem or is it a bug? Hi It sounds as though calculation is set to Manual. Tools>Options>Calculation>and select Automatic -- Regards Roger Govier "Jante" <J...

Hi all I'll preface this by pointing out that I do NOT want to use text to columns, nor a VBA solution. I'm looking for a formula alternative - maybe an array formula. I have a single column of data extracted from a string, which represents numeric values, single space-separated. The data looks like this: 8.56 2,514.12 3.18 0.35 What I'd ideally like is a formula that sums each of the four values, which in the above example would be 2,526.21. Any suggestions appreciated. >8.56 2,514.12 3.18 0.35 Assume that string is in cell A1. Create this named...

Hello everyone! I have a question that I thought some of you could answer: I have a number in column D and based on if it is $0-$100 I want i multiplied by 0.10 and then rounded up to .95. So, if it is 20.49 + 10% = $22.54 and then rounded up to $22.95 Now this is where it gets tricky.. If it is between 100.01 to 200.00 + 7% and then rounded up to a .95. So for example 129.44 + 7% = $9.08 = $138.50 then rounded up to .95 $138.95. I need to do this for a range of numbers like 200.00 to 300.00 an 300.00 to 500.00 etc, etc.. Could someone point me in the right direction? I appreciate a...

I don't have to much experience in Excel but I need to find out how to run a formula in a specific cell, for example if I enter 100.00 in A1 then come back and want to enter 250.00 then press enter can I get it to answer 350.00? Mike To show 350 as the answer, type the following, followed by enter =100+250 If you will be doing a lot of this, you might consider entering 100 in cell A1, 250 in cell A2 and =SUM(A1:A3) in cell A4 (leaving cell A3 for an additional value). Stephen http://groups.msn.com/HighlandsVillage where Villagers help each other "Mike" wrote: > I don...

I'm calculating simple percentages. I enter 5/10*100 and 50 appears in the cell. When I go back to that cell, what shows up in the formula bar is ..5*100. This makes it impossible for me to check to make sure I entered the correct numbers to begin with. Is there a way to make it keep the original formula in the formula bar when you return to the cell? Peter, If the cell is formatted for Percentage then enter =5/10 (equals fiv divide by 10) to produce 50% in the cell and =5/10 in the formula bar. The formula you are using suggests the cell is formatted to Number, bu if you enter =5/...

I am building a workbook that is intended to capture information on new feature requests for a system. The workbook will then consolidate information from each new feature request and perform different kinds of analysis to facilitate portfolio management (ranking views of each feature to make a determination as to which features will be funded for the next release). Analysis (and input for each feature) will include things like value to the customer, value to the company, development cost, marketing risk, development risk, deployment/support risk, company strategy supported, etc. I think I ...

Hello guys, is that true that excel-pivot is only able to sum (meanvalue is no selectable) from a formula made table. usually you put by drag and dro the data field into your table. but you also can put by hand a formula which is quit useful sometimes. after lunching the data-field in your pivot you can say pleasy show me the minimum or the meanvalue. It looks like that is not possible when you create a data-field using formula. then excel is just able to sum that ****... do you have any other idea?? cheers, filipu -- filipu ------------------------------------------------------------...

I use Word 2003, and recently formulas created by my professor do not display properly on my laptop. For instance, the ampersand symbol seems to appear where some superscripting or ' characters should be. What is the problem and solution? Hello Brian brian wrote: > I use Word 2003, and recently formulas created by my professor do not display > properly on my laptop. For instance, the ampersand symbol seems to appear > where some superscripting or ' characters should be. What is the problem and > solution? what version of Word has your professor been...

I would like to use a formula if A1 (e.g.$15) is filled in yellow then A1*2%, if in green then A1*1%.....Is there any way to do that. TIA. -- littleps ------------------------------------------------------------------------ littleps's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25482 View this thread: http://www.excelforum.com/showthread.php?threadid=391785 Hi, Check out this site: 'OzGrid' (http://www.ozgrid.com/VBA/ReturnCellColor.htm) Regards, -- aristotl ----------------------------------------------------------------------- aristotle...

I have the following setup A B C 1 Job Priority Satus 2 1 1 COMPLETED 3 2 1 CANCELLED 4 3 2 CANCELLED 5 4 3 WIP 6 5 1 WIP 7 6 4 WIP 8 7 1 COMPLETED I am trying to calculate the number of priority 1 jobs that have been completed and cancelled (the answer should be 4). I have tried looking at Array formulas, but I have not been able to figure them out. I would be grateful if anyone could offer any pointers. Best regards Neil =SUMPRODUCT((B2:B100=1)*(C2:C100={"Completed","Cancelled"})) I make it 4 -- HTH RP (remove nothere from the email address if mailing direct...

Hi, I would be grateful if someone could help me with the problem have got. I have written a macro, with my user defined functions. One of my user defined functions is called GetData and it has two parameters Reference and Last_Value, eg GetData(Reference, Last_Value). I call GetData by assigning this to a cell like so (which works fine): Cell.Formula = GetData(Reference, Last_Value) Now within the GetData function I use the Reference Parameter to get a New Value, which will be returned to the cell (which works fine too). But what I wanted to know is how can I change the Last_Value ...

I want to divide a number by counta for certain cells (which would yield a number). For example, =10/counta(A1:A4) would equal 10/4 if cells A1 to A4 are filled. What I am stuck on is this: =10/counta(A1:A4 but exclude the cells from this range that equal any of the numbers in in the range B5:B8) Example: =10/counta(A1:A4) would be 10/4 but since A2 = B7, the formula gives me 10/3 because A2 is excluded from counta What should happen if all the numbers match? Try something like this: =10/SUMPRODUCT(--(ISNA(MATCH(A1:A4,B5:B8,0))),A1:A4) -- Biff Microsoft Exce...

My question; I've seen somewhere that you can convert a text string into a formula using the INDIRECT Function. What I am constructing is a maintenance Schedule where some items need to be serviced on a weekly basis, others monthly, quarterly, bi-annually, and yearly. (Those are the only five rotations I require at the moment.) Once constructed the format will take on the appearance of a calendar with conditional formatting to highlight the dates the service falls on. Each of the rotations require (I think) different formulas, so what I want to do is have a list of the f...

Repost with responses Hi AlfD Well, here's the problem, all of the cells which are being averaged contain formulas which until other cells are filled weekly, will contain zeros. So, I wrote those to ignore the zeros. I've instructed the person who will be populating the weekly, currently empty cells to use "n/a" or "---" to show that the cell has been addressed but will not contain a value, so I'm in a quandry. I think it's best to ignore the zeros, but what's making my averages come up wrong. So, I think leave that condition alone. What I need, ...

Hi all, I don't know if this is possible, but any suggestions would be much appreciated! I have a spreadsheet with a few sheets, 1 for each site and then a Summary sheet which displays totals. eg. Site 1 shows how much has been fee'd for that site in each month, and the Summary Sheet shows how much has been fee'd for all sites in that month, by using a formula which takes the value's from each sheet. Is there a way of editing the formula so it will automatically include cell H3 for any sheet, including any new sheets added, without me having to amend the formulas? T...

I have the following formula: =SUMPRODUCT((MONTH(Summary!$F$7:$F$15000)=1)*1) I want the "15000" to be replaced by the varaible value of a cell in an other worksheet ("Summary"). I tried using INDIRECT but it didn't work. Thanks in advance =SUMPRODUCT((MONTH(OFFSET(Summary!$E$7,0,0,C4,1))=1)*1) C4 in this case is the cell that holds the number of items to be used in the calculation -- If the post is helpful, please consider donating something to an animal charity on my behalf ..... and click Yes "Leon" wrote: > I have the f...

i have this formula (Thanks Biff) =IF(J13="","",IF(J13=0,"Stock not rated",IF(OR(J13={1,2}),"Sell",""))) now i want to add in further variable - if J13 has "cmt" or "divs" then the cell is blank, then if any of the above... thank you =IF(OR(J13={"","cmt","divs"}),"",IF(J13=0,"Stock not rated",IF(OR(J13={1,2}),"Sell",""))) -- Return email address is not as DEEP as it appears "Micayla Bergen" <MicaylaBergen@discussions.microsoft.com> wro...

Need to to calculate the following Cell A1 had a figure calculated .....example 174 I then need a formula below in B1 to calc multiples of 97 and retur the figure that is greater than 174 i.e 174 in A1 194 in B1 (97*2) 20 in C1 (difference -- Message posted from http://www.ExcelForum.com B1 =97*2 C1 =B1-A1 -- Regards, Peo Sjoblom "sosteffo >" <<sosteffo.16i5o0@excelforum-nospam.com> wrote in message news:sosteffo.16i5o0@excelforum-nospam.com... > Need to to calculate the following > > Cell A1 had a figure calculated .....example 174 > > I t...

Since today I am facing some peculiar change in my excel files. When I drag a formula from one cell to another cell, result of the first cell itself are reflected in the subsequent cells. Then I have to press F9 key so that all cells give proper calculation of the formulas. E.g Product Price Qty Value A 2 10 20 B 5 15 20 C 10 20 20 D 4 2 20 E 6 5 20 I did the above table in exel. Cell D2 was the result of multiplication of B2 & C2. Thats is correct. The I dragged the formula from D2 to D6. However in this res...

Hi All! I don't know how I did this, but the information in the formula bar is showing up in white font... Please help! ... I want to change it back to black... TIA! LavaDude Try this (Think it's a windows setting) Right-click on any empty space in the desktop > choose Properties Select Appearance tab > Advanced button (in Win XP) In the Advanced Appearance dialog: Click on "Item" dropdown menu > choose Message Box Under "Color" for Font (in the line *below* "Item") Choose "Black" color > OK > Apply (and don't do i...