Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










AVERAGE Without Range Ignoring Blanks


Trying to determine the best way to do this. I understand that the standard AVERAGE function will ignore blanks if given a range; the function I'm using does a search for a particular value to determine if a value is to be included in the averaging: ...


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Ignoring Blanks In A Dynamic Named Range
I used each of the following codes for dynamic name ranges.

View Replies!   View Related
Concatenate Cells, Ignoring Blanks, Then Trim
=IF(Z2="","",Z2&","&IF(AA2="","",AA2&",")&IF(AB2="","",AB2&",")&IF(AC2="","",AC2&","))

I'm using the above formula to join text from columns Z through AC, separated by a comma. I now want to remove the comma at the end of the new string. Also, I would prefer it if the four cells were separated by a slash rather than a comma, but when I simply replace the commas in the formula with slashes I get an error.

View Replies!   View Related
List First Instance Only Ignoring Blanks In ROWS
I have a set up of investments. Say A1 through to G1. Then on the next row the investments range from A1 to G1 again. This continues down many rows. There are spaces such as D1 and D2 through E2.

A
B
C
D
E
F
G
1
ausbil........................

Starting in column H, I would like to list the investments but ignore investments that have already appeared on the row (ie list only the first occurence) and ignore the blanks as well. For example, D1, E1, F1 are to be ignored because of the blank, and the fact that ausbil and amp already appear.
In row 2, the blanks in D2 and E2 are ignored as well as the repeated 'amp' in F2. So the table should look like below from column H:

H
I
J
K
1
ausbil
amp
vanguard
bt
2
amp
becton
fidelity
bt

Is there a simple formula for this? It has to be in rows as I have at least 60 columns of data before these columns begin. There are at least 300 rows.

View Replies!   View Related
Average Ignoring Holidays
If I have a formula which adds the value of each day of the week and divides by 7 to get an average per day, how do i fix it to get an average if their is a holiday so their would only be 6 days without changing the formula as the holidays could change from week to week by province.
=(a+b+c+d+e+f+g)/7 what happens if "e" is a holiday and I need to divide by 6, or in the case of xmas if "d and e" are holidays and I need to divide by 5?

View Replies!   View Related
Ignoring Cells While Calculating Average
I want to average the cells in column B but ignore values in the corresponding rows with a 1 or 7 in column C.

This will be used in a years data where 1 - 7 are days of the week and I want to separate weekdays from weekends when calculating an average.

View Replies!   View Related
Skip Any Blanks Rather Than Count Them In The Average
I want to figure a class average for a test, but I have students that have moved. My Excel is automatically counting them as a zero. How can I program/tell Excel to skip any blanks rather than count them in the average?

View Replies!   View Related
Average & Count Zeros But Not Blanks
I am trying to make a excell spread sheet that will calculate my students averages for the year. I need to account for zeros in the coarse and I want this excel sheet to track the current average all year long . So I do not need to include my blanks in the average as I go. Also , how do I formulate my average accum to show this formula - Test scores, four of them count as 80% of the total grade. So each test is worth 20% / Lab 10% of total grade and homework is additional 10%. I downloaded the templete from MSN and have tweaked it to my liking except for the coding above. Please advise. I am a below par on Excel. My attachemtn is below of my templete. One note. the templete gave me the room to include 13 ros of homework - but I will not necessarily use all of them... I can make it one row for home work only ....

View Replies!   View Related
Mitigating Blanks With Arrays AVERAGE(IF(
I'm using an arrays AVERAGE(IF( to average several scores from one spreadsheet to another. However, if I have 30 employees, 1 may have no score, and I don't want that no score to factor into the average, but with the above array, it does.
Specifically, I get the wrong average for Score A, but the right one for Overal Score. See attached sample.

View Replies!   View Related
Omit Blanks From Conditional Average
I have a formula to calculate an average value for a reference range:

= SUMIF(DATA!B32:B61,"<># div/0")/MAX(1,COUNTIF(DATA!B32:B61,))

Because these cells are references the blanks are being treated as zeros so I am getting an inaccurate average value.

Is there a way to make it so it will treat blanks as blanks? I need the zeros to be zeros.

View Replies!   View Related
Average Omitting Blanks / Zeros
I have run into a problem with the array formula. After inserting formula with the CTRL+SHFT+Enter it is giving me the same sum to the 3 cells that I had array formulas in but trying to capture different data from what was in the capturing column. Example:

If row F consists of text types: Move-in, Mid Year, and Year End as potential options,and row G is the score for that text type(cells will consist percentages), give me the average of all the cells in row G that are specific to Move-In only, but don't include the blanks in the average.

My existing formula isn't designated to exclude the blanks. how to exclude blanks and how to get it to stop giving me the same result in the three separate cells. My current formula is as such: {=AVERAGE(IF(F2:F73=E76, H2:H73))}

View Replies!   View Related
Sum & Average Including Zeros But Not Blanks
Attached is an xls with my formulas and problem. We need a way to factor in zeros in grading student workers. However, we also need a way to omit blank or null cells if the workers did not do a particular project. The formulas currently in the sheet compute zeros for both scenarios, lowering the overall 'grade' for workers who didn't do a project compared with workers who did the project but got a '0'

View Replies!   View Related
Conditional Moving Average Omitting Adjacent Blanks
how do I perform calculations on the last x non-blank instances in a data range?
for example, let's say I have a spreadsheet of 5 baseball players' batting averages (rows are team game number played, columns are at bats and hits for each player). I want to see how each player has performed in their last 10 games played, but some players have not played in every game. If I just use the sum function for the last 10 cells, I won't get the correct information for any player that has missed one or more of the last 10 games.

View Replies!   View Related
Ignoring The #DIV/0! Error In A Range
I'm trying to average a column range range =AVERAGE(A5:A29) that has blank cells not yet populated and also cells with formulas that contain the #DIV/0! error.

Obviously the cell with my formula produces the #DIV/0! error when it tries to average this range. Can someone point me in the right direction so the formula =AVERAGE(A5:A29) will work?

View Replies!   View Related
Counting Some Blank Cells And Ignoring Others In A Range.
Using formula rather than VB, I would like to calculate the number of blank cells that appear in a column BEFORE the first active cell but exclude any blank cells that appear after the first active cell. To elaborate, I have sheets that contain the days of each month and I need to exclude for other calculation purposes, the number of days (cells) where no entry of data has been input at the beginning of a month, NOT after the first data entry. ie. September has 30 days.

The first data entry is the 10th and there are no entries (thus far) after the 10th, the result that I seek, will be 20 (although only one cell has data), being the days left in the month AFTER the first entry. I have looked at COUNT functions but cannot find an solution. Perhaps it's not possible using basic formula?

View Replies!   View Related
Compare Formulas Ignoring Range References
I would like to rotate a Line Chart 90 degrees. I tried using an XY scatter chart but my Y-axis would be time values which are somewhat random but increasing. I would like to keep the spacing between plot points consistent (Y-axis spacing). I could simply use a number list for my Y-values to get consistent spacing but then I lose the time information. I donít necessarily need the time value on the Y-axis if I could get the information to display when mousing over the plot point. Any ideas on how I could accomplish this?

View Replies!   View Related
Convert Row Range With Blanks Into Continuous Row Range
How would you turn

A | B | C | D | E | F | G

1 2 3

into

A | B | C |

1 2 3


A | B | C | D | E | F | G

1 2 3

A | B | C |

1 2 3

View Replies!   View Related
How To Work With A Range That Has Blanks
Using the following bit of code that will Select the whole range of A2:C2 all the way down to the last row:

ws2.Range("A2:C" & ws2.Range("A2:C2").End(xlDown).Row).Select
How do I get around it if there happens to be a Blank cell(s) within the range.

I still need to work with the range even though there may be blank cells.


View Replies!   View Related
Count Non Blanks In A Range
I have use for this function on varying ranges. I pasted my function as well as my call to it. PhasesActive is just a named range of 5 cells. I get an error... by ref argument type error. Something with the argument, do I have to name the worksheet the range is on?

Function RangeValueCount(Rng As range)
'The function to check if a range has more than one value marked for 'selection, ex: The phases choices

For Each cell In Rng
If Not IsEmpty(cell) Then
RangeValueCount = RangeValueCount + 1
End If
Next cell
End Function

Call RangeValueCount(PhasesActive)
If RangeValueCount > 1 Then
msg = "There appears to be multiple phases selected. Please select only" & vbNewLine
msg = msg & "one phase at a time"
MsgBox msg
End If

View Replies!   View Related
Output Range To CSV With No Blanks
I'm trying to output a couple columns (and 300 rows) to a csv file. This code works wonderfully, save for the fact that it doesn't igore any cells in the range that are empty.

how I can get it to ignore empty cells?

Public Sub textFileDelim()

Const DELIMITER = ","
Const MYFILE = "E:EricTesting Folder2_Thurs.csv"

Dim Last_Column As Integer
Dim Last_Row As Long
Dim Row_Loop As Long
Dim Column_Loop As Integer
Dim FileNum As Integer

FileNum = FreeFile

View Replies!   View Related
VBA To Fill Blanks In A Range With A Formula
I am in need of a solution (probably VBA) that can fill a range of cells with a formula IF they are blank. Ideally that range is a named range I can define in Excel. If that is too hard, then a hard coded column I hand-edit the script for is tolerable. Also, ideally, this script auto-executes whenever data changes on the sheet.

You formula I will populate is:
=IF(ISNA((VLOOKUP(B33,$A$32:$L$43,12,FALSE))+1),"",(VLOOKUP(B33,$A$32:$L$43,12,FALSE))+1)
..but a simpler formula can be stubbed in.

NOte that it does have relative references, so the script needs to adhere to normal EXCEL conventions of enumerating cell references.

If the script points to a refernce cell that contains the formula that is uber.
Maybe it should do copy and paste instead of a string replacement in order to leverage EXCEL's referencing?

I'm stuck on this, and this would be VERY useful for many of my sheets to be able to point to a refernce cell containing a formula to fill in.


View Replies!   View Related
Stop Blanks In Entry Range
I have created a worksheet with different validation rules for different columns for users to enter data. Is there a way I can make it so each cell must be filled in? I don't want any blank cells. Is it possible to create some so that the user must fill in the cell before moving on to the next cell.


View Replies!   View Related
Concatenate A Range Skip Blanks
I have copied the following codes to create a Function of "Concatenate a Range Skip Blanks". However, I humbly seek help to add "," in between every word and also take away the empty space when there is only 2 words are concatenated. Pls refer to my attachement for easy understanding.

View Replies!   View Related
Count How Many Blanks (false) In The Range
i have a sheet with many formulas on it some in a range are if statements which output a blank ("") if the condition is not met i.e. false.

i need to be able to count how many blanks (false) in the range

View Replies!   View Related
Count Blanks In Dynamic Range
I want to count blanks in a horizontal range (all in one row) that will change (dynamic range). The values in the range could be numbers or words. Some values may be added to the end, but there may still be some empty cells to the right of the last value. My goal is to count blanks in the range up to the last entered value, but no beyond that. As an example:

A6 = 2
B6 = empty cell
C6 = 2
D6 = empty cell
E6 = tt
F6 = empty cell

The range for the count blanks would be A6:E6. F6 is not included because the last entered value is in cell E6.

The answer (count blanks in dynamic range) should be 2.

I have got these 3 formulas to work, but it seems that there must be a better (shorter, faster calculating, more elegant) formula than these:

=COUNTBLANK(OFFSET(A6,,,,MATCH(9.9E+307,MATCH(A6:F6,A6:F6)+TRANSPOSE(ROW(INDIRECT(""1:""&COLUMNS(A6:F6)))))))

=COUNTBLANK(OFFSET(A6,,,,MATCH(9.9E+307,MATCH(A6:F6,A6:F6)+{1,2,3,4,5,6})))

=COUNTBLANK(A6:INDEX(A6:F6,MATCH(9.9E+307,MATCH(A6:F6,A6:F6)+TRANSPOSE(ROW(INDIRECT(""1:""&COLUMNS(A6:F6)))))))

View Replies!   View Related
Summing A Range - Omitting Blanks
Trying to do the following. Look at a range of cells in a row, say A1- H1. Sum the last three cells that have a number in them versus being blank. So, let's say the last numbers in row 1 are in cells B1, F1 and H1, but in row 2 they may be in cells E2, F2 and G2.

View Replies!   View Related
Display Last Cell In Range With Blanks
How can I get the last cell with data, show in other cell? For example last cell populated info in total cell.

Date Cases Within 30 Days
01-Sep-0622.83
02-Sep-0622.78
03-Sep-06
04-Sep-0622.86
05-Sep-0622.82
06-Sep-06
07-Sep-06

Total22.82

View Replies!   View Related
Sort Range By Column Containing Blanks
I have some trouble to find a solution to sort blocks of data, the blocks have multiple headers and i would like to sort by the user column C.

I have attached a sample to easier understand my ramblings.

View Replies!   View Related
Dynamic Named Range Without Blanks
I need to populate a cell with a dropdown list via data validation. The source is a list that contains blanks both in between data and at the end of the list. Like this:

John
Peter
(BLANK)
Ann
(BLANK)
Carol
(BLANK)
(BLANK)
(BLANK)
etc.

The blank cells are actually not empty, but contains formulas (and I therefore assume I cannot use COUNTA for leaving them out).

Anyone knows how to create a source list for the data validation dropdown list that leaves out any blanks (containing formulas) in the middle and the end of the range?

View Replies!   View Related
Drop-Down List With Variable Range And Hiding Blanks
I currently have a list on a worksheet named "Options". It is a named range entitled "Type" with rows A2:A500. Another worksheet uses "Type" as a drop-down list. Currently there are only five entries in the list - cells A2:A6. Other items will periodically be added to the list. That is the reason for the long range - up to A500.

In the drop-down list, all of the unused cells in the named range show up as blanks. Is there any way to not have the blanks show up in the drop-down list?

Or...

Is there a way to define the named range as a variable range to enable users to add items without redefining the range and have the added items automatically show up in the drop-down list?


View Replies!   View Related
Ignore Blanks & Duplicates In Dynamic Named Range
I am having 2 problems with dynamic named ranges. On one hand, I am getting a LOT of duplicates in some ranges and a lack of entries in those ranges that have too many blanks. Here is a sample of the dynamic named range in the first column:

This first range is called "NamedRange_1"
=OFFSET(Data!$A$2,0,0, COUNTA(Data!$A:$A)-1,1)

how to eliminate both the duplicates and the blanks?

View Replies!   View Related
Average With Variable Range
I have a formula: =SUMPRODUCT(($D$18:$U$18>=$V$18)*($D$18:$U$18

View Replies!   View Related
Average A Range IF Within 30 Days
I have a spreadsheet with dates running down column J, and a number running down column K. I want to average the numbers in column K for all the rows that are less than 30 days old.

I have a formula for determining if a date is less than a month old:
=IF(DATEDIF(J2,TODAY(),"d")<31,TRUE,FALSE)

How can I translate that into averaging a range?

I'm using Excel 2003

View Replies!   View Related
Average Range By Conditions
I have written a function below which is supposed to give average of selected range and will ignore #N/A, if any. Naturally, i use array formula:

Function m_avg(mRange As Range)

m_avg.FormulaArray = "=AVERAGE(IF(ISNUMBER(mRange),mRange))"

End Function

the above doesnt work and gives me #VALUE!.

View Replies!   View Related
Calculating Average Of A Range
What could be the syntax (in a code) for averaging a range in col C. the range values are given in E3 and E5. (E3=508 & E5=1200) These values changes each time I open the workbook.

View Replies!   View Related
Average Over Variable Range
I've attached a sample worksheet. I have a series of time values in ascending order (column A) and then 5 sets of data that correspond to the time values. I wish to be able to enter a minimum and maximum time range then selectively average the numbers from the sets of data that fall within the time range, but I can't think of a simple way of doing this.

View Replies!   View Related
Average Range Of Times
I work at a call center, and I need to find the average duration of time our employees spend on the phone. Here are some of the call times:

0:00:00
0:00:01
0:00:02
0:00:03
0:00:04
0:00:05.......

How would I find the average call time of these calls? When I use the formula =AVERAGE(Num1:Num2) I receive a # DIV/0 error.

View Replies!   View Related
Conditional Average Of Range
I am writing this application wants me to calculate running averages and Std Dev. I will copy a row of values into a spreadsheet. The next row will be a new average of each column. So far so good. But the next time I calculate the new average of the column, I do not want to count the row that represents the last average I measured. SO, I'd like to format the rows that are average values (i.e. bold) and then when I calculate the next average, exclude any value whose font is bold. Currently I am using the following line of code to calculate average:

Is there an easy way to limit the values used to calculate average to only those values that are NOT bold?

Sub ColAve ()
Cells(LastRow + 1, ActiveCell.Column).Value = WorksheetFunction.Average(ActiveCell.EntireColumn)
End Sub

View Replies!   View Related
Average Over Time Range
I have some data, consisting of two columns. The 1st column consists of time values in the form hh:mm:ss, and the 2nd column consists of a pressure reading, taken every couple of seconds. I wish to find the average pressure from the start time, to a 2nd time, then the average pressure from this 2nd time to a 3rd time, then the the av. pres. from this 3rd time to a 4th time, and so on. The time i want to take the average over varies, sometimes 3 minutes (i.e. ~180 pressure readings), sometimes 4, sometimes 5 minutes. I have to take the average over different ranges dozens of times -

View Replies!   View Related
Find Average Lap Time From Range
[for excel 2000]
Hi chaps, I have a spreadsheet of lap times that I'm trying to generate an average time from. data looks a bit like this (in time format hh:mm:ss) :

01:23:34
01:44:23
01:56:34
01:23:56

-you get the idea. problem is, when calculating an average I get either #DIV/0!,#VALUE! or 00:00:00 depending on the approach I use (the column is definitely time format hh:mm:ss), so far I've tried:

=AVERAGE(H4:H433)
=AVERAGE(IF(H4:H433,H4:H433))

-and so on, none work all give a variation of the aforementioned errors. I've tried changing the column format to number, copying all the data to a new sheet, still no joy. This should be very simple, any idea what am I missing?

View Replies!   View Related
Finding Average Of A Range With Macro
I'm working on a project in which i had to calculate the average of particular field and that also with a macro in this application i had done that that's working supperb but i'm coming accross a problem according to which the range which i had to take average dosen't contains all integers

eg if range is A1:A10

then data is like

79
80
98
TBA
98
TBA
TBA
N/A
N/A
N/A

now ave for this range can't be calculated directly as many values are strings
what i need is

using a avg function on this range where TBA(To Be Anounced) is to be treated as 0 and N/A(Not Applicable) as null value

here's da avg dunction which i had used in macro

View Replies!   View Related
Calulate The Average Of A Range Of Cells
What i want to do is calulate the average of a range of cells. some of them have a "-" value. can i use average from the function selection.

View Replies!   View Related
Formula To Average Cells In A Range
There are words in a cell and to its right is a number.

I have a name in P5. I need a formula in Q5 to add all the numbers
next to the same name in Range B4:O46.

Michael


View Replies!   View Related
Discard A Number Out Of A Range And Average The Others 2
In the sample book attached I need to:

* Calculate if the numbers on J2:J4 are within 30% of each other (which I do and the results are in the table in O1:Q4.

* The numbers in J2:J4 (and accordingly the results from O1:Q4) change when the numbers in F2:F4 change

* In M2 I need to calculate the average of only the numbers from J2:J4 that fall within 30% range from each other. In the example attached, I need to average only J2 & J3.

I thought about 2 ways:

A multiple IF formula or an Averageifs formula.

The multiple ifs formula below works fine except when all 3 numbers are either within or out of30% range.....

View Replies!   View Related
Average Over A Text Range Using A Vlookup
I have a sheet with traffic-light conditions: g (green), a (amber) and r (red). So, for instance, I have a row with the following traffic-lights:
agrag

Now I want to do some calculations with these traffic-light conditions so I create a v-lookup table:
g 5
a 4
r 0

This would mean that the row would now read:
45045

So far so good. Now I want the average of this row. Usually this is easy, I just do a =AVERAGE(A1:E1) which would give me a result of 3.6. But because there a not actual values but text values which are translated by a vlookup So I would need to do a
=AVERAGE(VLOOKUP(A1;H1:I3;2;FALSE);VLOOKUP(B1;H1:I3;2;FALSE);VLOOKUP(C1;H1:I3;2;FALSE);VLOOKUP(D1;H1 :I3;2;FALSE);VLOOKUP(E1;H1:I3;2;FALSE))

which gives me the expected result of 3.6. Although this works it quickly becomes rather messy when I have thousands of rows that contain hundreds of traffic-light conditions for each row.

So, and here is my question, why can't I specify a range. So something like:
AVERAGE(VLOOKUP(A2;H1:I3;2;FALSE);VLOOKUP(E2;H1:I3;2;FALSE)). If I try this I get an error.

View Replies!   View Related
Calculate Average Over Variable Range
I'd like to calculate an average over a variable range. In col.A there are grades from A4:A21. In col.C there are the values for the start row of the range and in col.D the values for the end row of the range.

For instance the value in C4=4 and D4=9. In cell F4 I want the average calculated over A4:A9. Value in C5=10 and D5=15. In cell F5 I want the average calculated over A10:A15.

View Replies!   View Related
Conditional Average, Naming A Range
i'm very new to excel formulas & statistics in general. i only need to calculate averages but am having trouble naming the range in my formulas

in the example below, i want to use the first column to determine the range of data for the mean calculation.... so, I am seeking the average of just the numbers which correspond w/ A only. Similarly, I will then want an average for numbers corresponding to B, and then to C, etc etc...

View Replies!   View Related
Average Based On Date Range
Having trouble working out a macro for this...

Column B with dates and column C with values. I'd like to make another column with the cell value averages based on the date. In essence, calculating daily averages. In turn I will later be adapting it to go through the all sheets in all workbooks.

View Replies!   View Related
Average Of Each Group In 2 Column Range
I just want to say If Column B = "Team A" then add up Column A (where the individual's percentage score is) and then divide by the number of matches found (this last part is the main part I'm struggling with). I've got a column that has the team name for each row as well, what's the formula word for doing this, I though vlookup, but I may be barking up the wrong tree.

View Replies!   View Related
Average Of Range In Macro Code
I have to make VBA codes to get the average of two ranges. The problem is I always get 0 value. The value of each cells came from the formula that's why I use .TEXT. Here is the code.

iAveragePrep = WorksheetFunction.Average(Range(Cells(275, 4).Text), Cells(275, 5).Text)

View Replies!   View Related
Lookup 2 Different Criteria Then Average Between A Date Range
For those of you that love difficult ones, here's one..

I have a sheet that has a column of names.. Across the top row I have dates.. let's say from the 1st through the 31st.... in the body of the report i have different sales numbers and what I am trying to is the following...

1/1 1/2 1/3 1/4 1/5
Johnny 3 4 2 2 1
Becca 3 4 1 1 1

So let's say in this instance, mind you my real report is about 342 rows of names and stats.. Is look for Johnny between 1/3 and 1/4 and average those numbers between those 2 dates...

I've tried everything from sumproduct to vlookups and can't seem to get it right...

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved