# Having A Minor Issue With SUM And AVERAGE

Nov 16, 2008
I am having a bit of an issue with SUM and AVERAGE of a particular column. The sum is giving me zero total, and the average is giving me a DIV/0 error. Have a look at the attached spreadsheet. I am trying to sum and Average column "I", but as you can see, it is giving me a total of zero, and an average of "DIV/0" error. Also min and max calculations are not working for that column either. Obviously it has something to do with the formula or formatting in column I, but I am not sure what or why... The formula in column I works as I like it to.

View 3 Replies
ADVERTISEMENT
Sep 15, 2014

I have two columns. One with the major weight(pounds and one with minor with ounces. The first column would have a 1 the second 8 for 1 lb 8 ounces which I need to combine to say 1.5 Every way I do it it is coming up with 10.5.

View 5 Replies
View Related
Jun 29, 2009

I have Office 2007 and i use this code on my word.docm to insert selected photos. the problem I'm having is that it insert photo at top of page. can additional code be added so that it will insert photo in same table as command button. and in front of button, so that it will hide button

Private Sub CommandButton1_Click()

Dim sFileName As String

Dim ilImage As InlineShape

With Dialogs(wdDialogInsertPicture)

.Display

If .Name "" Then

sFileName = .Name

Set ilImage = ThisDocument.InlineShapes.AddPicture(sFileName, , True)

With ilImage

'set any additional properties such as left, top, etc., here

End with

Else

Exit Sub

End If

End With

End Sub

View 9 Replies
View Related
May 26, 2014

I have a very large database that contains names with minor differences on column A such as:

A

EXCEL AMERICAN COMPANY S.A.

EXCEL AMERICAN COMPANY SA.

EXCEL AMERICAN COMPANY, S.A.

ASSOCIATION, INDUSTRIAL

ASSOCIATION INDUSTRIAL

L.A. COMPANY CO

LA COMPANY CO

As you can see, this makes a mess when dealing with numbers associated with them and makes analysis more difficult.

What I've been trying to figure out is how to quickly get in column B a standard name for each one, for example:

B

EXCEL AMERICAN COMPANY S.A.

ASSOCIATION INDUSTRIAL

L.A. COMPANY CO

I was thinking of using a combination of the FIND and EXTRACT formula or maybe LEFT as well. (Maybe VB?)

View 6 Replies
View Related
Feb 17, 2012

I have a database of approximately 250000 email addresses and I would like to see if there is some way to use Excel to analyse the strings in order to identify potential 'non-unique' individuals.

For example, if I have the following email addresses:

12345@example.com

12346@example.com

12347@example.com

or

example1@example.com

example2@example.com

etc

Is there some way of extracting common text strings, lets say where there is a difference of 1 character / digit between strings (rather than alphabetising and searching manually)?

View 2 Replies
View Related
Nov 7, 2009

I've adjusted a jonmo code to add an item in col B which is not in col A to the bottom of col A. - fab code, thanks jonmo.

But.. i want to:

insert rows beneath those in column A to accommodate the added items and shade those cells in list A once they been added ( so the users now they've been moved )

I've posted the code below ( including my attempts at colour change where it shade the right cell but in the wrong column ) ...

View 9 Replies
View Related
Jun 14, 2013

E11 through E24 contains numbers and a few errors (#N/A) that need to persist (the errors need to show).

E10 needs to show the average of the numbers that are in E11 through E24, and just ignore the errors.

I have many columns like that - where the errors need to show and I need to show an average of the number/values that do appear, ignoring the errors.

View 14 Replies
View Related
Dec 9, 2008

i have two columns...a and b (a w/ names, and b w/grades). then i have the table lookup with names and grades all mixed up for many rows. i want to be able to average the grades with appropriate names.

=average(vlookup(name, table, column, false))?? i don't get it to work and how can i specify the grade to average?

View 5 Replies
View Related
Jan 23, 2007

TotHCInv.Value = WorksheetFunction. Sum(KRInv, PBLInv, CRInv, PVInv)

If i >= 34 Then CPSCtphRMA.Value = WorksheetFunction.Average("G" & (i - 30) & ":G" & i)

The first line runs properly, but the second line bugs out with the error message "Unable to get the Average property of the WorksheetFunction class". I can simply do the math, but I thought that using the worksheet function would be easier than summing and dividing. I'm curious, though, as to why I can't seem to use the Average function.

View 3 Replies
View Related
May 23, 2014

Can I get a minimum average and a maximum average, I have a worksheet with days of supply for 100 stores with about 100-200 products each, the dos resides in column D.

I was going to create a summary page and reference column d.

The following just gives me the min, I want the min average if possible:

View 5 Replies
View Related
Sep 1, 2006

I am attempting to calculate Grade point averages for my students for all classes. There are 5 columns of grades to be considered. I frist assign values of 0-5 to the grades then average the points. My problem is that I want the AVERAGE to ignore the zero but it calculates it as well. (I enter a 0 if I have no grade for that class.) I have tried the{ } to make it an array formula but this also did not work. Here is what I have, can anyone help?

=SUM(AVERAGE(IF(AND(L2>89,L2<100),5,IF(AND(L2<90,L2>79),4,IF(AND(L2>69,L2<80),3,IF(AND(L2<70,L2>59), 2,IF(AND(L2>0,L2<60),1,0))))),IF(AND(T2>89,T2<100),5,IF(AND(T2<90,T2>79),4,IF(AND(T2>69,T2<80),3,IF( AND(T2<70,T2>59),2,IF(AND(T2>0,T2<60),1,0))))),IF(AND(AB2>89,AB2<100),5,IF(AND(AB2<90,AB2>79),4,IF(A ND(AB2>69,AB2<80),3,IF(AND(AB2<70,AB2>59),2,IF(AND(AB2>0,AB2<60),1,0))))),IF(AND(AJ2>89,AJ2<100),5,I F(AND(AJ2<90,AJ2>79),4,IF(AND(AJ2>69,AJ2<80),3,IF(AND(AJ2<70,AJ2>59),2,IF(AND(AJ2>0,AJ2<60),1,0))))) ,IF(AND(AR2>89,AR2<100),5,IF(AND(AR2<90,AR2>79),4,IF(AND(AR2>69,AR2<80),3,IF(AND(AR2<70,AR2>59),2,IF (AND(AR2>0,AR2<60),1,0)))))),-1)

I know it is huge. The syntax is correct. It calculates the average, but always for all 5 columns. It will not ignore a 0 in a column.

View 10 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
Oct 11, 2006

Ok, I for some reason just cant wrap my head around this. I need to to get the average time per call of two rows, but they are based on how many calls taken.

so in one column i have 50168 calls taken at 4:21 seconds per call. and in the next row i have 597 calls taken at 5:20 per call. I need to see what the new average will be with them combined, and I need it to display in m:ss form.

View 9 Replies
View Related
Aug 13, 2008

to formulate Excel formulas to obtain the average buy price and average sell price for me to do this futures trading. Thanks a lot. I downloaded the Htmlmaker to post the spreadsheet here to show the manual way to calcualte the average buy price and average sell price but when it is on html form, i clicked on the 'Please click this button to send the source into clipboard' button & then i paste into this thread. Is the way to make my spreadsheet appear here correct cause it cannot work.

View 9 Replies
View Related
Jan 25, 2014

I wish to average the best three values (highest) of the last 5 values ( excluding zeros ) in a row.

View 2 Replies
View Related
Mar 15, 2006

What I am trying to do is track a 10 week rolling average, where scores are entered weekly in row A6 thru W6. The formula needs to count back starting with W6 until it counts 10 scores then average them. Some cells may be blank.

View 14 Replies
View Related
Jun 23, 2008

I have a column with time data in HH:MM:SS format. I need to find the average and sum of the entire column. But the problem is it does not give me the sum or average...

If I press F2 and then Enter in each of the cellsthen I get the sum or average. However, as there are thousands of entries, it's not possible to manually do F2 and Enter in all the data.

Is there a way to do it (F2 & Enter).

View 20 Replies
View Related
Jul 11, 2006

I have a list of 16,000 numbers in a column. I need to average every 62 consecutive entries, i.e average of 1-62, 63-125, 126-188.......

View 2 Replies
View Related
Jul 30, 2013

I have a spreadsheet which has data in the format of:

Date

Sales

Monday, 8 July 2013

191

Tuesday, 9 July 2013

180

[Code] ....

I calculate the average of the last four weekdays individually with =Average(A1,A8,A14..) in a cell above them to get a four week average.

Each time I add a new weeks worth of data, I manually adjust the formula to remove the first cell, and include the latest cell with new data.

I'm looking to find out if there is a way I can automate adjusting the formula so it always grabs the last four Mondays (for example)? Either in VBA or a formula.

View 3 Replies
View Related
Dec 9, 2013

Here is my code as it is right now (With the correct range, and yes its a messy range :

[Code] ......

That is my range and thats the range I have to use (The stats are spread out in those rows. How I can get this to disregard 0's? Right now it seems to fail on the third rangevalue (For example in the range above it fails and highlights Y88), but if i remove that value it does the same on the next one instead, always the third.

I have tried =AVERAGEIFS(Range,">0") versions and also ones with /COUNTIF after and others but cant get this to work.

View 4 Replies
View Related
Dec 20, 2013

screenshot.jpg

Ok, so from the attached picture (screenshot.jpg), you will see a data export of staff losses.

On the table to the right, i need cell G2 to take the ID from cell F2 and then search column A and add up every time there is negative number in column B.

So if staff ID has -15 , -15, -10 and -10 in the first table, their summary on the right table will show -50

(PS, i know it says AVERAGE LOSS) but i just need it to be the total. So basically, from the loss log on the left, it just counts the total losses occurred by each individual staff member on the summary table to the right.

View 2 Replies
View Related
Apr 20, 2014

i m using this to get average

r4/s4 = result(t4)

r4 = 5

s4 = 517

Result(t4) = 517/5 = 103.4

now i use the roundup function to get the decimal less value

=Roundup(t4,0) = Result = 103

i want both function in one function to get result

View 2 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
Oct 21, 2008

Is there a way to average every other row in excel?

View 7 Replies
View Related
Nov 16, 2008

The title isn't very good I know; I don't know the right terminology to explain the problem properly, but i'll give it a go: In column A I have a list of names, all of them appear multiple times. In column B there is a corresponding percentage. I would like to create a formula that takes the average of all the percentages that have the same corresponding name, or 'category'. I don't want to just go through and do it manually since there are roughly 32,000 different values.

I did have one idea; in column C I pasted in a list of all the possible names in column A and then in column D I used countif to find out how many times each name recurred. If both columns A and C are in alphabetical order, I thought I could use the normal average function, by doing, for example AVERAGE(B1:BD1), but this didn't work. Is there some special way in which you can use a cell name (i.e. D1) instead of a number in a cell range? I tried brackets, speech marks etc. but nothing worked. I have searched around a bit on this forum to see if my question had already been answered, and did some extensive googling, but I couldn't find a solution.

View 3 Replies
View Related
Jan 30, 2009

I have a spreadsheet with days of week and places saved as text values, and I need to get averages for specific items,

for example,

Day of week in A1, place in A2, number in A3

I want to get in A4 say, the average of the numbers for all sundays at cambridge.

Any ideas how I could do this?

View 9 Replies
View Related
Apr 19, 2009

Could you please supply a formula that will calculate a four week rolling average of column B in relation to the dates in column A? I tried using the average and offset functions but can't quite get it to calculate the correct averages.

View 9 Replies
View Related
Jul 7, 2009

Within a worksheet I have data like this in a column (actually, lots like it in many columns):

View 3 Replies
View Related
Aug 18, 2009

Field 1 input: Beginning Date (e.g 7/17/09)

Field 2 input: End Date (e.g. 7/23/09)

Need: Average costs on and between these given dates

From table with this information:

Date Market two

7/16/2009 80

7/17/2009 80

7/20/2009 82

7/21/2009 84

7/22/2009 82

7/23/2009 82

7/24/2009 82

7/27/2009 82

I need Excel to average Market two 7/17 to 7/23 (80, 82, 84, 82, 82) based on the dates I give it.

If I change the date to 7/20 to 7/24, I then need excel to average (82, 84, 82, 82, 82)

How can I get Excel to average based on different dates? Or basically, how do I get it to pull rows between 2 inputs?

View 4 Replies
View Related
Sep 15, 2009

I am needing to do a formula based on a range of data that will return the top third middle third and bottom third.

Example Data

W X Y

Gross Gross

Margin Margin% Rank

51,241 36.46%2

25,584 19.56%20

19,914 16.26%40

17,188 14.27%52

19,167 15.55%43

6,828 5.23%84

48,076 29.17%3

18,282 13.78%47

16,896 12.75%58

16,124 12.14%64

16,549 12.27%59....................

I first need it to look in the rank column for highest number and divide by 3

Then I need it to give me the average Gross Margin if rank is between 1-32, 33-64, 65-95

I have this for the top and bottom but can't get the mid range to work(except it doesn't automatically calculate)

SUMIF($Y$8:$Y$102,"<33",$W$8:$W$102)/32

I have tried averageif....

Using rank to calculate this may not be the most efficent way

View 3 Replies
View Related