YTLE#90: Formula: Average w MONTH criteria

I am not sure which formula would be the easiest to calculate an average using month as one of the criteria. I tried to use averageifs but I cannot figure how to only pull values from a specified month

My database is something similar to

Date Classification Yield
Jan-20 Red 90
Jan-25 Blue 92
Feb-2 Green 88
Feb-13 Blue 93
Feb-21 Red 89
March-8 Blue 84
March-15 Green 86

I want to calculate an average yield for all "Red" for the Month of Jan what is the best way to go about this. I need very detailed instruction as I am not very good with excel. Is there a Mr Excel Youtube video that would help. I viewed YTLE 90 and I'm not that would work for me. Please Help!

Hello, I am hoping someone can help me;

I am doing my own accounts for my business and I'm trying to set up a system which on one sheet, shows the running total of takings so far in the year, my allowable expenses, the resulting taxable profit and then the goverment deductions (tax, NI and student loan). I'd then like to have another sheet of monthly summaries, detailing the same as the above but so I can take a wage and put some money away for the tax bill at the end of the year. I'm struggling to think of a formula which brings in the tax built up so far, minus what I've already put away from previous months to give that months contribution- I'm hoping this makes even the slightest bit of sense lol.

I've based my annual summary on the threshold I'm allowed before I start paying and I'm using an IF formula- if my taxable profits are less than 7500 (not right I know but for the example) then I pay no tax, if its more, I take 7500 from my profit and it gives me 20% of that.

I basically need to know how to average this figure over the 12 month summaries. I don't know if its possible or even if my question makes any sense, but hopefully someone can help me?!

See Attached.

The YTD 10 column is fine that will change everytime you update each month. I was wondering if there was a formula I could use for the Rolling 12 month Column.

At the moment it is averging from Mar 09-Feb10 which is correct but when it comes to putting in data for Mar 10, the Rolling 12 month column will obviously not change.

Is there a way of moving the formula along one each time you input data into a new month. So when Mar 10 has been entered the rolling 12 month column will pick up Apr-09-Mar-10 then when Apr 10 has been entered the formula will change again to May-09-APr-10?

Many Thanks....

Hi,

Using Excel 2007.

The data I have on Sheet1 has the date in col A1 - A365 and the number of eggs sold on each day in col B1 - B365.

The dates run from 1st Jan - 31st Dec 2011.

What I need to do on Sheet2 is provide on a daily basis the month to date average for the number of eggs sold on each day.

As an example :

10 eggs were sold on Jan 1st, 3 sold on Jan 2nd & 5 were sold on Jan 3rd. So the month to date average eggs sold on Jan 3rd was 18/3 = 6

Can anyone provide me with help on a formula, that I can enter in a single cell on sheet 2 which will keep track of this ?

I know that I need to sum col B on sheet 1 and then divide by the number of days which have passed so far that month but I am struggling with two things really - the fact that the denominator is incremental with each day that passes and the fact that each month doesn't have the same amount of days. Also the increment of the denominator needs to return to one with each new month.

All help gratefully received.

TIA

D

Any particular reason this array formula won't work? If I remove the or it works fine but I'm trying to get the average for the current month and the previous month. B1=Current Date.



=AVERAGE(IF(MONTH('Q.A.History'!C1:IV1)=or(MONTH(B1),MONTH(B1)-1),'Q.A. History'!C21:IV21))

I cannot remember how to calculate a 12 month rolling average for my
emissions. I believe I know the formula but need some reassuring.

Thanks.

Hi there,

I'm working on a dashboard to track employee productivity and accuracy. Presently I enter the accuracy data into one worksheet, and productivity into another. The dashboard is a seperate worksheet which contains formulae to display averages for accuracy and productivity for the current month.

I'm having a problem with my formula (below) to average the productivity stats as it is also counting blanks and/or DIV/0 cells and returning the incorrect average. I don't know how to make it stop this! I'd like to ignore blanks and DIV/0 cells so that the averages are accurate.

To average the stats I have to use a combination of SUMIF/COUNTIF as there is no AVERAGEIF in my version of Excel.

Code:

=SUMIF(Julie!$A$4:$A$5000,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),),Julie!$K$4:$K$5000)/COUNTIF(Julie!$A$4:$A$5000,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),))

Hi team,

I have a spreadsheet that has columns of monthly values for three years of financial data and where the values for the latest month are added to the last column. Months that have not been completed will have a zero value (e.g. Jul-09).

Jan-09



Feb-09



Mar-09



Apr-09



May-09



Jun-09



Jul-09



Aug-09



Sep-09



1



2



3



4



5



6



0



0



0






I want to indicate a trend based on the last three months and I am using the formula below. The absolute references (e.g. $AP$1) are to cells containing these symbols ▼, ▲, ▬ to indicate that the latest month is trending up, down or staying the same compared to the average of the last three months.

=IF(AD11>(SUM(AB11:AD11)/3),$AP$1,IF(AD11

I have a question about an average formula.

Below is the forumla I am using:
=IFERROR(AVERAGEIFS('MONTH TO DATE RAW DATA'!E:E,'MONTH TO DATE RAW DATA'!C:C,A30,'MONTH TO DATE RAW DATA'!AJ:AJ,"NO"),"0")

I am having it look at raw data on sheet 'MONTH TO DATE RAW DATA' and if comes across any that equals what is in cell A30 then average. I would like to do a OR statement where either match A30 or A31. How would I accomplish that.

I am using a great average formula that Aladin provided. However I would like to have it modified to ignore blanks and/or "" in the date column. The "" is produced when the end of month is reached. I use a counter in the date column to advance the date by one. I have a formula that writes a "" if the end of month is reached rather than have the formula show the next day of the next month. However the average formula results in zero when it encounters the "". When I take away the "", the formula works.

Feb 2011c


B C D E F G 4 February 2011




5





6 Date ph Lab Daily Flow Total 7 2/1/2011 6.94 0.0100 367 342 492,413 8 2/2/2011 6.93
305 184 265,522 9 2/3/2011 6.86 0.01 311 300 432,013 33 2/27/2011 6.72
313 297 427,001 34 2/28/2011 6.94
353 257 370,324 35





36





37





38 Average


0

Spreadsheet Formulas Cell Formula B7 =B4 B8 =B7+1 B9 =B8+1 B33 =B32+1 B34 =B33+1 B35 =IF(ISTEXT (B34) ,"",IF (B34+1>EOMONTH ($B$7,0) ,"",IF (ISNUMBER (B34) , (B34+1) ,"") ) ) B36 =IF(ISTEXT (B35) ,"",IF (B35+1>EOMONTH ($B$7,0) ,"",IF (ISNUMBER (B35) , (B35+1) ,"") ) ) B37 =IF(ISTEXT (B36) ,"",IF (B36+1>EOMONTH ($B$7,0) ,"",IF (ISNUMBER (B36) , (B36+1) ,"") ) ) F38 {=LOOKUP(9.99999999999999E+307,CHOOSE ({1,2},0,
AVERAGE (IF ($B$7:$B$37-DAY ($B$7:$B$37) +1=$B$4,
IF (ISNUMBER ($F$7:$F$37) ,$F$7:$F$37) ) ) ) )} Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

Excel tables to the web >> Excel Jeanie HTML 4

Is there a way to modify the average formula to still calculate the average?