I am trying to average a range of numbers (F35:F38,F42:F45,F48:F54) that have numeric zeros in some of the cells. However, I would like to exclude them, and the cells from the calculation. Why does this array not work? =AVERAGE(IF((F35:F38,F42:F45,F48:F54)<>0,(F35:F38,F42:F45,F48:F54))) Try... =AVERAGE(IF((1-ISNUMBER(MATCH(ROW(F35:F54),{39,40,41,46,47},0)))*(F35:F54 >0),F35:F54)) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article <121BA8FE-53BC-42CA-82D6-AAADEB917F70@microsoft.com>, "Coal Miner" <Coal Miner@discussions.mi...

Is there any danger? Specifically, does the year-end processing adversely affect the payroll transaction batch(es) scheduled for payment during the following calendar year, and, does the existence of pending batches adversely affect the year-end process? My uninformed opinion is that creating batches for next year should be just fine; otherwise recurring batches would be in big trouble!? Thanks. ...

Hi All Trying to work out the best way to get an average answer based on a set of answers The answer will be 1 - 10 across a number of questions e.g B C D E F Answer Q1 Q2 Q3 Q4 1 1 1 3 3 2 1 0 1 2 3 0 1 3 3 4 1 0 1 1 5 1 1 6 7 6 2 2 5 2 7 2 3 3 4 8 2 1 7 6 9 1 2 1 3 10 2 2 8 7 Total 13 13 38 38 Average ? ? ? ? What is the best way to get the average response to each question. i've tried =SUMPRODUCT($B$2:$B$11,C2:C11)/SUM(C2:C11) but not sure this is right....

Hello - I need to average a range of cells B2:Y31. Normally, I'd use the formula =AVERAGE(B2:Y31)... easy enough. The twist is that I need to average that range of cells, BUT any cell that has a value less than 1 or more than 900 cannot be used in the calculation. These values are considered invalid for my purposes. All cell values are referencing a cell value in another worksheet that is part of the spreadsheet; for instance =bam!B2 How can I use a formula to automatically calculate the average of the range without calculating the invalid values? TIA! --- Phil =AVERAGE(IF((...

I just need a help from you all. Can any one of you please provide me the formula for caluclating average days to pay? And is debit memo's are considered for it? Please clarify. Thanks in advance. Hi Shree, Please find below the information on how the Average days to pay is calculated. TechKnowledge Content Issue: When and how is the Average Days to Pay calculated in the Customer Credit Summary window (Cards|Sales|Summary|Credit Summary)? Resolution: The Average Days to Pay field is updated when a debit document is fully applied. The average is recalculated as the document i...

A client has found that it is possible in Payroll to misenter a check date into a historical year and leave the posting date for the run in the open year, even though the periods were all locked in the historical year. This creates payroll information in the wrong year, which affects tax reports, payroll reports and payroll summary information. Voiding the check involves removing the year-end wage file, which can't be recreated without putting the old tax table back in. Why not have the system look at the check date as well as the posting date to check for historical or other tha...

Hi, I was in the process of printing a table that I made in One note. However, now more than three quarters of my documents is shaded black and I can't remove it. I would appreciate any suggestions regarding how to get rid of this. Thank you. Melissa Submitted via EggHeadCafe - Software Developer Portal of Choice Documenting Exceptional Developers! http://www.eggheadcafe.com/tutorials/aspnet/fc9142ae-d54b-4591-9bde-48c249ae8247/documenting-exceptional-d.aspx > I was in the process of printing a table that I made in One note. > However, now more than three quart...

I am using the formulas below for a couple of ranges. Most work but one is giving me a total of all cells instead of the average. Example C36 = 6.00 C37 = 6.00 This formula gives me 12.00 instead of 6.00. =SUM(C36:C200)/MAX(1,COUNTIF(C36:C200,">0")) Other ranges work fine. B36 = 119 B37 = 119 This formula gives me 119 (correct) =SUM(B36:B200)/MAX(1,COUNTIF(B36:B200,">0")) I am trying to get the average of the range counting only the cells that have a number other than "0" in them. There may be cells in between that are blank of do not have a numbers or ...

Can some one please provide a formula on how to change months into years and months? Example, I have 15 months in a cell, but would like the next cell to be read 1.3. meaning 1 year and 3 months. Thanks Marty With that format, how are you going to differentiate between 1 year and 1 month and 1 year and 10 months? The numbers 1.1 and 1.10 are indentical to Excel, so you can't accomplish the visual difference (1.1 vs 1.10) with formatting. =DOLLARFR(A1/12,12) will give you 1.01 and 1.10 for the above examples. You need to format the cell with 2 decimal places so you see 1.10, not just ...

Is it possible to color one (diagonal) half of an Excel sheet cell instead of the whole cell? thanks, roland Maybe you could add a triangle shape over the cell (from the Drawing toolbar), then shade that the way you want???? Roland wrote: > > Is it possible to color one (diagonal) half of an Excel sheet cell instead of > the whole cell? > thanks, roland -- Dave Peterson ...

If I have a user enter a date into a date formatted cell (d/mmmm/yyyy), how do I get the year to appear as an integer in another cell? EG, if a user enters 25/11/2003 (say) in Cell B2, how would I go about getting 2003 to appear as an integer in cell C2? Non-vba solutions preferred since I am still a vbaidiot. Thanks in advance, Dave ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ =year(B2) Ken Russell "DavidObeid" <DavidObeid.xfkca@excelforum-nosp...

I am trying to create a formula to calculate the average of the last 30 days of data I have. I want the average to be based on the today and average the last 30 days. In addition, I want the formula to change to take into account what today's date is. Any help is greatly appreciated. Assuming the data is in row C, with no embedded blank cells: =AVERAGE(OFFSET(INDEX($C:$C,MATCH(TODAY(),$C:$C,1)),-29,0,30,1)) If you have fewer than 29 values prior to today, the above will give and erro and requires a more complicated formula. On 4 Nov 2003 13:12:43 -0800, chris_cutler@landsafe.com ...

I would like to change to year portion from 2009 to 2010 in multiple cells. The cells are formatted as Date with *3/14/2001 selected for k = 1 to 5 Sheets("Sheet1").Cells(k,2)=Date if Date.year = 2009 then Date.year=2010 next k Date.year is incorrect. How do I change and save the date? Sub dural() Dim d As Date, s As String For i = 1 To 30 d = Cells(i, 1).Value m = Month(d) dt = Day(d) s = m & "/" & dt & "/" & "2010" Cells(i, 1).Value = DateValue(s) Next End Sub -- Gary''s...

I have groups of data points in categories. I can easily calculate the average in each category and show these as a bar chart, but is it possible to show the individual data pioints in each category as well? Thanks, Hi, How are you plotting the data points - as columns or line? and also could you show us dummy data layed out so we can understand what you data looks like. If you plot a clustered column chart you could show the average for each cluster as a line within the cluster or as a single point. -- Thanks, Shane Devenshire "lenos" wrote: > I have groups of dat...

It seems that no closing entry has been made during Year-End Closing. Thus there are no beginning balances in any accounts for the next year. The transactions for the year are still in table GL20000. What should I do to transfer the beginning balances? Thanks ...

if you had 5/1/2006 and 2/28/2002 how would you get 4.17? If 5/1/2006 is in A1 and 2/28/2002 is in A2, then maybe: =(a1-a2)/365 or =(a1-a2)/365.25 But if you're trying to find differences between dates, you may want to take a look at =datedif(). You can find lots of info at Chip Pearson's site: http://www.cpearson.com/excel/datedif.htm (=datedif() was only documented in xl2k's help.) jhon doe wrote: > > if you had 5/1/2006 and 2/28/2002 how would you get 4.17? -- Dave Peterson ...

Hi; I am using Canadian Payroll in GP 10, one of my employee who turned to 65 years of old, subject to Canada regulation, he does not need to contribute CPP. How can I configure Canadian Payroll to calculate CPP under this employee, today; I have to remove the calculated amount manuall when I ran pay roll each time. The date of birth of the employee has been entered in system already. -- Kane I assume the Canadian Payroll is similar to that of the US in the sense that you have benefits and deductions. Canada Pension Plan (CPP) is a deduction that is (must be) associated to an empl...

---------------- 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/default.mspx?mid=3f1ded5d-c4e9-4d4b-8f20-a22fa66ebd7f&dg=microsoft.public.greatplains ...

We haven't been running the IV year end close and this accumulated the amounts in the the current year column. It didn't update the last year column as well. Is there any way to fix this so that the current year and last year columns will show the correct figures? Thanks, got this taken cared of. "Rheiner" wrote: > We haven't been running the IV year end close and this accumulated the > amounts in the the current year column. It didn't update the last year column > as well. > > Is there any way to fix this so that the curren...

I am trying to construct a report for total vendor payments by year (more than ytd and LY). I have used the inquiry (yearly and changed the years) , but am looking at an on demad report by class etc for all vendors for several years. I have loaded PM00200 PM00201 and PM00202 into a Crystal report, but can't figure out how to get the years as columns (buckets), any ideas?? Thanks Chris in KY wrote: > I am trying to construct a report for total vendor payments by year (more > than ytd and LY). I have used the inquiry (yearly and changed the years) , > but am looking at an on...

From: learning_codes@hotmail.com - view profile Date: Thurs, Nov 16 2006 9:24 pm Email: "learning_co...@hotmail.com" <learning_co...@hotmail.com> Groups: microsoft.public.excel Not yet ratedRating: show options Reply | Reply to Author | Forward | Print | Individual Message | Show original | Remove | Report Abuse | Find messages by this author Hi, I tried to convert the date to YEAR and then the year plus 25 Years later. =3DYear(A1) I'm getting the result 1900 instead of 1965. I tried to add 25 years later to 1990 from 1965. Your help would be much apprecated...

I want to track my gains (or losses) separately in different investment accounts but when I purchase a stock already owned in a different account it averages the costs and even pulls in gains achieved on stock that was sold in another account. I have tried checking the "Actual Cost" box. No change. In microsoft.public.money, Squamish guy <Squamish guy@discussions.microsoft.com> wrote: >I want to track my gains (or losses) separately in different investment >accounts but when I purchase a stock already owned in a different account it >averages the costs and even ...

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 ...

Hi, I have a continuous form with lots of data on it. In the form footer I would like to average certain fields by checking a checkbox next to the fields (in the detail section) I would like included in the average. I am having a brain fart on this and just not getting it to average the selected records. Can anyone point me in the right direction? Thanks "Lythandra" <Lythandra@discussions.microsoft.com> wrote in message news:262B5AEB-2692-4561-824C-D6FC7465FC1D@microsoft.com... > Hi, > > I have a continuous form with lots of data on it. > > In the form ...

Hi, I'm trying to create a report that compares revenues received this year/qtr/mo/wk with revenues received last year in the comparable year/qtr/mo/wk, and additionally by sales division and sales manager. I have the data stored in a table with fields: SlsDivID, SlsMgrID, DtRecd and AmtRecd. I know I will need to use the date part function, but am not sure how to get the info in the report I need. format should be: SlsDiv - SlsMgr - CY YTD - LY YTD - G/L and % - CY QTD - LY QTD - G/L and % etc etc. I have tried pivot tables (not working) and various group and total functions (a...