#### Another Math calculation

```=(A1+B1+C1+D1)/4

That is an average. So far, so good.  But what happens if I do not have one
of the values and want the average of the ones that I have?

Something like

= { if A1 A1+ if B1 B1 + if C1 C1 + if D1 D1}/ ???? divided by what?

JB

```
 0
open (40)
5/12/2009 7:21:40 PM
excel 39879 articles. 2 followers.

5 Replies
443 Views

Similar Articles

[PageSpeed] 8

```AVERAGE(A1:D1)

Regards,
Bernd
```
 0
bplumhoff1 (208)
5/12/2009 7:27:06 PM
```=AVERAGE(A1:A4)

Empty cells will be ignored and Excel averages just the numbers that are
present.

Gord Dibben  MS Excel MVP

On Tue, 12 May 2009 20:21:40 +0100, "JB" <open@closed.com> wrote:

>=(A1+B1+C1+D1)/4
>
>That is an average. So far, so good.  But what happens if I do not have one
>of the values and want the average of the ones that I have?
>
>Something like
>
>= { if A1 A1+ if B1 B1 + if C1 C1 + if D1 D1}/ ???? divided by what?
>
>JB
>

```
 0
Gord
5/12/2009 9:29:41 PM
```Great. Thanks

But, now another problem  I have two tables.  The second converts readings
from the first x/18.  However, some value in the first table are
missing(empty cells), and the second table assumes that it must divide 0/18
and inserts '0' where it should be empty.  How can I get the second table to
ignore epty cells in the first and so not convert?

Thank you

JB

"Gord Dibben" <gorddibbATshawDOTca> escreveu na mensagem
news:efqj05dbdkmbrslcelm35o66gvmce1rg6s@4ax.com...
> =AVERAGE(A1:A4)
>
> Empty cells will be ignored and Excel averages just the numbers that are
> present.
>
>
> Gord Dibben  MS Excel MVP
>
> On Tue, 12 May 2009 20:21:40 +0100, "JB" <open@closed.com> wrote:
>
>>=(A1+B1+C1+D1)/4
>>
>>That is an average. So far, so good.  But what happens if I do not have
>>one
>>of the values and want the average of the ones that I have?
>>
>>Something like
>>
>>= { if A1 A1+ if B1 B1 + if C1 C1 + if D1 D1}/ ???? divided by what?
>>
>>JB
>>
>

```
 0
open (40)
5/18/2009 10:21:34 PM
```Generally to prevent zeros from interfering you can trap.

=IF(A1/B1=0,"",A1/B1)

Or with your example data   =IF(0/18=0,"",0/18)

AVERAGE function ignores the  ""  that is returned if result is zero.

Gord

On Mon, 18 May 2009 23:21:34 +0100, "JB" <open@closed.com> wrote:

>Great. Thanks
>
>But, now another problem  I have two tables.  The second converts readings
>from the first x/18.  However, some value in the first table are
>missing(empty cells), and the second table assumes that it must divide 0/18
>and inserts '0' where it should be empty.  How can I get the second table to
>ignore epty cells in the first and so not convert?
>
>Thank you
>
>JB
>
>
>
>"Gord Dibben" <gorddibbATshawDOTca> escreveu na mensagem
>news:efqj05dbdkmbrslcelm35o66gvmce1rg6s@4ax.com...
>> =AVERAGE(A1:A4)
>>
>> Empty cells will be ignored and Excel averages just the numbers that are
>> present.
>>
>>
>> Gord Dibben  MS Excel MVP
>>
>> On Tue, 12 May 2009 20:21:40 +0100, "JB" <open@closed.com> wrote:
>>
>>>=(A1+B1+C1+D1)/4
>>>
>>>That is an average. So far, so good.  But what happens if I do not have
>>>one
>>>of the values and want the average of the ones that I have?
>>>
>>>Something like
>>>
>>>= { if A1 A1+ if B1 B1 + if C1 C1 + if D1 D1}/ ???? divided by what?
>>>
>>>JB
>>>
>>
>

```
 0
Gord
5/18/2009 10:45:44 PM
```Thanks!

JB

"Gord Dibben" <gorddibbATshawDOTca> escreveu na mensagem
> Generally to prevent zeros from interfering you can trap.
>
> =IF(A1/B1=0,"",A1/B1)
>
> Or with your example data   =IF(0/18=0,"",0/18)
>
> AVERAGE function ignores the  ""  that is returned if result is zero.
>
>
> Gord
>
> On Mon, 18 May 2009 23:21:34 +0100, "JB" <open@closed.com> wrote:
>
>>Great. Thanks
>>
>>But, now another problem  I have two tables.  The second converts readings
>>from the first x/18.  However, some value in the first table are
>>missing(empty cells), and the second table assumes that it must divide
>>0/18
>>and inserts '0' where it should be empty.  How can I get the second table
>>to
>>ignore epty cells in the first and so not convert?
>>
>>Thank you
>>
>>JB
>>
>>
>>
>>"Gord Dibben" <gorddibbATshawDOTca> escreveu na mensagem
>>news:efqj05dbdkmbrslcelm35o66gvmce1rg6s@4ax.com...
>>> =AVERAGE(A1:A4)
>>>
>>> Empty cells will be ignored and Excel averages just the numbers that are
>>> present.
>>>
>>>
>>> Gord Dibben  MS Excel MVP
>>>
>>> On Tue, 12 May 2009 20:21:40 +0100, "JB" <open@closed.com> wrote:
>>>
>>>>=(A1+B1+C1+D1)/4
>>>>
>>>>That is an average. So far, so good.  But what happens if I do not have
>>>>one
>>>>of the values and want the average of the ones that I have?
>>>>
>>>>Something like
>>>>
>>>>= { if A1 A1+ if B1 B1 + if C1 C1 + if D1 D1}/ ???? divided by what?
>>>>
>>>>JB
>>>>
>>>
>>
>

```
 0
open (40)
5/19/2009 11:48:57 AM

Similar Artilces:

matching a column of numbers to another in another spreadsheet
I have a spreadsheet of shortpayment amounts and their invoice numbers on one spreadsheet with a column of credit amounts with their invoice numbers on another spreadsheet. I need to match the credits written with the shortpayments. kinda like this spreadsheet 1 spreadsheet 2 inv # amount date Credit # Amount Date 122334 15.00 11-25-09 675555 15.00 12-10-09 223345 22.00 10-20-09 754444 22.00 111-25-09 naturally...

Summing Percent of Totals Calculation
Hi. I'm apportioning a City's TotalPremium to their Departments based on each Department's Payroll. I have a main form containing the Total Premium to be apportioned. I have a subform based on a query generating each Department's Payroll. So, in my Subform's detail section I'm showing: [Department] [Payroll] [txtPercentShareOfPayroll] ( =[Payroll] / Sum([Payroll] ) [txtDeptPremium] ( =[TotalPremium] from MainForm * [txtDeptPremium] ) The above works. I run into problems in the Subforms footer section where I want totals.I can only get a total payroll: [txtPayrollT...

Recommend Calculator program for programmer?
There are a zillion calculator programs around. Can anyone recommend one suitable for a programmer? (I figure there must be a lot of excellent programmers on this newsgroup. A good place to ask such a question.) Have you tried this one: http://www.softpedia.com/get/Science-CAD/iota-calc.shtml Tom "David Deley" <deleyd@gte.net> wrote in message news:CWaSi.12293\$1_2.1342@newsfe12.phx... > There are a zillion calculator programs around. Can anyone recommend one > suitable for a programmer? > > > (I figure there must be a lot of excellent programmers on thi...

Acessing function with a DLL which has be loaded from another dll
I have A.dll and B.dll. Assume that A.dll has following functions: A1() A2() Assume that B.dll has following functions: B1() B2() I am loading B.dll from function A1() in A.dll.After Loading B.dll i make a call to B1().After executing the call the function returns to beginning of A2.dll. why is this so? Define "begining". The word tends to make no sense in this context. What you seem to be saying is that you have void A1() { HANDLE h = ::LoadLibrary(_T("B.DLL")); ...test for h==NULL, deal with error typedef void (*B1)() B1Proc; B1Proc B1; ...

Grabbing a number from another cell on another worksheet
Hello, I have a spreadsheet with a number of different worksheets. All I want to do is if I enter a number into a cell on the 2nd worksheet for example, I want it to automatically show in a specific cell on the first worksheet. So a simple copy and paste but for excel to automatically do it for me! it seems like something so easy that I just cant figure out how to do. Try this... Do this once. Select the cell on the 2nd sheet Right click>Copy Navigate to the other sheet and select the cell where you want the value to appear. Right click>Paste Special>Paste Lin...

Creating column content based on another column's keyword(s) #2
Max, That scriptlet was very useful, and easily tweakable to work in myria applications. Thx! -K -- KHashmi31 ----------------------------------------------------------------------- KHashmi316's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1043 View this thread: http://www.excelforum.com/showthread.php?threadid=27348 Glad to hear that ! Thanks for posting back -- Rgds Max xl 97 --- Please respond in thread xdemechanik <at>yahoo<dot>com ---- "KHashmi316" <KHashmi316.1evevz@excelforum-nospam.com> wrote in message news:KHashmi316.1e...

Populate data in cell by looking at another cells data
Hi Everyone, Hope I find you well. I'm not even sure how to go about this, so I hope that someone can shed some light. I have a serial number in one cell eg '80199DD270238' where 'DD' is code for another value, in this case 'DD' = 'BLUE'. How can I automatically populate a cell with the value 'BLUE' by looking at the serial number. Many thanks for any help you can provide. Best Regards Gazza Hi if the characters are alsways at the same place use =IF(MID(A1,6,2)="DD","Blue","other color") if they could be at ...

Sum dependant on '1' value in another column
Hi, Let's say I have a '1' or nothing in column 'L' and a time in seconds in column 'B'. The '1' means the time is a legitimate one. Can I do a sum of all the 'legitimate' times in column 'B' based on whether there is a corresponding '1' in column 'L'? Is there a formula that will allow me to do that? Sorry for the ignorance of the question but it is late and I am struggling to make this spreadsheet work for me. Thanks in advance, Dave Hi DL, Look at the SUMIF function in Excel help. Try: =SUMIF(L1:L10...

is it possible to execute write to the fields in another .xsl form a macro in another .xsl? e.g. some way to load another .xsl into an .xsl macro and write to its data? #2
is it possible to execute write to the fields in another .xsl form a macro in another .xsl? e.g. some way to load another .xsl into an .xsl macro and write to its data? ...

Copy Calculated Field in Report Writer
Is there a way to copy a calculated field in report writer from one report to another? Thanks, Jocelyn You can copy a calculated field from one report to another by creating a package file using customization maintenance and then editing the file using notepad. You need to change the table names if necessary and be very careful. Which field from which report did you want to copy and to which report? I've done the address block and can post it to my blog (I should do that today anyway). You can copy the entire report layout using the same method. Like for instance the SOP Blan...

I have a workbook created that, every time I open it, I get the question: Do you want to update the automatic link to data in another workbook? I didn't consciously set up a link and I don't want to get this message. How do I find this link and break it so I will not continue to get this message? Any help is appreciated. Close any other Excel workbooks you have open and do a "Find" for :\ (colon backslash). If you have several worksheets in your workbook, ask Excel to search the entire workbook by selecting find within "Workbook" instead of "Sheet&quo...

Copying from one ListControl to Another...
Hello everyone, I have been studying how List Controls work, and now I have two List Controls, A and B. Both of the list controls are in Report_View and they both have the same # of columns. What I would like to do is copy each entry of A into B. I use CListCtrl::GetItem(..) to get the item, and I use CListCtrl::InsertItem(..) to insert the item into B. However, this is not working. It is only inserting the first subitem. Is it possible to copy the entire item i.e, subitems 0-2 from CListCtrl A into CListCtrl B? Thanks ...

Another question about IF(COUNTIF) checks in Excel
<Tiff1618@discussions.microsoft.com> wrote in message > news:219EB429-F90A-406E-A208-5C787ED70467@microsoft.com... > > Hey again, > > > > Is there a formula I can use to figure out if there is one specific phrase > > in a selection? > > > > I'm updating the attendance prgram at the school that I work at. Every > > student has their own attendance sheet in Excel, and each sheet sort of > > looks > > like this: > > > > |Monday| > > Period 1:| A | (A=Absent; S=Seat time) > > Per...

Another If/Ten
HI all. Trying to use IF/THEN to look at Column A on one sheet, if any cell in that column equals today() would like cells from that row to fill in another sheet. Is this possible? Any ideas on how to make that happen?? Thanks. Flue. Maybe you can use format|Conditional formatting. Select the range to color (I used A2:X999). With A2 the activecell, I did: format|conditional formatting formula is: =(\$a2=today()) and give it a nice format Brian Bennett wrote: > > HI all. > > Trying to use IF/THEN to look at Column A on one sheet, if any cell in that > column equal...

What's the difference between an Irish wedding and an Irish funeral? One less drunk at the funeral. The mayor of New Orleans was asked about his position on Roe vs. Wade. He said he didn't really care how people got out of the city. -- * "AB" <AB@discussions.microsoft.com> wrote in message news:2B46FD0D-3AA8-4D81-B90A-3FB512EE1F8F@microsoft.com... What's the difference between an Irish wedding and an Irish funeral? One less drunk at the funeral. By looking at a picture of a person, you have to decide if he is a computer geek or a serial k...

maths expression antilog10
Excel 2000 has log10 but I can't find antilog10 anywhere - can anyone help? Check this out: http://www.swrp.org/Student_Presentations/Preparation/Poster/Statistics/log_mean.html "andy" wrote: > Excel 2000 has log10 but I can't find antilog10 anywhere - can anyone help? > Check this out: http://www.swrp.org/Student_Presentations/Preparation/Poster/Statistics/log_mean.html "andy" wrote: > Excel 2000 has log10 but I can't find antilog10 anywhere - can anyone help? > Check this out: http://www.swrp.org/Student_Presentations/Preparatio...

How can I open a file saved in another version of Publisher?
I have Publisher 2000 and I am trying to open a .pub file but the message says it cannot open because the file is save in a different version of Publisher. Is there a way to open the file in Publisher 2000? Any help would be greatly appreciated. YvetteR wrote: > I have Publisher 2000 and I am trying to open a .pub file but the > message says it cannot open because the file is save in a different > version of Publisher. Is there a way to open the file in Publisher > 2000? Any help would be greatly appreciated. More than likely, this file was created in Publisher 2002 or 2003. Y...

Prevent formula calculation from overriding validation
I have a list of 4 numbers with a sum at the bottom. One of the 4 numbers is a calculation, the others are direct entry. The calculated number cannot be less than zero. How do I fix this so it doesn't allow this when the number is calculated? >The calculated number cannot be less than zero. You should have posted the formula. Try changing your current formula to something like this: =MAX(0,your_current_formula_here) -- Biff Microsoft Excel MVP "CindyMc" <CindyMc@discussions.microsoft.com> wrote in message news:6211BA9C-94BF-42B1-8128-4B9C...

Calculating a Formula only is 2 cells have values
I currently have a formuala that subtracts 2 cells which both contain times. (i.e. 23:30 and 04:30 would give an answer of 5). The formula looks like this: =ROUND((C2-B2+(B2>C2))*24,1) --> it rounds the answer to 1 decimal place. But, I do not want to make this calculation occur until both times (B2 & C2) have a time. If 1 or both are empty, i want a message to say that they need 2 times before it can be calculated. Somewhat of an IF function I guess. I also have another formula that uses the previous calculation. So if that 1st calculation does not occur due to empty values ...

Referencing another field
Hello, I have a Combobox field called "WorkerID" on a form. This field is not tied to the Forms data source. It is pretty much floating, no control source, but it does have a Row Source Type, Select Query that pulls Worker IDs form a table called "tbl_WorkerIDs". This table has both WorkerIDs and WorkerNames. I want to add another floating combobox field to the form to display the WorkerNames. So if I change the combobox WorkerID I need the WorkerName to change automatically. How do I configure the WorkerName combobox to look at the WorkerID field and upda...