Results 1 to 22 of 22
Thread: Average If â Excel 2003
Hybrid View
-
15th April 2011 01:28 #1Registered User
Join Date: Apr:2003
Location: Varna
Posts: 5,205
Average If â Excel 2003
Êîëåãè,
Êàê ìîãà äà ñìåòíà ñðåäíà ñòîéíîñò â Åêñåë 2003 ïðè ïîëîæåíèå, ÷å èìàì ïðàçíè êëåòêè, êîèòî íå èñêàì äà áúäàò âêëþ÷âàíå â ñìåòêàòà?
Èñêàì äà èñêàðàì averagå ( ñðåäíà ñòîéíîñò) íà öèôðèòå îò êëåòêà B4 do B16, êàòî êúì ìîìåíòà èìàì ïîïúëíåíè öèôðè åäèíñòâåíî â êëåòêè B4 do B9, à îñòàíàëèòå îò B10 do B16 ñà ïðàçíè.
Àêî ñëîæà ñàìî =AVERAGE(B4:B16), íå ìè èçêàðâà êîðåêòåí ðåçóëòàò, çàùîòî âìåñòî äà ñúáåðå ñàìî ïúëíèòå êëåòêè è äà ãè ðàçäåëè íà áðîÿ èì (êîåòî íà ìåí ìè òðÿáâà), òî ñúáèðà êëåòêèòå è ãè äåëè íà îáùèÿ áðîé (ïîïúëíåíè è ïðàçíè).
Îñâåí òîâà ïðàçíèòå êëåòêè, êîèòî ñà íàïðàâåíè ñ ôîðìóëêà äà âçåìàò ñòîéíîñò îò äðóã øèèò, íî òîâà ìèñëÿ ÷å, íå å îò çíà÷åíèå.
Ìèñëÿ, ÷å ñõâàíàõòå êàêâî ìè òðÿáâà.
Ïîìàãàéòå äà âèäèì äàëè ìîæå äà ñòàíå.
Èíà÷å çíàì, ÷å â Åõñåë 2007 ñà ñëîæèëè òàêàâà ôóíêöèÿ íà ãîòîâî, àìà íÿìà êàê äà ñìåíÿ âåðñèÿòà, ÷å òàçè å ëèöåíçèðàíà.
-
15th April 2011 01:38 #2
Ùî íå ïîëçâàø ôîðìóëà îò ñîðòà - =SUM(B4:B16)/COUNT(B4:B16)
Òàêà ùå äåëè ñóìàòà ñàìî íà áðîÿ íà ïîïúëíåíèòå êëåòêè.CPU:Ryzen 7 5800X3D + Arctic Liquid Freezer II 280; MB:Asus Crosshair VII; RAM:2x8GB GSKILL F4-3200C14D-16GTZRX; VGA:Saphire RX 5700XT Nitro+; HDD:Samsung 970 EVO Plus 1TB+860 EVO 500GB+860EVO 1TB; PSU:Seasonic X-760; CASE:FD Define R6 TG; DSPL:Samsung C32HG70; SPKR:Logitech Z906;
-
15th April 2011 02:45 #3Registered User
Join Date: Apr:2003
Location: Varna
Posts: 5,205
Ïàê êàòî èìà íóëè â êëåòêèòå (à òå ñå ïîÿâÿâàò, òúé êàòî êëåòêèòå îò êúäå ñå ÷åðïè èíôîðìàöèÿ â äðóãèòå øèèòîâå îùå ñà ïðàçíè) è òèÿ 0 ãè ñìÿòà è äåëè íà òÿõ, à àç íå èñêàì.
Ñÿêàø ñòàíà ñ ôîðìóëà: =SUM(B4:B16)/COUNTIF(B4:B16;">0")
Íî, ñåãà èìàì è äðóãî:
 åäíàòà êîëîíà ìè ñìÿòà ïðèìåðíî
=C10/B10 ,
îáà÷å òå íàëè ñà îùå ïðàçíè è âàäè
#DIV/0!
è êàòî ìó äàì äîëó äà âàäè Àâåðåéäæ
ñ
=SUM(D4
16)/COUNTIF(D4
16;">0")
è âàäè #DIV/0!
à èñêàì äà âàäè ñàìî àâåðåéäæ íà ïîïúëíåíèòå ñ ðåçóëòàòè êîëîíè.
-
15th April 2011 02:52 #4
Ñëîæè ìó COUNTIF, ùîì èìà íóëè. Ùå ñòàíå òàêà =SUM(B4:B16)/COUNTIF(B4:B16;"<>0")
Íÿìàì 2003 ïîä ðúêà, òà çàòîâà åêñïåðèìåíòèðàì ñ ôîðìóëèòå.
Ùå òðÿáâà äà òóðíåø â êëåòêèòå =IFERROR(òóê ôîðìóëàòà, êîÿòî òè äàâà ãðåøêà;0), çà äà ìîæå ïðè ãðåøêà äà òè ñëàãà íóëè â êëåòêèòå. Ïîñëå ñðåäíî àðèòìåòè÷íîòî ùå å ëåñíî ñ ãîðíàòà ôîðìóëà.Last edited by VoyagerX; 15th April 2011 at 03:12.
CPU:Ryzen 7 5800X3D + Arctic Liquid Freezer II 280; MB:Asus Crosshair VII; RAM:2x8GB GSKILL F4-3200C14D-16GTZRX; VGA:Saphire RX 5700XT Nitro+; HDD:Samsung 970 EVO Plus 1TB+860 EVO 500GB+860EVO 1TB; PSU:Seasonic X-760; CASE:FD Define R6 TG; DSPL:Samsung C32HG70; SPKR:Logitech Z906;
-
15th April 2011 08:32 #5Registered User
Join Date: Oct:2003
Location: Ñîôèÿ
Posts: 4,317
-
15th April 2011 09:11 #6CPU:Ryzen 7 5800X3D + Arctic Liquid Freezer II 280; MB:Asus Crosshair VII; RAM:2x8GB GSKILL F4-3200C14D-16GTZRX; VGA:Saphire RX 5700XT Nitro+; HDD:Samsung 970 EVO Plus 1TB+860 EVO 500GB+860EVO 1TB; PSU:Seasonic X-760; CASE:FD Define R6 TG; DSPL:Samsung C32HG70; SPKR:Logitech Z906;
-
15th April 2011 09:41 #7Registered User
Join Date: May:2005
Location: Ñîôèÿ
Posts: 933
=AVERAGEIF(A1:A10,">0",A1:A10) - òîâà ïðîïóñêà íóëèòå ïðè ìåí.
-
15th April 2011 09:52 #8CPU:Ryzen 7 5800X3D + Arctic Liquid Freezer II 280; MB:Asus Crosshair VII; RAM:2x8GB GSKILL F4-3200C14D-16GTZRX; VGA:Saphire RX 5700XT Nitro+; HDD:Samsung 970 EVO Plus 1TB+860 EVO 500GB+860EVO 1TB; PSU:Seasonic X-760; CASE:FD Define R6 TG; DSPL:Samsung C32HG70; SPKR:Logitech Z906;
-
15th April 2011 10:20 #9Registered User
Join Date: May:2005
Location: Ñîôèÿ
Posts: 933
Âÿðíî ÷å ÿ íÿìà. Èçâèíÿâàì ñå.
Ôîðìóëèòå êîéòî ñà ãðåøêè ñå ïðîìåíÿò íà =IF(ISERROR(C10/B10),0,C10/B10) - âðúùàò ñå íóëè.
Ñðåäíàòà ñòîéíîñò ñå ñìÿòà, òàêà ={AVERAGE(IF(B4:B16<>0, B4:B16,""))} - èìà {} êàâè÷êè
Åòî è òåñò:
http://img638.imageshack.us/i/unled1d.jpg/
http://img858.imageshack.us/i/unledta.jpg/Last edited by Miro85; 15th April 2011 at 10:57.
-
15th April 2011 11:47 #10CPU:Ryzen 7 5800X3D + Arctic Liquid Freezer II 280; MB:Asus Crosshair VII; RAM:2x8GB GSKILL F4-3200C14D-16GTZRX; VGA:Saphire RX 5700XT Nitro+; HDD:Samsung 970 EVO Plus 1TB+860 EVO 500GB+860EVO 1TB; PSU:Seasonic X-760; CASE:FD Define R6 TG; DSPL:Samsung C32HG70; SPKR:Logitech Z906;
-
15th April 2011 15:46 #11Registered User
Join Date: Aug:2009
Location: Ñîôèÿ
Posts: 265
-
15th April 2011 17:55 #12ASRock Z77 Extreme4 | Intel Core i5-3570K @ 4.4 + Noctua NH-C14 | 2x 4GB Geil Evo Leggera 2133 C10 | 2x Gigabyte GTX 760 OC | G.Skill Phoenix Pro 60GB + Samsung 840 250GB + Hitachi Deskstar 500GB | Seasonic X-660 | CM HAF-X
AOC d2757Ph | MS Arc kbd | Cyborg R.A.T. 9 | Logitech G930
-
16th April 2011 19:28 #13Registered User
Join Date: Aug:2009
Location: Ñîôèÿ
Posts: 265
-
18th April 2011 10:50 #14
Áëàãîäàðÿ, âãëåæäàì ñå â êîä, êîèòî íå ñà ïèñàíè îò ìåí, âñåêè äåí áëèçêèòå 10 ãîäèíè.
Ïúðâî, ISERR íå ðàáîòè ñ ðåéíäæîâå - çà íåãî âñåêè ðåéíäæ Å ãðåøêà.
Âòîðî, äîðè äà ñòàâàøå êàêòî ñè ãî ìèñëèø, è â ä4:ä16 íÿêúäå èìà ãðåøêà, íî íà äðóãèòå ìåñòà íå, evaluation-úò íà ôîðìóëàòà áè áèë òàêúâ:
Code:=AVERAGE(IF(IF(ISERR(D4:D16);0;D4:D16)>0;D4:D16;"")) =AVERAGE(IF(IF(TRUE;0;D4:D16)>0;D4:D16;"")) =AVERAGE(IF(0>0;D4:D16;"")) =AVERAGE("") #VALUE!ASRock Z77 Extreme4 | Intel Core i5-3570K @ 4.4 + Noctua NH-C14 | 2x 4GB Geil Evo Leggera 2133 C10 | 2x Gigabyte GTX 760 OC | G.Skill Phoenix Pro 60GB + Samsung 840 250GB + Hitachi Deskstar 500GB | Seasonic X-660 | CM HAF-X
AOC d2757Ph | MS Arc kbd | Cyborg R.A.T. 9 | Logitech G930
-
16th April 2011 10:09 #15CPU:Ryzen 7 5800X3D + Arctic Liquid Freezer II 280; MB:Asus Crosshair VII; RAM:2x8GB GSKILL F4-3200C14D-16GTZRX; VGA:Saphire RX 5700XT Nitro+; HDD:Samsung 970 EVO Plus 1TB+860 EVO 500GB+860EVO 1TB; PSU:Seasonic X-760; CASE:FD Define R6 TG; DSPL:Samsung C32HG70; SPKR:Logitech Z906;
-
5th May 2011 06:06 #16Registered User
Join Date: Apr:2003
Location: Varna
Posts: 5,205
10 ïúòè ñå ìú÷èõ ñ ctrl i enter, íî íèùî íå ñòàâà è íå ñå ïîÿâÿâàò {} , à àêî ñå îïèòàì ðú÷íî äà ãè âúâåäà ìè èçëàçà íÿêàêâà ãðåøêà.
Îò êúäå äðóãàäå ìîæå äà ñå ïðîìåíÿ òîâà äàëè äà å ìàòðè÷íà èëè äðóã âèä ôîðìóëà.
Íî ïúê è ìèñëÿ, ÷å ñòàâà ïî íà÷èíà íàïèñàí îò ìåí.
-
5th May 2011 08:35 #17Registered User
Join Date: Aug:2009
Location: Ñîôèÿ
Posts: 265
Ìîÿ å ãðåøêàòà, Ctrl-Shift-Enter áèëî. Èçîáùî ñúì ñïðÿë äà îáðúùàì âíèìàíèå, ÷å íàòèñêàì è Shift, íàëîæè ñå äà ñè ïîãëåäíà ïðúñòèòå. Òâîÿòà ôîðìóëà ñè ðàáîòè, ñòèãà äà íÿìàø ãðåøêè èëè îòðèöàòåëíè ÷èñëà â ðåçóëòàòèòå.
-
5th May 2011 09:51 #18Registered User
Join Date: Apr:2003
Location: Varna
Posts: 5,205
Å íÿìà ãðåøêè, íàâñÿêúäå å Ñ íåùî ñè/Ä íåùî ñè, êàòî òå èëè ñà ïðàçíè èëè ñà ïúëíè ñ ïîëîæèòåëíè ÷èñëà, òà âàäÿ àâåðåéäæ íà êëåòêè ñ ïîëîæèòåëíè ÷èñëà è êëåòêè ñ DIV/0.




Reply With Quote


Ïðîáëåìíà R9 280, àðòåôàêòè
7th May 2023, 21:28 in Âèäåîêàðòè