Hi, I recently had help understanding the following formula {=IF(ISERROR(AVERAGE(IF(Score>0,Score))),"",AVERAGE(IF(Score>0,Score)))} Thanks to Luke M, Pete UK and David Biddulph who provided the explanation. I've now been asked to add to this formula so that if either Q3 or Q4 is greater than 4 then the average cannot be lower than 3. Q1:Q35 is the named range score. This is really complicated because I still need to have an average and I'm thinking that the If part is non array but the average part still needs to be an array. Can you mix and match ...

I have a cell B1 that has NOW() time and another cell B2 that has NOW() date. I am trying to have data from another cell F1 brought in to the destination cell B3, when a given date and time occur. =(IF(AND(B1="23:00:00",B2="12/03/2009"),F1," ") This formulas does not work. The Clock is continuously active / always changing. Any suggestions would be appreciated. Thanks. The NOW() function returns both a date and a time. Even if you format the cell to display only date/time, all the data is still retained. NOte that if you did want just the d...

I am looking for a function for linear interpolation Look in HELP for the LINEST() function -- Kind regards, Niek Otten Microsoft MVP - Excel "azad" <azad@discussions.microsoft.com> wrote in message news:C53A752C-DF76-4B9F-9145-6F39495088B7@microsoft.com... >I am looking for a function for linear interpolation ...

Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I am trying to put together a spreadsheet for a business plan and am having problems with the autsum and autofill functions. Firstly, even though I do a simple autosum, if I change a value in a cell, the sum does not change. Secondly, if I try to autofill across columns, the values of those columns simply mirror the value of the first. In other words it copies it. I am at a loss how to get round it as I have been working w Excel for years and have never had this problem. I have experimented with a new worksheet with ...

I have a situation where I am creating a borrowing base for a company. (I am going to shorten numbers to make it easier.) There is a $300 loan. Type A loan can borrow up to $300. Type B loan can borrow up to $150. Type C loan can borrow up to $50. I need a formula to input in excel that will give me a borrowing base for type B and C loans. For example, to borrow in type B loans, I have to check 1-what's outstanding on the entire loan. 2-what's outstanding on type B loans. 3-Take those two numbers and figure out what can be advanced under the answer to those questions. (I.E. $2...

Bit short on detail, but maybe =IF(rng>10,rng) as an array formula, committed with Ctrl-Shift-Enter or =IF(rng1>some_val,rng2) again an array formula, where rng1 and rng2 are the same size. -- HTH RP (remove nothere from the email address if mailing direct) "jimk" <jimk@discussions.microsoft.com> wrote in message news:2873BFCF-598E-4956-808C-31C1638741A9@microsoft.com... > ...

Is there a way to use a filter string with the IF function? I have a spreadsheet that looks like this: Invoice No. Invoice Date Invoice Currency Invoice Value 1 dd/mm/yyyy USD 1234.56 65 dd/mm/yyyy USD 2345.67 74 dd/mm/yyyy EUR 3456.78 88 dd/mm/yyyy USD 4567.89 when I filter on the currency: is there a function such as if(filter_string="USD";TRUE;FALSE)??? Thanks Aref Maybe this: Assuming row 1 are t...

Trying to compose a formula that will only sum 13 cells to the left (13 months of data). When columns are added, I still only want to sum thirteens months of data. Can this be done without constanty changing my formulas? Make sense TIA Neil S. Hi see my reply to your old post -- Regards Frank Kabel Frankfurt, Germany Neil S. wrote: > Trying to compose a formula that will only sum 13 cells to the left > (13 months of data). When columns are added, I still only want to > sum thirteens months of data. Can this be done without constanty > changing my formulas? Make sense? ...

Ron Rothstein suggested and I used this function in my Excel 2003 Workbook: Public Function QBRushYds(W, Q) QBRushYds = Worksheets("Week" & W).Range("C2").Offset(Q).Value / 10 End Function In Worksheet Totals Cell B56 thru B59 I have the following four calculations: =TRUNC(QBRushYds(1,1)) =TRUNC(QBRushYds(1,2)) =TRUNC(QBRushYds(1,3)) =TRUNC(QBRushYds(1,4)) In Worksheet Week1 Cells C3 thru C6 I have the following values: 725 814 283 9 The function correctly calculates the values: 72 81 28 0 If I change any of the values in C3 th...

I´m trying to get this function to work. But I´m getting an error message. =If(and(100/I6)*I7>101); I7<I6; 1; 0) What the function is supposed to do is to detect peak values in chart table that are measure errors from a measuring intrument and count every peak as 1. This is how the function is ment to operate: If the percent of the difference of the rise from the former cell to the next cell in the sequence of data is larger than 101% AND the value of this cell is smaller than the former cell then true=1 False=0 Im using a norwegian version of excel and I´m no...

Why would my RANK function be returning #DIV/0! ???? Didn't know this was possible. Anybody run into this before? Thanks!! > Didn't know this was possible It isn't - so Excel is saying that you are trying to do the impossible :) Your code will probably need to check for and exclude zero values -- Message posted from http://www.ExcelForum.com ...

I am currently creating a page that has a column of calculations in it. The calculations are amounts of Future options. at the bottom of the column I calculate an average 6 month total by simply suming 6 months into the future. The question I have is how do I automate the Total sum number at the bottom, because every time I update my worksheet I have to redo all my calculations because the previous months value is now 0 because there is no future value anymore, so I have to rename my range and do my calculation over again. I originally thought of the Hlookup function but I can't get it to ...

Would like to have the abiltiy to enter multiple meter reading per day in fields service instead of being limited to just one entry per day. ---------------- 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-u...

I have been using Money 2003 with the base currency set to Canadian with no problems. Now I can't enter Invesment Transactions in accounts with the account currency set to US. When I enter the details in a transaction form for a US dollar account, and then try to enter the transaction, the "Exchange Rate Converter" dialog box appears, showng the correct exchange rate, and the transaction amount shown in both USD and CAD. When I press "OK" to confirm the rate and enter the transaction, I get a small dialog box saying "Please enter an amount". The only opt...

This is a multi-part message in MIME format. ------=_NextPart_000_00AE_01C71C7B.8E24F3D0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I'm using Excel 2002 for the first time and trying to place a function = into a cell. I 'm using IF(N44=3DL, M44-L44, L44-M44) . Which is supposed to mean If the letter L is in cell N44, then subtract = number in L44 from number in M44. Otherwise subtract M44 from L44. If have 2 main questions...First, how to get the result of the function = to show. Right now I see the function typed out in...

I'm using Excel 2000 and have *hidden* several rows that include numeric data. I want to do a sum function on some of the columns intersecting those rows, but when I insert an AutoSum function, it includes data in the visible rows AND the hidden rows. Is there an easy way to perform a sum function that *only* includes the rows that are *-visible?-* (Which rows are hidden/visible are subject to constant change) Thanks for your help! --- Message posted from http://www.ExcelForum.com/ You would need a UDF for that or if there is a pattern (like if every other column/row is hidden) th...

I tried to post a question earlier but it never appeared on the messag list. Not sure if it went through. So here it is again. I am trying to set up a formula where when I type in a weight i another column it will automatically puts in an assigned value for tha weight. The problem is that the weights range from 110 through 260. have found that I cannot put in more than 7 IF functions in a formula. This is my first time trying to do something like this and I am havin problems. I know there has to be a way to do this. For example I tried this formula and it worked except I con only put ...

Hi all I wonder is there a way to reconfigure the Function keys in the POS screen For example instead of F1 being displaying Help screen, I want to make it to reprint last receipt or some other function. Does anyone want to show your pretty customized POS screen ?? I want to get and idea how people's POS screen looks like Thank you Regards, Joie Joie, There is not built-in way to make this happen, but we have made this work for other customers in the past. Due to the nature of the program though, this is nothing we have a commercial application for, and would have to make s...

I am entering unique serial no. in col A. Can I set up a rule which will not allow a duplicate serial no. entry into another cell? See http://cpearson.com/excel/NoDupEntry.htm In article <FFEF019B-E20C-4177-856A-8D8BD0F0698F@microsoft.com>, "pg23673" <pg23673@discussions.microsoft.com> wrote: > I am entering unique serial no. in col A. Can I set up a rule which will not > allow a duplicate serial no. entry into another cell? Check out the validation in the Data section. select the area of interest (A:A) <data><validation><Settings><C...

This is a multi-part message in MIME format. ------=_NextPart_000_000C_01C781BE.4CC3FAF0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable hello pos world, we are installing scanners at some of our pos's in the field, the = barcodes and items have been setup in SOM. now here is my problem, = after scanning an item code into RMS it does not automatically enter, = the cashier has to manually hit the enter button on the keyboard. = anyone else experiencing this or now the correct setup so that a manual = key punch is not necessary? Thank...

Hello, I have actived KITL in polling mode but I have the following trace .. ... ... OALKitlStart DeviceId................. MX3117493 pArgs->flags............. 0x19 --> OAL_KITL_FLAGS_POLL active pArgs->devLoc.IfcType.... 0 pArgs->devLoc.LogicalLoc. 0xb4020300 pArgs->devLoc.PhysicalLoc 0xb4020300 pArgs->devLoc.Pin........ 0 pArgs->ip4address........ 192.168.3.101 pDevice->Name............ CS8900A pDevice->ifcType......... 0 pDevice->id.............. 0xb4020300 pDevice->resource........ 0 pDevice->type............ 2 pDevice->pDriver..........

I have a startup function that I run from the AutoExec macro that essentially checks the connection between the front end and back end and reconnects if necessary. This has been working for a really long time. Today, when I opened the database, I got the message: The expression you entered has a function name Microsoft Access can't find. Then I get a dialogue entitled "Action Failed" with the following information Macro Name: AutoExec Action Name: RunCode Arguments: TestStuff(0) It has three buttons, Step, Halt, Contine. Halt is the ony active one. If I run the macro by ...

Ok here is what i have i have created a payroll. now i need to add this: if pay is $0 - $95 deduct $0 plus 0c per dollar over 0 if pay is $96-$345 deduct $0 plus 20c per dollar over 96 if pay is $346-$480 deduct $63 plus 25c per dollar over 346 if pay is $481-$672 deduct $96 plus 40c per dollar over 481 if pay is $673-$961 deduct $183 plus 47c per dollar over 673 if pay is $962+ deduct $308 plus 48c per dollar over 962 And make it automatically deduct from the Gross Income. I have tried this =IF(C4>300,C4<400,(C4-43)*0.36,IF(C4>401,C4<500,(C4-60)*0.45,IF(C4>501,<600,(C4...

I have an application that evaluates the results of nutrition classes given to needy people of all ages on how to prepare nutritious meals from the food received from Harvesters. There are three option groups, three text boxes and a check box for permission to use their name. In an effort to insist on accurate data, the user is supposed to click on an "Add Record" button which uses the Click event procedure and VBA to check the accuracy of entries. I have added the following code to the Form keypress event to try to prevent the enter key from being functional at all: Privat...

I am using visio 2003. I nned more space for my flow chart so want to get rid of the title bar, or reduce it in size. ...