Average, Excluding Zeros, Non-Consecutive Range
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?
....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...Creating payroll batch for 2006 prior to payroll year-end 2005
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
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
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....AVERAGE formula with a twist
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?
=AVERAGE(IF((...Average days to pay #3
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.
Please find below the information on how the Average days to pay is
When and how is the Average Days to Pay calculated in the Customer Credit
Summary window (Cards|Sales|Summary|Credit Summary)?
The Average Days to Pay field is updated when a debit document is fully
applied. The average is recalculated as the document i...Should not be able to enter a paycheck in a historical year
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...Can't remove grey/black shading on half my One Note page
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.
Submitted via EggHeadCafe - Software Developer Portal of Choice
Documenting Exceptional Developers!
> I was in the process of printing a table that I made in One note.
> However, now more than three quart...Average ERROR
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.
C36 = 6.00
C37 = 6.00
This formula gives me 12.00 instead of 6.00.
Other ranges work fine.
B36 = 119
B37 = 119
This formula gives me 119 (correct)
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 ...How do i change 15 months to read 1 year and 3 months?
Can some one please provide a formula on how to change months into years and
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.
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
=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 ...pattern in half of the cell
Is it possible to color one (diagonal) half of an Excel sheet cell instead of
the whole cell?
Maybe you could add a triangle shape over the cell (from the Drawing toolbar),
then shade that the way you want????
> Is it possible to color one (diagonal) half of an Excel sheet cell instead of
> the whole cell?
> thanks, roland
...Extracting the year from a date
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,
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/
"DavidObeid" <DavidObeid.xfkca@excelforum-nosp...30 Day Rolling Average
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:
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, firstname.lastname@example.org ...Changing the Year in formatted Date
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
if Date.year = 2009 then Date.year=2010
Date.year is incorrect. How do I change and save the date?
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)
Gary''s...add individual data points to a bar chart of averages
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?
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.
> I have groups of dat...No Closing Action during Year-End Closing
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?
...How do get the years and days Between two dates
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:
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:
(=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?
...How to stop calculating CPP when employee turns to 65 years of age
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
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...How about if next year W-2's could be printed on white paper?
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.
...How to populate the Last Year column in the Item History window
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
Is there any way to fix this so that the current year and last year columns
will show the correct figures?
got this taken cared of.
> 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...Vendor Payments by year
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??
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...Date Format turn to Year #2
From: email@example.com - view profile
Date: Thurs, Nov 16 2006 9:24 pm
Email: "learning_co...@hotmail.com" <learning_co...@hotmail.com>
Not yet ratedRating:
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse | Find messages by this author
I tried to convert the date to YEAR and then the year plus 25 Years
=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...Cant stop Money averaging cost of same stock in different accounts
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
I have tried checking the "Actual Cost" box. No change.
In microsoft.public.money, Squamish guy <Squamish
>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 ...Adding if to Average If Array function
I recently had help understanding the following formula
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
Can you mix and match ...Averaging Selected Records
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
I am having a brain fart on this and just not getting it to average the
Can anyone point me in the right direction?
"Lythandra" <Lythandra@discussions.microsoft.com> wrote in message
> I have a continuous form with lots of data on it.
> In the form ...Compare this year/qtr/mo to date with prior year/qtr/mo to date
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 %
I have tried pivot tables (not working) and various group and total