 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / October 2004#div/0!
I am trying to get an average score for each month of the year and I have a spreedsheet where the total is the following formula =AVERAGE(H22:H33) But until each month ot the year is completed it only shows #div/0!. Is there a way to force it to show the average if you only have data in H22 and H23 etc? Jim Hi =IF(COUNT(H22:H33),AVERAGE(H22:H33),"")  Signature-- Regards Frank Kabel Frankfurt, Germany > I am trying to get an average score for each month of the year and I have a > spreedsheet where the total is the following formula =AVERAGE(H22:H33) But [quoted text clipped - 3 lines] > > Jim How about: =SUM(H22:H33)/MAX(1,COUNT(H22:H33)) This will still give you a #DIV/0! error if all entries in H22:H3 consists of real 0's. Apply, if you so wish, the following custom format to the formul cell: 0;0;; big jim Wrote: > I am trying to get an average score for each month of the year and > have a [quoted text clipped - 7 lines] > > Ji Neither formula suggestion worked it still shows #DIV/0! but at this point the data in h32 and h33 which is month 11 and 12 are also at zero and I still want to see what the average was in the total column for the first 10 months. The cells are formatted to show % and I notice that if I change it the format to number the #DIV/0! goes away and I can see the average but am I not going to be able to show the results in %? Jim > How about: > [quoted text clipped - 20 lines] >> >> Jim Exactly what formula do you have in H22:H33? SignatureRegards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- Neither formula suggestion worked it still shows #DIV/0! but at this point the data in h32 and h33 which is month 11 and 12 are also at zero and I still want to see what the average was in the total column for the first 10 months. The cells are formatted to show % and I notice that if I change it the format to number the #DIV/0! goes away and I can see the average but am I not going to be able to show the results in %? Jim "Aladin Akyurek" <Aladin.Akyurek.1eljby@excelforum-nospam.com> wrote in message news:Aladin.Akyurek.1eljby@excelforum-nospam.com... > How about: > [quoted text clipped - 20 lines] >> >> Jim I have the formula =average(h22:h33) Jim > Exactly what formula do you have in H22:H33? > Neither formula suggestion worked it still shows #DIV/0! but at this [quoted text clipped - 37 lines] >>> >>> Jim Hi but then either Aladin's or my formula should have worked. Or do you also have formulas in the cells H22 to H33 (which may for itself result in a #DIV/0 error? Signature-- Regards Frank Kabel Frankfurt, Germany > I have the formula =average(h22:h33) > [quoted text clipped - 57 lines] > >> -------------------------------------------------------------------- ---- > >> Aladin Akyurek's Profile: > >> http://www.excelforum.com/member.php?action=getinfo&userid=4165 > >> View this thread: > >> http://www.excelforum.com/showthread.php?threadid=271834 Yes I do have formulas in H22 thru H33 that are pulling data from somewhere else. Jim S > Hi > but then either Aladin's or my formula should have worked. Or do you [quoted text clipped - 83 lines] >> >> View this thread: >> >> http://www.excelforum.com/showthread.php?threadid=271834 Hi then PLEASE post these formulas and also explain if these formulas do return errors on its own? Signature-- Regards Frank Kabel Frankfurt, Germany > Yes I do have formulas in H22 thru H33 that are pulling data from somewhere > else. [quoted text clipped - 88 lines] > >> >> View this thread: > >> >> http://www.excelforum.com/showthread.php?threadid=271834 The question was "What formula is in H22:H33"! I'm sure you don't have "=average(h22:h33)" *IN* H22 !!! SignatureRegards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- I have the formula =average(h22:h33) Jim > Exactly what formula do you have in H22:H33? > Neither formula suggestion worked it still shows #DIV/0! but at this [quoted text clipped - 37 lines] >>> >>> Jim In H22 I have the formula =SUM(ESP2!F22+LV!F22+LA!F22+RATON!F22+SF1!F22+SF2!F22+SF3!F22+TAOS!F22)/8 which is pulling info from several different pages of a workbook that are also in a percentage format. Jim > The question was "What formula is in H22:H33"! > [quoted text clipped - 45 lines] >>>> >>>> Jim Hi do these formulas return an error? also note: no need for the SUM formula in your case. If these worksheets are one after the other try the formula =SUM(ESP2:TAOS!F22)/8 this should prevent a #VALUE error if these cells contain text values Signature-- Regards Frank Kabel Frankfurt, Germany > In H22 I have the formula =SUM(ESP2!F22+LV!F22+LA!F22+RATON!F22+SF1!F22+SF2!F22+SF3!F22+TAOS!F22) /8 > which is pulling info from several different pages of a workbook that are > also in a percentage format. [quoted text clipped - 77 lines] > >>> ------------------------------------------------------------------- ----- > >>> Aladin Akyurek's Profile: > >>> http://www.excelforum.com/member.php?action=getinfo&userid=4165 > >>> View this thread: > >>> http://www.excelforum.com/showthread.php?threadid=271834 Assuming that you will *not* have negatives, try this formula: =SUMIF(H22:H33,">0")/COUNTIF(H22:H33,">0") As far as your formula to sum across your sheets, have a look at this old post to enable you to make a shorter formula, similar to Frank's suggestion: http://tinyurl.com/6gujb SignatureHTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== In H22 I have the formula =SUM(ESP2!F22+LV!F22+LA!F22+RATON!F22+SF1!F22+SF2!F22+SF3!F22+TAOS!F22)/8 which is pulling info from several different pages of a workbook that are also in a percentage format. Jim > The question was "What formula is in H22:H33"! > [quoted text clipped - 32 lines] >>>> >>>> Jim On the assumption that the formulas in the referenced range are returning error values, try =IF(COUNT(H22:H33),AVERAGE(IF(ISNUMBER(H22:H33),H22:H33)),"") array entered (Ctrl+Shift+Enter) Jerry > Neither formula suggestion worked it still shows #DIV/0! but at this point > the data in h32 and h33 which is month 11 and 12 are also at zero and I [quoted text clipped - 30 lines] >>> >>>Jim |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.