Free Microsoft Excel 2013 Quick Reference

# Limit or Exclude cells in Average and Sum formula

I am curious if anyone can help me out. I am trying to create both an average and a sum formula that will limit or exclude certain fields. Here is an example of what I'm working with:

Day1: 25
Day2: 20
Day3: 20
Day4: 25
Day5: 25
Weekly Total: 115

Day1: 15
Day2: 20
Day3: 30
Day4: 35
Day5: 25
Weekly Total: 125

Daily average= ?
Daily Total= ?

Is there any way to run an average excluding the weekly total fields either by color or any other way. There is a SUM formula in the weekly total field, can formula fields be excluded? The same for the average of the daily numbers?

Thanks for any help you can provide.
Todd

## Related Results

### Limit or Exclude cells in Average and Sum formula

I am curious if anyone can help me out. I am trying to create both an
average and a sum formula that will limit or exclude certain fields.
Here is an example of what I'm working with:

Day1: 25
Day2: 20
Day3: 20
Day4: 25
Day5: 25
Weekly Total: 115

Day1: 15
Day2: 20
Day3: 30
Day4: 35
Day5: 25
Weekly Total: 125

Daily average= ?
Daily Total= ?

Is there any way to run an average excluding the weekly total fields
either by color or any other way. There is a SUM formula in the weekly
total field, can formula fields be excluded? The same for the average
of the daily numbers?

Thanks for any help you can provide.
Todd

--
dagger
------------------------------------------------------------------------
dagger's Profile: http://www.excelforum.com/member.php...o&userid=24996

### Problem in Average and Sum

Hi,

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).

Regards,

### Add alternate cells in column and sum

I need to add alternate cells in a single column. The cells contain a formula
result summing values in rows to the left of the result cell.

The cells to be summed in the column are formatted to display results in
currency format.

Cell range (column) to be summed AA5 to AA382 (using alternate cells i.e.
AA5,AA7,AA9 ...AA381)

Thanks for any and all assistance in advance.
John

### Average and Sum Grades

Column A contains school grades (70, 80, 90) and could contain many more.
Column B contains 'extra credit' points (i.e. 5)
I want to average by however many numbers are in column A (in this case 3) but I want the numbers in column A and B added before dividing by 3. Any way to do this formula? I know how to average and sum but don't know how to tell Excel to add all numbers and then add by 'however many numbers?' are in column A. Thanks in advance.

### How can I ignore BLANK or ZERO cells in a MIN/IF function??

I AM TRYING TO USE AN IF FUNCTION TO LOOK FOR THE LOWEST (MIN) NUMBER IN A RANGE OF CELLS THAT IS LESS THAN MY SELECTED CELL. EXAMPLE: =IF((MIN(\$F\$4:\$F\$18)<\$I\$4),TRUE,FALSE). Lets say that \$I\$4 equals 60, and the lowest number in the F4:F18 array is 61. This should return FALSE, but since some of the cells in F4:F18 have "0" or are Blank, the formula returns TRUE even though I DO NOT WANT IT TO! However, I must have some "0" or Blank cells in this F4:F18 cell range.

So, I tried to combine AND with IF to argue something like this: =IF(AND(MIN(\$F\$4:\$F\$18)>0,MIN(\$F\$4:\$F\$18)<\$I\$4),TRUE,FALSE). This way I get TRUE if values in my cell range are "greater than 0" AND less than my selected cell (\$I\$4). PROBLEM IS THAT this blasted Excel will only return FALSE, even though both these arguments are TRUE!! How can I get this argument =IF(AND(MIN(\$F\$4:\$F\$18)>0,MIN(\$F\$4:\$F\$18)<\$I\$4),TRUE,FALSE) to return TRUE when some cells in my array have a "0" in them?

THANK YOU IN ADVANCE TO ANYONE WHO CAN HELP ME RESOLVE THIS!

### Macro to Insert a row above and below, if any cells in a coloumn has formula

Hi, please bear with me as its only my second post and still finding my way in the VBA world.

In my spreadsheet, some of the cells in col I has formulas. I want a macro to go down col I and insert a row above and a row below any cell which has formulas in it. If a cell has no formula, leave it and go to the next cell down.

Many thanks for help.

### How to copy a cell in excell and paste in a txt file with macro

how to copy a cell in excell and paste in a txt file with macro

### EXCLUDE ADDRESSES IN A SERIES SUM

how do i exclude addresses in a series sum function

### Conditional Formatting if 6 or more cells in a range are not blank

I'm trying to create a work schedule so that if 6 or more shifts are being scheduled, that the cell will highlight. I was able to set up the formula in the 7th cell by just doing a not(isblank(ref)+isblank(ref) etc. but I'm stumped about creating a formula that highlights a cell if 6 or more cells in a range are not blank. Any help would be greatly appreciated! TIA!

### Exclude Hidden Cells In Average

Greetings, I have a large data table that has autofilters on the headings.

I also have a "Data Summary" Sheet in which I would like to get a conditional average.

Here is my *working* array formula:

=AVERAGE(IF('Data Table'!\$C\$10:\$C\$65536=A2,'Data Table'!\$BP\$10:\$BP\$65536))

The problem is, it averages all cell values (conditional upon the C column's cell being equal to A2 of course), whereas I only want to average the VISIBLE cells that match the condition.

I have tried using a little VBA with:
```
VB:
Range
'Returns the subset of Rin that is visible
Dim Cell       As Range
Application.Volatile
Set Vis = Nothing
For Each Cell In Rin
If Not (Cell.EntireRow.Hidden Or Cell.EntireColumn.Hidden) Then
If Vis Is Nothing Then
Set Vis = Cell
Else
Set Vis = Union(Vis, Cell)
End If
End If
Next Cell
End Function

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```
and then trying the formula:
=AVERAGE(IF(Vis('Data Table'!\$C\$10:\$C\$65536)=A2,Vis('Data Table'!\$BP\$10:\$BP\$65536)))
but it does not work.

Any ideas?? Thanks in advance.

### Exclude cell from average calculation

I have a worksheet that contains test results in various rows/columns. A
formula at the bottom of the column of data calculates stats on this data e.g:
=average(B2,B5,B8,B11,B13)

Occasionally an individual value is identified as an outlier and must be
excluded from the average calculation. To make this easy I'm thinking about
adding two items to the Cell shortcut menu (the one you get when you
right-click over a cell)- "Exclude cell", and "Include cell".

Currently, to exclude a cell from the average calculation I insert an "x"
before the value. This works, but does not look so good. Can anyone think of
a better way to exclude an individual cell from the calculation without
changing the formula itself? Is there a character other than "x" that would
not be seen, or any other method?

Very grateful for any help...

Cheers,
Dave

Here's the code I'm using behind the shortcut menu:
Const cAppTitle As String = "AppTitle"
Const cExcludeSymbol As String = "x" 'string to add or remove indicating
excluded cell

Private Sub ExcludeResult()
'''Add a "x" to the start of a cell to indicate exclusion from calculations
' Run from worksheet shortcut menu

Dim rngS As Range, rngA As Range, rngR As Range
Dim strTemp As String

If ActiveSheet.Parent Is ThisWorkbook Then 'don't run in other workbooks
If TypeName(Selection) = "Range" Then 'don't run if chart etc is selected
Set rngS = Selection
For Each rngA In rngS.Areas
For Each rngR In rngA.Cells
strTemp = rngR.Formula
If Left(strTemp, Len(cExcludeSymbol)) <> cExcludeSymbol And
Left(strTemp, 1) <> "=" And Len(strTemp) > 0 And IsNumeric(strTemp) Then
'not already excluded, not a formula, not blank, is
numeric
rngR.Formula = cExcludeSymbol & strTemp
rngR.Font.ColorIndex = 15
End If
Next rngR
Next rngA
Else
MsgBox prompt:="Please select a cell to include or exclude first!",
Buttons:=vbExclamation, Title:=cAppTitle
End If
End If
End Sub

Private Sub IncludeResult()
'''Remove a "x" from the start of a cell to remove exclusion from calculations
' Run from worksheet shortcut menu

Dim rngS As Range, rngA As Range, rngR As Range
Dim strTemp As String

If ActiveSheet.Parent Is ThisWorkbook Then
If TypeName(Selection) = "Range" Then
Set rngS = Selection
For Each rngA In rngS.Areas
For Each rngR In rngA.Cells
strTemp = rngR.Formula
If Left(strTemp, Len(cExcludeSymbol)) = cExcludeSymbol Then
rngR.Formula = Right(strTemp, Len(strTemp) -
Len(cExcludeSymbol))
rngR.Style = "Normal"
End If
Next rngR
Next rngA
Else
MsgBox prompt:="Please select a cell to include or exclude first!",
Buttons:=vbExclamation, Title:=cAppTitle
End If
End If
End Sub

### How can I view or add cells in shapes and smart art?

I am trying to make a form where I can enter data that I can link to to populate some graphs and tables, but I wan't to make the forms out of either shapes or smart art. The problem is that I can't add in a cell within a shape(or view the cells behind it) so I can't link the data.

Can I somehow link a formula to data in a textbox, or insert or view a cell within a shape?

### Search for a word in column and summing up in a different cell

hi guys need some help!

i need a way to look for a word in a column, and sum them quantity found in a cell.
is this possible ?

thnx!

### Pocket Excel How do I limit the # of cells in a sheet?

I am very new to excel, and I am using excel '97 to make a workbook for use on my PDA (iPAQ 1910 PPC). I have everything working fine, running SpreadCE on the iPAQ (many functions are missing from PocketExcel).

Each worksheet is laid out so that all relevant cells fit on the screen at the same time (screen size is 240 x 320). I want to keep cell A1 fixed in the top left corner of the screen, so that I do not have to scroll back after entering data, or if I "click" in the wrong place. I thought I would do this by limiting the number of cell in each sheet, ie removing all the blank cells to the right and below the cells I want on the screen. That way, there would be nowhere to scroll to, so the view would be fixed.

Is this possible, or is there another way to fix the view?

Cheers

Steve

### Configuring SUM formula to include or exclude certain characters?

Hi all,

I have a spreadsheet to keep track of my record company's sales. We're tallying both units sold and dollar amount (the latter of which may vary depending on where an album is sold to).

Attached is a screenshot of the format that I have going right now. I realize that there are better ways to lay out this information, but right now I'm not looking for suggestions on more efficient ways to manage the data.

What I'm looking for is one of the following: A way to take a sum of a column and have that sum only include odd or even numbered rows. Or a way to sum a column and include or exclude cells based on the formatting (a presence or lack of the \$ symbol). Or some other way.

Any help is very much appreciated!

### Average non-adjacent cells in column

I need to find an average for this column, excluding cells with 0 and the sub-totals in row 18 and 30. Can anyone help? I can exclude the 0's but am stuck when trying to average the non-adjacent cells.

### Conditionally ignoring certain cells in columnar calculations

It's probably easier if I ask this using an example, so please reference this
sample:

A B

1 0.3 0.2
2 0.4 0.3
3 0.5 0.4
4 # N/A N/A 0.5

I use a Bloomberg add-in to automatically load data into Excel. If for some
reason this data is not available, the add-in will return a value such as the
one seen in cell A4.

What I'm trying to do is sum these numbers and calculate the percentage
change from the sum of column B to the sum of column A. However, calculating
the percentage change in three quarters' worth of data over four is useless,
so I'm trying to find a way (SUMIF?) to exclude cell B4 from the sum
calculation of column B if the adjacent cell (A4) is blank or contains an
error code like the one shown above. Is this possible?

Thanks so much for the help!

### Finding the minimum in a range while checking for conditions to exclude cells in the range

I am having the following problem which I would like to solve using only excel functions without resorting to programming in vba or macros or manual commands.

I have a list of numbers in an array and I am looking for the minimal number in that array that satisfies some conditions, the conditions are such that they might exclude some cells in the array.
The ideal solution would have been a command like MINIF(range,condition) but such a function does not exist for MIN (it exists for counting COUNTIF)

How do I achieve the same result using other EXCEL functions?

Keep in mind that the array length and the conditions change dynamically during the run.

### Find first empty cell in range and copy data

I am looking for a formula function or a vba code where:

- In workbook1 find the first cell that is empty between range A7 -
A10,
- In workbook2, in Range G10- G13: find the word "Day1".
- If the word "Day1" exists in cells G10 or G11 or G12 or G13, copy
the particular cell or cells where "Day1" exists to the first found
empty cell or cells in range A7-A10 in workbook1.

Lets suppose cells A8, A9, A10(workbook1) are empty cells, that means
A8 is the first empty cell.
And G10,G11,G12, G13 (workbook2) have the word "Day1"
Then,
Copy cell G10 into cell A8
Copy cell G11 into cell A9
Copy cell G12 into cell A10

Please let me know if this explanation is confusing.... and I can try
explaining again.

Appreciate all help!

### Data Validation List and Custom formula in 1 cell

Dear Smartest Excelers In The World,

Is there a way to have Data Validation List and Data Validation Custom formula in the same formula?

Here is the complete problem description:

1) I made a list of 20 first-names of people on Sheet3, one first-name per cell, no formulas or anything on Sheet3, it's all typed letter by letter.

2) I named those cells on Sheet3 NAMES so that I can use Data Validation on it afterwards

3) I made 15 data validation cells on sheet1, each one is set to allow: LIST and the source for each one is NAMES

4) I'm happy because in each of those data validation cells on Sheet1 I have a dropdown list allowing me to choose amongst any of the firstnames on sheet3

5) I'm unhappy because I can choose one firstname in one data validation cell in sheet1 AND THE SAME ONE in another data validation cell in sheet1
(And this is for a timetable so I don't want 2 firstnames to be able to have the same timetable)

6) I found a method for disallowing duplicates amongst all my data validation cells: (hypothesis: let's say my 15 Data Validation cells on sheet1 are in A1:A15)
I select A1 then I select A1:A15, I go to DATA VALIDATION, I allow CUSTOM, and in SOURCE I put: =COUNTIF(\$A\$1:\$A\$15,A1)=1

7) Now I cannot put the SAME firstname, in 2 different cells amongst my15 data validating cells in sheet1 BUT I lost my drop down list!!!

8) I want both at the SAME TIME in each of my 15 data validation cells on sheet1!!! (A dropdown data validation list AND disallow duplicates amongst those 15 data validation cells on sheet1)

Any ideas?

### Insert Cells and Shift Down, Based on Blank/Not Blank Cell in Row

I have a spreadsheet that has data in columns A and B that need to line up with data in Column C but doesn't (I cannot change the way this data comes to me). There are no blank cells in columns A and B, but many in column C. Every value in A/B corresponds to a value in C. When properly aligned, all values in columns A and B should line up with a value in column C, and all the blanks in C should have blank cells in A and B. I'm doing this alignment manually right now.

I believe what I need to do is create a macro that goes row-by-row and checks to see if the cells in columns A (or B) and C are blank. If A is not blank and C is blank, I need to insert cells in just A and B so that everything below that row in A and B shift down, and then move to the next row to check. If both A and C are blank, it shouldn't do anything to the row but should move on to check the next row. The # of rows that need to be checked may vary from time to time.

I'm a beginner-level VBA so any assistance would be appreciated.

### Using the address properties from range objects in a sum formula

I am trying to create a sum formula that uses two range objects as the input
to the sum formula. For example I have the following ranges dimensioned:

Dim r1 As Range, r2 As Range

Further down in the code have the following lines:

Range("c22").select
Set r1 = Range(ActiveCell.Address)
Range("C40").Select
Set r2 = Range(ActiveCell.Address)

Now I want to do a sum formula using these two ranges. I then go to a blank
cell in code and try to enter this:

When i try this it doesn't compile and throws an error that says expected
list seperator or ).

Can someone point me to some documentation or otherwise advise me on how I
should be approaching this problem?

Thank you very much!
JEFF

### Re: what formula do I use to count right-justified cells in a row?

You can't justify your boss's decisions, but you can justify cells and you
can count the cells that you have justified. Suppose that you have a set of
cells with text in them and have right-justified some of them. Select the
cells in question and run:

Sub countj()
Dim R As Range
countjustify = 0
For Each R In Selection
If R.HorizontalAlignment = xlRight Then
countjustify = countjustify + 1
End If
Next
Cells(1, 1) = countjustify
End Sub

This will count the number of right-justified cells and put the result in
A1. It will even count empty cells that have been right-justified.

Maybe this can be adapted to your needs (you could make a function out of it)
--
Gary's Student

"Bernie Deitrick" wrote:

> Laura,
>
> You were clear, but maybe I wasn't clear in my explanation. There are NO formulas in Excel that
> depend on justification, so there is NO way that your boss's calculations can depend on
> justification. The one EXCEPTION is the visual difference between numbers and text, but you cannot
> determine the apparent justification of a non-formatted cell except based on the contents.
>
> Perhaps you should post the formula that you boss thinks depends on justification.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Laura" <lragsdale@geo-logic.com> wrote in message
> news:C16833F0-E35C-45AE-91FE-E1D3D571274C@microsoft.com...
> > Maybe I wasn't clear . . . I don't need to sum the total of the cells I need
> > to know how many cells are right-justified in each row. I hope that is a
> > clearer explanation of what I am looking for.
> > --
> > Thank you for your assistance.
> >
> > Laura
> >
> >
> > "Bernie Deitrick" wrote:
> >
> >> Laura,
> >>
> >> Justification doesn't change the underlying cell value, so it can be misleading. If you enter a
> >> number, the default justification is right: enter that smae number with a leading single quote
> >> (to
> >> enter it as a string) and the default justification is left. But you can change either
> >> justification using formatting.
> >>
> >> For normally entered values in non-formatted cells, if you use a formula like
> >>
> >> =A1+A2
> >>
> >> and you will get the sum of the values, whether they are strings or not, as opposed to
> >>
> >> =SUM(A1:A2)
> >>
> >> which will only sum up true numbers.
> >>
> >> To count the number of true numbers in a row, you can use (to count the numbers in row 1):
> >>
> >> =SUMPRODUCT(ISNUMBER(1:1)*1)
> >>
> >> Your boss needs to do a better job of designing his spreadsheet.
> >>
> >> HTH,
> >> Bernie
> >> MS Excel MVP
> >>
> >>
> >> "Laura" <lragsdale@geo-logic.com> wrote in message
> >> news:AA5BB120-71C3-461E-BA96-40830D76FD1D@microsoft.com...
> >> >I am working on a spreadsheet and the project manager needs me to write a
> >> > formula to count right-justified cells in each row (so he will know if the
> >> > information is going to be used for his calculations). I was told that Excel
> >> > doesn't have anything built in to do this, but can it be done. If so, how????
> >> > --
> >> > Thank you for your assistance.
> >> >
> >> > Laura
> >>
> >>
> >>
>
>
>

### Using a relative or absolute? cell in formula

Hey,

I'm working on my first real macro - so I'm a greenhorn. I've spent a few hours trying to research a solution, but most threads are too technical for me so I'm hoping someone can really help me out.

I am trying to use a formula that references a cell that changes day to day from when I use the macro. I need to use a formula which grabs from a cell in a non concrete location. Let me try and show you what I mean.

From I4:I10, I need a formula like this:
```The part in red is the cell I have trouble referencing. In other words, I am trying to reference a cell in a separate range,
but that cell changes rows. This is probably really vague, so I'm sorry.Is there a way to name a cell in a range?
Because the cell I'm looking for is always D2 in the range I created. But the cells with the formula are not in the range, so
I'm having trouble referencing that particular cell. I appreciate any help that is offered, but please keep in mind my
knowledge is extremely limited - especially with various functions, etc.

```

No luck finding an answer? You could always try Google.