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

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

- Limit or Exclude cells in Average and Sum formula
- Problem in Average and Sum
- Add alternate cells in column and sum
- Average and Sum Grades
- How can I ignore BLANK or ZERO cells in a MIN/IF function??
- Macro to Insert a row above and below, if any cells in a coloumn has formula
- How to copy a cell in excell and paste in a txt file with macro
- EXCLUDE ADDRESSES IN A SERIES SUM
- Conditional Formatting if 6 or more cells in a range are not blank
- Exclude Hidden Cells In Average
- Exclude cell from average calculation
- How can I view or add cells in shapes and smart art?
- Search for a word in column and summing up in a different cell
- Pocket Excel How do I limit the # of cells in a sheet?
- Configuring SUM formula to include or exclude certain characters?
- Average non-adjacent cells in column
- Conditionally ignoring certain cells in columnar calculations
- Finding the minimum in a range while checking for conditions to exclude cells in the range
- Find first empty cell in range and copy data
- Data Validation List and Custom formula in 1 cell
- Insert Cells and Shift Down, Based on Blank/Not Blank Cell in Row
- Using the address properties from range objects in a sum formula
- Re: what formula do I use to count right-justified cells in a row?
- Using a relative or absolute? cell in formula

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

View this thread: http://www.excelforum.com/showthread...hreadid=385228

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,

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

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.

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!

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.

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:and then trying the formula: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 FunctionIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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

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

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

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

is this possible ?

thnx!

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

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!

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!

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.

- 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!

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?

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.

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:

= sum(r1.Address:r2.Address)

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

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

> >>

> >>

> >>

>

>

>

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.