Note: groups.io will be down for maintenance on Wednesday, October 5th, starting at 9AM Pacific Time (4PM Wednesday October 5, 2022 UTC), for approximately one hour.
Date
1  9 of 9
excel questions
stintonjoe@...
hi all
ok i have jaws 17 and windows 7 and think all is uptodate
I am trying to make a excel sheet, and what I need to do is first
in the row I need a average from cell b2 to cell l 2 , and again in cell
b3 to l 3 now i need to do this in every row.
next i need a avridge in the collom from b2 to b 100 then
again in c 2 to c 100 and d 2 to d 100 and so on.
but the problem is I cant work out how to get a average formula in the cell
I need.
well hope you can work out what im trying to get at and you havnt
just read this and thought darn my head now hurts lets go for a pint
!!!
well if any one can help me with what I need to do I would be very
greatfull thanks in advance Joe


Chris Chaffin
Now from what I read, you are wanting 3 averages; cells b2 to l2, cells b3 to l3, and cells b2 to b100. And you want this for each row and column respectively. You can use the average function to accomplish this: =average(b2:l2) =average(b3:l3) =average(b2:b100) If I misunderstood you, please let me know. Chris
On Nov 18, 2016, at 4:27 AM, stintonjoe@... wrote:


Ann Byrne
I you want to use the formulas dialog, press ALT+m, then f. Tab to the categories and choose all, then tab once more and find average. You can fill in range after range after range, tabbing from one to the next. Tabbing will *never get you to the okay button, because it just allows for another range. Pressing enter might do the okay, or shift+tab till you get back to okay.
toggle quoted messageShow quoted text
At 03:27 AM 11/18/2016, you wrote:
hi all


stintonjoe@...
ok I'm now trying to get excel to tell me the AVERAGE of cells B102 D102 F102 G102 H102 I102 J102 K102 L102 M102
the problem being at the moment not all the cells have a number in them and all it says is div 0,but as time goes on they will all have a number in them. the formula i have tried is =AVERAGE(B102 D102 F102 G102 H102 I102 J102 K102 L102 M102) but with this fromaler as far as I can tell the advridge of these numbers all need a number in for it to show anything. for example if b 102 and f 102 have numbers in I still get nothing in the average cell. well I hope you can make some sort of sense out of my ramblings. cheers Joe cheers Joe Original Message  From: Ann Byrne Sent: Friday, November 18, 2016 1:19 PM To: main@jfw.groups.io Subject: Re: excel questions I you want to use the formulas dialog, press ALT+m, then f. Tab to the categories and choose all, then tab once more and find average. You can fill in range after range after range, tabbing from one to the next. Tabbing will *never get you to the okay button, because it just allows for another range. Pressing enter might do the okay, or shift+tab till you get back to okay. At 03:27 AM 11/18/2016, you wrote: hi all


Dave...
Joe,
toggle quoted messageShow quoted text
There may be another function for averaging that avoids any zero values. Otherwise, create a formula that first finds all nonzero values, and then adds them up and divide by the total count of nonzero values. That takes a few functions, but workable. I recall doing something like this several years ago. First try to find a builtin function that averages all nonzero values. Also, check the Excel help function to look for any websourced solutions to your question. Farfar Dave Oregonian, woodworker, Engineer, Musician, and Pioneer
 Original Message 
From: <stintonjoe@...> To: <main@jfw.groups.io> Sent: Friday, November 18, 2016 12:40 Subject: Re: excel questions ok I'm now trying to get excel to tell me the AVERAGE of cells B102 D102 F102 G102 H102 I102 J102 K102 L102 M102 the problem being at the moment not all the cells have a number in them and all it says is div 0,but as time goes on they will all have a number in them. the formula i have tried is =AVERAGE(B102 D102 F102 G102 H102 I102 J102 K102 L102 M102) but with this fromaler as far as I can tell the advridge of these numbers all need a number in for it to show anything. for example if b 102 and f 102 have numbers in I still get nothing in the average cell. well I hope you can make some sort of sense out of my ramblings. cheers Joe cheers Joe Original Message  From: Ann Byrne Sent: Friday, November 18, 2016 1:19 PM To: main@jfw.groups.io Subject: Re: excel questions I you want to use the formulas dialog, press ALT+m, then f. Tab to the categories and choose all, then tab once more and find average. You can fill in range after range after range, tabbing from one to the next. Tabbing will *never get you to the okay button, because it just allows for another range. Pressing enter might do the okay, or shift+tab till you get back to okay. At 03:27 AM 11/18/2016, you wrote: hi all


Nicole Massey <nyyki@...>
Remember that for a range of cells you can reflect all of them with a colon,
toggle quoted messageShow quoted text
so you could do something like =average(D102:L102) and it's give you an average for the entire range. This is helpful as there is a limit as to how many operators you can put in a formula separated by commas.
Original Message
From: main@jfw.groups.io [mailto:main@jfw.groups.io] On Behalf Of stintonjoe@... Sent: Friday, November 18, 2016 2:41 PM To: main@jfw.groups.io Subject: Re: excel questions ok I'm now trying to get excel to tell me the AVERAGE of cells B102 D102 F102 G102 H102 I102 J102 K102 L102 M102 the problem being at the moment not all the cells have a number in them and all it says is div 0,but as time goes on they will all have a number in them. the formula i have tried is =AVERAGE(B102 D102 F102 G102 H102 I102 J102 K102 L102 M102) but with this fromaler as far as I can tell the advridge of these numbers all need a number in for it to show anything. for example if b 102 and f 102 have numbers in I still get nothing in the average cell. well I hope you can make some sort of sense out of my ramblings. cheers Joe cheers Joe Original Message  From: Ann Byrne Sent: Friday, November 18, 2016 1:19 PM To: main@jfw.groups.io Subject: Re: excel questions I you want to use the formulas dialog, press ALT+m, then f. Tab to the categories and choose all, then tab once more and find average. You can fill in range after range after range, tabbing from one to the next. Tabbing will *never get you to the okay button, because it just allows for another range. Pressing enter might do the okay, or shift+tab till you get back to okay. At 03:27 AM 11/18/2016, you wrote: hi all


Chris Chaffin
Hello, For your situation, you could try using the sum function combined with the count function. Another words, you could get the sum of numbers in the range of cells, then use the count function to divide by the number of numbers you have. This would hopefully get rid of your error. In your example, try this formula: I noticed that you did not include c102 in your formula, so I left it out. =sum(b102, d102:m102)/counta(b102, d102:m102) Let me know if you have any questions. Chris
On Nov 18, 2016, at 3:40 PM, stintonjoe@... wrote:


Dale Alton <blinkydale@...>
That shouldn't matter. I tried it by putting in random numbers in column
toggle quoted messageShow quoted text
a1:a10. I put the formula =average(a1:a10) in cell a11 and it came up with an average. Not all cells had numbers in them. If you don't want the error showing up you can try =ifError(average(a2:a101),""). The formula will show a blanck cell if there is an error. As far as getting it to the other cells I would just do a copy paste. I hope this helps. Denver Dale
Original Message
From: main@jfw.groups.io [mailto:main@jfw.groups.io] On Behalf Of stintonjoe@... Sent: Friday, November 18, 2016 1:41 PM To: main@jfw.groups.io Subject: Re: excel questions ok I'm now trying to get excel to tell me the AVERAGE of cells B102 D102 F102 G102 H102 I102 J102 K102 L102 M102 the problem being at the moment not all the cells have a number in them and all it says is div 0,but as time goes on they will all have a number in them. the formula i have tried is =AVERAGE(B102 D102 F102 G102 H102 I102 J102 K102 L102 M102) but with this fromaler as far as I can tell the advridge of these numbers all need a number in for it to show anything. for example if b 102 and f 102 have numbers in I still get nothing in the average cell. well I hope you can make some sort of sense out of my ramblings. cheers Joe cheers Joe Original Message  From: Ann Byrne Sent: Friday, November 18, 2016 1:19 PM To: main@jfw.groups.io Subject: Re: excel questions I you want to use the formulas dialog, press ALT+m, then f. Tab to the categories and choose all, then tab once more and find average. You can fill in range after range after range, tabbing from one to the next. Tabbing will *never get you to the okay button, because it just allows for another range. Pressing enter might do the okay, or shift+tab till you get back to okay. At 03:27 AM 11/18/2016, you wrote: hi all


Lisle, Ted (CHFS DMS)
A straight range would also work, if there are any values in that range at all. That always worked for me when I was figuring grades.
toggle quoted messageShow quoted text
Ted
Original Message
From: main@jfw.groups.io [mailto:main@jfw.groups.io] On Behalf Of Dave Carlson Sent: Friday, November 18, 2016 3:47 PM To: main@jfw.groups.io Subject: Re: excel questions Joe, There may be another function for averaging that avoids any zero values. Otherwise, create a formula that first finds all nonzero values, and then adds them up and divide by the total count of nonzero values. That takes a few functions, but workable. I recall doing something like this several years ago. First try to find a builtin function that averages all nonzero values. Also, check the Excel help function to look for any websourced solutions to your question. Farfar Dave Oregonian, woodworker, Engineer, Musician, and Pioneer  Original Message  From: <stintonjoe@...> To: <main@jfw.groups.io> Sent: Friday, November 18, 2016 12:40 Subject: Re: excel questions ok I'm now trying to get excel to tell me the AVERAGE of cells B102 D102 F102 G102 H102 I102 J102 K102 L102 M102 the problem being at the moment not all the cells have a number in them and all it says is div 0,but as time goes on they will all have a number in them. the formula i have tried is =AVERAGE(B102 D102 F102 G102 H102 I102 J102 K102 L102 M102) but with this fromaler as far as I can tell the advridge of these numbers all need a number in for it to show anything. for example if b 102 and f 102 have numbers in I still get nothing in the average cell. well I hope you can make some sort of sense out of my ramblings. cheers Joe cheers Joe Original Message  From: Ann Byrne Sent: Friday, November 18, 2016 1:19 PM To: main@jfw.groups.io Subject: Re: excel questions I you want to use the formulas dialog, press ALT+m, then f. Tab to the categories and choose all, then tab once more and find average. You can fill in range after range after range, tabbing from one to the next. Tabbing will *never get you to the okay button, because it just allows for another range. Pressing enter might do the okay, or shift+tab till you get back to okay. At 03:27 AM 11/18/2016, you wrote: hi all

