Specialized Average Formula Needed
Jan 14, 2009
In order to avoid embedding 12 if statements, I want to take an average of a selection of cells based on what month it is. So I have a "now" function, and then a month function referencing the "now" cell so that I get the current month as a number 1 through 12. I have a row of expenses (C14:N14) and I just want the average from January up to the current month. The reason I can't take an average of the entire selection is because the users don't want to have to enter a 0 everytime there's nothing, they'd like to leave it blank, so the sheet needs to recognize what month we're up to. And the average function in Excel only takes an average of the data that is present, not the entire range. For example, if the data in the first cell was 15, second cell was 15, and third cell was blank, the average function returns 15 and not 10. So what I need is, for example, if it's March, the average will calculate =average(C14:E14) and so on. Any ideas? Maybe it's a simple change of formula instead.
View 13 Replies
ADVERTISEMENT
Oct 12, 2008
I'm trying to make a spreadsheet for work so when im not there they can just enter the day for example sunday and what they have and then it will show them what they need to prep
View 4 Replies
View Related
Apr 9, 2014
I have a big list with names, money spent and date. I need somehow with a formula to do:
Take all the money spent on some specific day from a specific person an put it into a field.. and this for every date of the week and for everyname... Im gonna upload a photo.
Name
Money
Date
Daniel
5
01.03.2014
[Code] ........
I need the list of the left to produce me list of the right somehow, at least just the sum of everything spent everyday.
View 7 Replies
View Related
Jan 21, 2014
Basically I have in the first column, different products (lets call them a, b,c and d). in the second column is a counter which starts at 1 and continues counting down the column.
In the third column I need to identify if it is the last time the product has been run. i.e there are no more duplicates of product a further down the 1st column.
I have attached an example : example.xlsm‎
View 2 Replies
View Related
Feb 5, 2012
I'm in need of some VBA code to control the updating of information from 16 active workbooks to a master workbook and vise versa. I would like it on command as it slows down the use of the workbooks when they calculate after every change of data as they are being used. These workbooks are connected over a company intranet and are used 24/7.
The desire is that cells in the master workbook will read specific cells on each of the other workbooks and complete a "map display" of the data. The data is not in a column or row range side by side but scattered across the sheet to form a map when data is entered.
I've tried using code to open and close the workbook with on time events but that hasn't worked.
View 5 Replies
View Related
Sep 21, 2012
I have a phone list set up as LastName, FirstName PhNum all in a single column. The problem is, some last names have a space, some phone numbers are in the xxx-xxx-xxxx format while others are just an extension like xxxxx.
Example:
Smith, John 519-123-4567
Van Smit, Joe 12345
What can I use in a formula to extract the numbers so that I can end up with 2 columns, one with names, the other with numbers.
View 6 Replies
View Related
Dec 14, 2012
In my spreadsheet, I have data for each sale in each row. Column C is the buy price, and column D is the sell price. I need column C to be subtracted from column D to get the profit into column E so that I can AutoSUM any cells in column E for total profit over any time period. The problem I'm having is that in columns C and D, I have either a dollar sign in front, or the letter m following without a space (ie $45.00 or 150m). Many cells in column C also contain another word following the buy price, and many cells in column D have a date following the sell price (ie $32.00 12/11 or 42m buyout).
Examples of how my spreadsheet looks:
A1: 12/13/12 B1: Item #1 C1: 100m D1: 152m
A2: 12/14/12 B2: Item #2 C2: $20.00 D2: $35.00
A3: 12/15/12 B3: Item #3 C3: 90m bid D3: 120m 12/16
So looking at my example above, I would need cell E1 to automatically show "52m", cell E2 "$15.00", and cell E3 "30m"
How I can get a formula for all of column E that will give me my profit for both $ and "m" for each item (row). I have tried the SUM function but just keep getting the #VALUE! error, it does not ignore the text like many people say it should.
View 6 Replies
View Related
Jul 12, 2007
formula to calculate how many cells in a column contain data of any kind. The data might be a number, a word, or even a symbol, such as "):>:)" for example. The range will change from time to time. For example, one day it might be a2:a4956. A few days from now I might delete a few rows from the top, such as rows 2 through 26, so then the range would be a2:a4931. Then I might add rows at the bottom with data that I need to be counted. I dont want to have to change the formula evertime I add or delete rows with data. I do not want the formula to count a blank space created by the space bar as "data." One formula I used sorta worked, but if the cell appeared empty, but had been cleard out by pressing the space bar instead of the delete key, the formula counted it as a cell with data.
View 9 Replies
View Related
Jan 9, 2014
I am an architect and recently I was required to do some extensive calculation relating to water requirements and toilet requirements for a building that i was designing. i created a excel file, with a basic vlookup function to put certain values in the cells that are pre-defined for a particular building typology (mostly created by governmental bodies as guidelines). for example, when i choose a particular type in column E, column F and G fills up automatically. now this table also gives me occupant load based on what is the area i enter in column D.
The problem is that I want to automate the calculation of toilet requirements. Now, each typology will have a different formula to calculate WC/Urinal/washbasin.
1. there will be 17 different formula - one for each typology (as shown in sheet 5)
2. Column j will calculate what is the number of WC required based on 2 criteria - first id what is the typology and then looking what is the number of occupant.
The problem here is for example, if in E9, selected typology as "offices", the formula of offices typology will be used in J9 and occupant value from H9 will be used but if i have E18 also selected as offices typology, the same formula will use occupant value mentioned in H18 instead of using H9.
Hence, excel will first have to look what is the typology selected. based on that, it will load formula - out of that 17 different formula - specific to that typology and finally it will refer to corresponding row for occupant load and will use it in the formula to get the result.
View 3 Replies
View Related
Jun 11, 2014
I was working on a chart to calculate my Amazon seller rating to see how many more perfect order I need to hit my next target grade. Here is how the calculation works.
Order with no problem gets 100 points per order
Minor problem gets 0 point
Moderate problem minus 100 points
Severe problem minus 500 points
Grade are evaluate by:
<84.5 Fair
<96.5 Good
<98.5 Very good
Above is Excellent
Total points / total orders = final grade (or percentage)
As of now i got : 53800 (points) / 624 (orders) = 86.22(%)
Now that I know what grade I've got so far and I also want to know how many more perfect orders I need to achieve my next targets or in case some bad orders come by and how far they pull me down.
Attached is my work sheet so far and I'm just missing the calculation for the orange shaded part.
Amazon seller rating calculator.xlsx‎
View 3 Replies
View Related
Nov 27, 2012
I have a list of data in columns Q R S and i need to in between each line insert the data from columns D E F underneath and then underneath that the data from G H I
so bascially i have a list at the moment like this
Q
R
S
[Code]....
There is 2 sheets in total , 1 i need to inc 1-1 draw as above and the other sheet to be exc the 1-1 draw . I have attached an example of what i am trying to achieve . can offer as i have had this on excelforum.com with over 150 views and no solution
View 2 Replies
View Related
Jan 7, 2008
I'm using the formula =AVERAGE(B16:L16) to give me the average.
However I have a couple of problems with this. Firstly I would like to exclude the value zero from the average. Secondly to also ignore the lowest and highest values.
Example, if the values in the cells are 0,1,2,3,4,5,6,7,8,9,10 then the current result shows 5, by ignoring the 0 and lowest value 1 and highest value 10 the average should be 4.5.
View 9 Replies
View Related
Mar 21, 2014
A
B
c
d
[Code]....
So the data I put in Is Cells A to D the reast are calculated for me. so for example E2 is looking down column D to see if the number matches the number in A2, If any of them do it will put the name in the corresponding cell in E2 (Or you ca use a different cell if you prefer) so in this case D3=A2 so B3 is the answer also in the cell next to it I need whatever number is the cell next to the answer so in this case C3
View 3 Replies
View Related
Apr 20, 2007
On 18 worksheets I intend to use variations of the formula below to read ColsD:G and ColsK:N to ascertain whether the values are less then or equal (
View 9 Replies
View Related
Apr 27, 2009
Is it possible to do the following?
if cell A1 = 6 and cell A2 =5.8 (0.2 below A1) colour cell A2 orange
if cell A1 = 6 and cell A2 =5.5 or below (0.5 or more below A1) colour cell A2 red
if cell A1 = 6 and cell A2 =6.2 / 6.5 / 6.8 (0.2/0.5/0.8 above A1) colour cell A2 green
if cell A1 = 6 and cell A2 =7.2 or above (1.2 or above A1) colour cell A2 green
View 9 Replies
View Related
Nov 7, 2007
Cell: F6 is to represent "average weekly offering".
D2:D53: represents the Total Given Year To Date.
I need a Weekly Average for Offering, so is it possible to have a formula that can show the average weekly offering in cell F6?
View 10 Replies
View Related
Mar 31, 2007
I'm building a yearly workbook with multiple worksheets (i.e. one for each month, quarterly reports and one final yearly report). My question is that although I can have the data transfer easily to my quarterly and year end report sheets, I can't seem to figure out how to filter my data and then calculate an average based on personnel averages in the various categories I have set up.
My example is that I want to be able filter my personnel shifts (i.e. 7am, 3pm, 11pm, etc) and then set up a formula which can for example calculate a column of data and average it only based on the employees working that particular shift. My formula should adjust for the shift time I pick.
View 10 Replies
View Related
Feb 3, 2009
The number of columns vary, but the number of rows is constant. Lets assume, max_col is the number of columns and max_row is the number of rows per report.
Then, I need the following logic as VB macro.
for row = 6
F6 = (G6*G4) + (H6*H4) + (I6*I4)+...+ ("max_col:6" * "max_col:4") / (G4+H4+I4... "max_col:4")
View 2 Replies
View Related
Oct 26, 2012
I have a large amount of data in 2 columns which I want to statistically average to see if a relationship exists between two variables.
Column A: Contains Iodine concentrations ranging between 0 and 22000 ppm.
Column B: Contains Nitrate (NO3) concentrations ranging between 0 and 22 %.
I have over 7500 rows of data. In theory there is an endless amount of data but it will all be kept in these two columns
What I want to do is create average values of NO3 based on set Iodine ranges.
e.g. I want to know the average NO3 grade for the cells where the Iodine concentration is between 0 and 99 ppm.
I want to be able to calculate this average based initially on 100ppm Iodine bins but dont know what clause I would need to add into the formula or if this is even possible.
I would also like to be able to edit the bin size to see if I can discern a better relationship between the two using different ranges. As such I can create the bins in a separate column but again I don't know the best way to do this to make it work well in a formula.
View 1 Replies
View Related
Jul 8, 2013
I have data regarding the percentage of QC for the team.
I need to find out the individual's average QC scores from the list already available.
The columns that is in the primary sheet goes like this: Date/Name/Comments/QC percentage
View 3 Replies
View Related
Mar 3, 2014
I am trying to average a data set where some rows at the moment are blank but will update at a later date, I am getting a div/0 error, the formula I am trying to use is
=IF(C23:C30="","",AVERAGE(C23:C30))
I thought this would look at the data and see if its blank leave it blank but if there are numbers there it would give me an average.
View 4 Replies
View Related
Apr 4, 2007
I am trying to do a stock ( goods) inventry for a catering industry, meaning most good swill keep havinf cost prices changing.
I am trying to use a LIFO system. To achieve the end result, i am trying an average formula and i am trying something as follows:
=SUMIF(I4:I20,">0",I4:I20)/COUNTIF(I4:I20,">0")
To try it out, i created a similar page with the same amounts but using a normal multiplication and division formula, the totals dont marry.
View 9 Replies
View Related
Mar 11, 2008
I am working on formula to return an average of data.
Currently it is matching a text criteria.
Thus if (the text in) column a = (the text in) column b, (return the average of) column c.
The formula that I am using is =IF(A:A,B:B,AVERAGE(P:P))
This is returning - #value!
Now is this a formatting problem in column P? Or is the formula I am using incorrect?
I know that the text criteria (col A & B) matches.
View 9 Replies
View Related
Apr 30, 2008
I am trying to do an average of quartely results (2008 results) and because certain quarters can have no data present then my quarter shows 0%. But if i do a simple average of all Qs then the 0% affects my overall year to date. I need to keep the 0% because it may be a legit value. So i've devised a way to is if we actually have something in the cell (using isblank). So i test with isblank and if something is present then i use the Quartely value. It could be 50% or it can be 0%.
I would like to incorporate the snapshot but i can't because i do not have access to uploading a picture. If any of you wish i could email the pictures. When i test out my formula in different cells its works but when i combine them all together i get #VALUE! error.
In the example the error comes from the Q3 which test out and should give "" but when its added to the formula i gert #VALUE!. Shouldn't the average function disregard "" cells?
My formula is:
=average((if(and(isblank(F33),isblank(J33),isblank(N33))," ",R33)),(if(and(isblank(T33),isblank(X33),isblank(AB33)),"",AF33)),(if(and(isblank(AH33),isblank(AL33),isblank(AP33)),"",AT33)),(if(and(isblank(AV33),isblank(AZ33),isblank(BD33)),"",BH33)))
Maybe my logic is flawed or there is a better way to do it. Either way i would like your opinion on it.
View 9 Replies
View Related
Nov 13, 2009
I need to count the daily average of a task to a week ending number.
I need to see the current average after each day during the week. Example – Mon = 2, Tues = 4 AVERAGE is 3 – Wed = 2 AVERAGE IS NOW 2.6…and so on averaging out after each day is added.
View 9 Replies
View Related
Feb 1, 2010
Looking for an average formula.
All found formulas average within a string (ex. A1:F1).
My spreadsheet contains a series of three columns.
Example:
Column A reflects a number stat for a month (January).
Column B reflects another number stat for the same month (January)
Column C reflects the difference between Columns A & B.
Column D reflects a number stat for the next month (February)
Column E reflects another number stat for the same month (February)
Column F reflects the difference between Columns D & E.
and so on.
I would like to average the rows stats reflected in Column A, D, G, J, and so on.
Also, I would like to average the rows stats reflected in Column B, E, H, K, and so on.
Also, I would like to average the rows stats reflected in Column C, F, I, L, and so on.
View 9 Replies
View Related
Feb 21, 2010
There are (i think) 5 t-ball coaches
25 5 yrs old
44 6 yrs old
each were score on RUN, THROW, GROUND BALL, FLY BALL, BATTING,
scores were: 5-Excellent, 4-Very Good, 3-Good, 2-Average, 1-Fair
So I was thinking of making 5 sheets (one for each coach, with their scoring for each players)
The last sheet will be what the Average would be for each area from each coach, giving me a general idea about the players that I didn't get to watch and score.
So how would I formula the Avarage sheet?
Again I'm on short notice, and need to have this done on Sunday, when the information starts to come in and I can data-entry the scores, and have it ready for Monday nights draft.
View 9 Replies
View Related
Oct 11, 2006
I have a database of information for which I need to be able to calculate the average of the top 20% for each column for all those rows that meet a certain criteria. In other words, I need a formula that's equivalent to the DAVERAGE function but I only want it to work on the top 20% of records that meet the criteria range that I've specified (as opposed to DAVERAGE which works on 100% of records that meet the criteria range). The database continues to grow so I'm unable to predetermine what quantity makes up this top 20%. I've attached a small sample file to show you what I'm talking about.
View 5 Replies
View Related
Jan 23, 2007
Trying to average multiple cells that are not consecutive and contain zeros that i want to disregard. I have tried to do an array formula but the cells must be consecutive so I am at a loss. This is what i tried. =AVERAGE(IF(A2,A8,A12<>0,A2,A8,A12,””))
View 9 Replies
View Related
Mar 30, 2014
HTML Code:Â
=100*($I2/AVERAGE(IF($B2=$B$2:$B$38129,$I$2:$I$38129)))
I have the above array formula which I wanted it to calculate the average of all products selling in each store, but it seems to be calculated each?
In column b I have the store number, column I is the sales, I want to average all the sales in column I for each store.
I have probably over complicated?
View 3 Replies
View Related
