#### Average Row Formula

```Is there a way to calculate the average of a row if some cells are
empty but when calculating the average they should have the value of
the cell to the left?

If the following data is enterered

A B C D E F G

1 3     5   6

For the calculation  the cells should use the data

A B C D E F G

1 3 3 3 5 5 6

Also what would the average formula be if you wanted to skip one of the
cells from the average calculation?

Thanks

Tom

```
 0
5/7/2005 12:44:10 PM
excel.misc 78881 articles. 5 followers.

2 Replies
531 Views

Similar Articles

[PageSpeed] 1

```pls do NOT multipost. It wastes resources

--
Don Guillett
SalesAid Software
donaldb@281.com
"Tom" <tsanders123@hotmail.com> wrote in message
> Is there a way to calculate the average of a row if some cells are
> empty but when calculating the average they should have the value of
> the cell to the left?
>
> If the following data is enterered
>
> A B C D E F G
>
> 1 3     5   6
>
> For the calculation  the cells should use the data
>
> A B C D E F G
>
> 1 3 3 3 5 5 6
>
> Also what would the average formula be if you wanted to skip one of the
> cells from the average calculation?
>
> Thanks
>
> Tom
>

```
 0
Don
5/7/2005 1:37:30 PM
```Answered in microsoft.public.excel

Jerry

Tom wrote:

> Is there a way to calculate the average of a row if some cells are
> empty but when calculating the average they should have the value of
> the cell to the left?
>
> If the following data is enterered
>
> A B C D E F G
>
> 1 3     5   6
>
> For the calculation  the cells should use the data
>
> A B C D E F G
>
> 1 3 3 3 5 5 6
>
> Also what would the average formula be if you wanted to skip one of the
> cells from the average calculation?
>
> Thanks
>
> Tom

```
 0
5/7/2005 2:11:16 PM

Similar Artilces:

help with problem formula
this formula worked up to yesterday. i had a power surg and now it onl give me one color. Private Sub LCase(Target.Val Target As Range) Dim myColor As Long If Target.Cells.Count > 1 Then Exit Sub If Intersect ( Target, Me.Range("d:d")) Is Nothing Then Exit Sub Select Case LCase(Target.Value) Case Is = "a" : myColor = 33 Case Is = "b" : myColor = 38 Case Is = "c" : myColor = 20 Case Is = "e" : myColor = 35 Case Is = "f" : myColor = 40 Case Is = "g" : myColor = 8 Case Else myColor =x1None End selection Target.Interior.Col...

show columns on other worksheet without using formulas
Hi, I have 1 worksheet containing lot's records. Just like in access, each line is 1 record, and each column contains data. ex: WORKSHEET 1: name title address city 1 dfjw kjhk kjhkj kjhk 2 lmkj lkjlk lkjkuf guyg 3 drdtg xcx yjutuy hgyy Now I want to have a selection of columns on worksheet 2: ex, only name and title: WORKSHEET 2: name title 1 dfjw kjhk 2 lmkj lkjlk 3 drdtg xcx I will only add/modify rows in worksheet 1. And I want that this is filled automaticaly in worksheet...

Pivot table, calculating % difference of subtotal rows
This is what I need to do: I have rows of data in a pivot table that have subtotals associated with them (automatically generated via the pivot table). I need to to take the subtotals for 2009 and 2010 and calculate the % difference. Here's a screenshot of what I'm talking about: http://home.comcast.net/~wilsoch/PercentDiff.jpg Is this even possible or am I going to have to do this manually? Hi I have'nt seen the screenshot as am at work , go to Pivot Table>Formulas>Calculated Field wilsoch@comcast.net wrote: >This is what I need to do: I have rows of ...

Subtract several rows from a total
This is a multi-part message in MIME format. ------=_NextPart_000_0029_01C53AC8.AD6EB0F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi Guys, I have a total in cell C32. I want to subtract numbers in cells C 34-40 = and have the answer in cell C41. I know that =3DC32-C34 will give me an = answer but I want several rows to be subtracted. What would my formula = be? I have used the insert function feature before but I don't see diff or = anything that looks like a subtraction function in there. =20 Thanks, Linda ------=_NextPa...

help with the syntax for using range name in a formula?
I have --------=3D+AVERAGE(SMALL(B7:B19,{1,2,3}))-------------. I want to replace B7:B19 with =93Myrange=94 but I can=92t seem to get the syntax right. excel says error in formula. Thks in advance for any help BRC Hi =AVERAGE(SMALL(Myrange,{1,2,3})) have you created Myrange? have you spelt it correctly? -- Regards Roger Govier "BRC" <brc1051-googrps@yahoo.com> wrote in message news:d0e09817-22dc-4c36-b60d-67a397067441@h9g2000yqa.googlegroups.com... > I have --------=+AVERAGE(SMALL(B7:B19,{1,2,3}))-------------. I want > to replace B7:B19 with �Myra...

Deleting rows containing common data
I have an Excel 2000 spreadsheet with 30,000 rows. The rows look like this: 007-007 68-4611 68-4682 total 007-007 total 007-007 28-4831 68-2454 68-4682 total 007-007 28-4831 68-2454 68-4682 total 007-007 28-4831 68-2454 68-4682 total 007-007 68-4682 total 007-007 68-4682 total How do I delete the rows containing "total" One way: 1. Select the range and apply an AutoFilter (Data > Filter > AutoFilter) 2. On the drop-down list, select "Total". 3. Select the range again if needed. 5. Press F5 > Special and click "Visible Cells Only." 6. Remove...

Continuation of Formula in New Array
Hi I am using this formula VLOOKUP(\$B\$1,'Team A'!\$A\$18:\$IV\$45,MATCH(\$B5,'Team A'!\$18:\$18,0),0) The Array starts from A18 As you realise due to Excel limitation (!) the array only goes as far as Column IV; I wish to continue the Table array ( starting from a new row rather than a new worksheet) say from Cell A50 to IV77 and then continue again from A82 to IV109 and perhaps etc Is it possible. Please Note If it is to be done in Macro Please provide me a detailed Idiot Guide as I have NEVER PERFORMED/USED a Macro, and would not know where to start. Thx Gunjani I don'...

Average if
Hi All, Am trying to get an average with the following =AVERAGE(IF('Commercial Register'!N14:N2437="Repair",'Commercial Register'!\$AD\$14:\$AD\$2437, 0)) So if results in column N meet "repair" criteria, it averages the results in column AD No Joy even in an array format. Any ideas??? Cheers Jim you can try sumif / countif -- Gary Keramidas Excel 2003 "OzzyJim" <OzzyJim@discussions.microsoft.com> wrote in message news:D6EC39C6-6F55-4D10-9C8D-48028024CD38@microsoft.com... > Hi All, > > Am trying ...

rolling weeks formula
Hi all, I have a sheet that has data in cells A1-A10 and will continue on through A11, A12 etc as the weeks go on and data is added. I want to make a sum of lets say A2-A6 this week, but next week A3-A7 and so on, like a rolling week data. Is there a way of doing this simple sum without having to SUM(A2-A6) and editing it the following week to SUM(A3-A7)? Any help is appreciated Andrew Hi Andrew One way Using cell B1 to hold the starting week number =SUM(INDEX(A:A,B1):INDEX(A:A,B1+3)) Just alter the value in B1 to give a different range -- Regards Roger Govier "@Homeonthecou...

characters in 1 col, 1 per row
Hi all, I want to transform the text of a worddocument to Excel in a special way. In my case text is just a number of words in a number of lines (nothing special about headers, footers etc.), so something comparable with this post. I want in Excel the first charachter of the text in A1, the second in A2 and so on. This email document would result in: H i a l l , I w a n t etc. I can't figure out how to do this, your help wil be appreciated. Jack Sons The Netherlands ...

Formula
Have a simple idea that is giving me fits. Have a worksheet with numberic data and labels in rows and columns. Below each column I use a simple SUM() function which works fine, until I disturb a row of data. By disturb, what I mean is that if I move the top row of data down to the bottom of the array, highlight the entire data area, move that area up one row, I lose the correct references in the SUM() function. I'm just moving the data area, not the row containing my SUM() functions - that stays put. IOW, the SUM() function is in row 12, the data area extends from row 1 through ...

Automatic Insert Rows VBA
A Very Good Evening All, I have a macro that runs: advanced filter/copy to another place/unique, between two worksheets (Column A in each worksheet being the identfier; worksheet1 is source data,with duplicates of identifiers; worksheet2 is formula driven from the sum of the unique identifiers in Worksheet1) However...I would like the macro to automatically extend the rows in worksheet2, to accommodate the ever increasing rows of worksheet1 Here is my specific code worksheet1 = "Invoice Record" worksheet2 = "General Report" Sub CommandButton1_Click() ' A...

Need Formula Help....
Hi: Is there a formula string for something like this.....?: I want a number in a cell when a specific word is typed in another cell, for example, if I wanted the number 50 to appear in C2 if I type the word, say, Airplane, in cell C1? Thanks, pinger You need to define what you want when cell C1 does not contain the word airplane. Enter this formula in C2: =IF(C1="airplane",50,"") If C1 does not contain airplane the formula will leave the cell blank. -- Biff Microsoft Excel MVP "pinger" <pinger@ec.rr.com> wrote in message news:47cf6287\$0...

Formula Paste Button
Excel provides menu bar buttons for "format" and "value" pastes, but not for "formula" pastes. I've tried creating a macro that I could then create a menu bar button for, but it doesn't work (VBA says the "selection" is bad.) Does anyone know how to create a simple "formula paste" button for the menu bar? I haven't seen any threads on this. Thanks, -- alandry753 ------------------------------------------------------------------------ alandry753's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26700 V...

Formula not copying over as before
I track sales for 97 people using seperate worksheets for each one and one total page. Everytime I would add a worksheet, I would go to the total page, copy and paste the forumula to the next cell and everything worked great. The total was pulled from lets say the 95th worksheet and put on cover sheet of the corresponding person. I've tried manually typing in the formula and it still doesn't work. Any ideas? "doesn't work" means what? No results? Results don't change? Have you looked at your Calculation Options? Are they set to "Auto"? Formula onl...

interest/inflation formula help
Could someone please do this formula for me. �300,000 to work out its worth each year (including interest) Then deduct cost of living % (inflation) each year. So �300,000 (at 6% interest) = �318000 deduct (inflation at say 3%)= �308460 (this would be its net worth say after one year I would use rows A1 to J10 for the actual amounts and would use 2 cells in row 2 for the interest rate and for the inflation figure, this way I could change the rates without altering the formula. I hope this makes sense. thanks There are a heap of financial functions built into excel. Look up PV ...

Some macro help
Lets say I have selected the arbitray range of j31:ax44. I want to run some macro code that will hide every column in that selection where the entire selected column range has nothing in it (blank). Data validation may be assigned to the cells, but if nothing has been selected from the drop down list, then those cells are technically blank. There may be data entered elsewhere in the column, but it is outside of the selected range and thus shouldn't be considered for being hidden or not. Can anyone help me with some code to perform this action? TIA, AlanN How about: Option Explicit Sub...

I need a formula to mark up a product by 200%
Hello, i am stupid when it comes to this stuff and frustrate easily!! If their is anyone that can help me i would greatly appreciate it. I have a product and it cost me \$4.50 to produce. I would like to mark it up 200%. What would the function be?? Thanks On Thu, 4 Mar 2010 18:29:01 -0800, StitchWitch <StitchWitch@discussions.microsoft.com> wrote: >Hello, i am stupid when it comes to this stuff and frustrate easily!! If >their is anyone that can help me i would greatly appreciate it. I have a >product and it cost me \$4.50 to produce. I would like to mark ...

conditional formatting: separating rows with differing values
Hi, Need help with conditional fomatting. What I need: I got a worksheet with 950 rows and 5 columns. The first row contain the headers. Columns A, B, D and E contains unique values. But the column C contains text values which repeats sequentially for 6-7 rows and changes thereafter. Now I want to put up a line (using border) through the whole row dividing this transition row. My Idea: Compare C2 with C3, if equal do nothing. Then compare C3 with C4 and so on till 2 consequtive values differ. Now divide the 2 rows using the border formatting option in conditional formatting dialog box. So...

"A Formula or sheet..."
Windows Vista Ultimate - SP1 RC1 Office 2007 Professional When I try to copy a worksheet I get the following: "A formula or sheet you want to move or copy contains the name '_XXXX', which already exists on the destination worksheet. Do you want to use this version of the name?" This messages appears many time before the sheet is finally copied. I have been clicking the yes button and the task is completed. Is there a work-around, or add in, that allows me to go in and delete all these names so I can just copy the sheet w/o all the extra effort. It seems to me I ran i...

Turn on Automatic Formula Fill in Table
I turned off the 'automatically fill th column with this formula' option in Excell 2007 but I can't see how to turn it on again. Can anyone point me in teh right direction? TIA Excel 2007 Excel Options > Proofing > AutoCorrect Options > Autoformat As You Type > Fill formulas in tables to create calculated columns ...

Easy formula question
=IF(D4>D5:D6:D7:D8:D9:D10:D11:D12:D13:D14:D15:D16,"Leader","---") :confused: I am trying to get a cell to say the word Leader if cell D4 is greate than the other cells in the list or if D4 is not greater than the cell in the list to say --- but obviously I have the formula above wron because it is not working. Help ASAP please!!!! Thanks in advance for any help with this, Mik -- mikeee ----------------------------------------------------------------------- mikeeee's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1461 View this thread: htt...

make dropdown list greater than 8 rows
Hello How can I make my dropdown list 12 rows instead of only 8? thanks for any help For a Forms Combo Box right click and choose Format Control-Control tab For an ActiveX ComboBox right click and choose Properties and adjust ListRows. -- Regards Dave Hawley www.ozgrid.com "SteveZmyname" <SteveZmyname@discussions.microsoft.com> wrote in message news:105058EA-1275-417C-B4BC-4023DFDAC16C@microsoft.com... > Hello > How can I make my dropdown list 12 rows instead of only 8? > thanks for any help I can change the drop down lines to 12 in the F...

Delete color row
Hi, After I compare two worksheets (VB as below) and highlight the same value with colour, now I want to delete the same value row. The comparison VB that I have worked: Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=COUNTIF(New,H5)=1" Selection.FormatConditions(1).Interior.ColorIndex = 6 How do I write VB to delete color row? Raymond ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ You can't programmatically check for a color produc...

If statements within Array formulas
I'm putting together a crosstab report using an array formula, th formula is as follows {=SUM(IF(\$A3&D\$2&CC_Choice=HYP&date&CC,Amount))} This basically produces a cost centre report for the Cost Centre Choic by account code by month. I now wish to add an extra field of selection, by Business Unit, if BU is selected a cost centre would not be selected so the CC-Choic field will be blank, and vice versa. Is it possible to put an If statement into the above formula t determine if the report is by CC or BU. My best attempt involves having a IF statement that checks i...