Kč - Kč ( ) - Kč - Kč Kč - Kč Kč - Kč Kč - Kč £ - £ £ - £ £ - £ £ - £ £ - £ ( ) ( ) £ - £ £ - £ € - € - € / / / / € - € Kč - Kč Kč - Kč Kč - Kč Kč - Kč - - - - - - Kč - Kč - Kč - - Kč - Kč - Kč : : : : Yes Yes No True True False On On Off ¥€ ( € ) € - € $ ($ ) € - € € - € € - € € - € - - € - € - € - - € - € - € $ ($ ) $ ($ ) $ ($ ) ( ) - $ $ ( ) $ - $ $ ( ) $ - € ( € ) \ \- \ \- \ \- \ \- \ \ - \ - - - \ \ - \ - - - - - - - $ $( ) $- ( ) - $ $( ) $- ( ) - % % % % % % % % % % € - € € - € € - € € - € € - € € - € € - € € - € € - € € - € € - € € - € € - € € - € € - € % % % % % % $ ( $ ) % % - % - - - £ - £ £ - £ % - % DM - DM DM - DM DM - DM DM - DM - - DM - DM - DM - - DM - DM - DM Ouch! - - Error detected! - Result=0 - No Errordetection / / / / / / - - - - : : : : : : / / : ( ) ( ) ( ) ( ) ( ) ( ) - : : : ( ) % ( ) - ( ) - ( ) - ( ) - . . : : : . . : - - - - € - € € - € € - € € - € % % / / / / $ - $ ??? Page 1 ??? (???) 00/00/0000, 00:00:00 Page 1 / 99 AVERAGEIF Function Result TRUE Sheet Result Description 1 TRUE Simple AVERAGEIF formulas with local references and values Function Expected Correct FunctionString Comment Product Name Sales Revenue book Property Value Commission 19 19 TRUE =AVERAGEIF(K2:K6,"<35") pencil 20 65 $35.00 100000 7000 19 19 TRUE =AVERAGEIF(K2:K6,"<"&MAX(K2:K6)) pen 35 85 200000 14000 25 25 TRUE =AVERAGEIF(K2:K6,">"&SMALL(K2:K6,1)) notebook 20 190 300000 21000 145 145 TRUE =AVERAGEIF(K2:K6,"<35",L2:L6) book 17 180 400000 28000 113.333333333333 113.333333333333 TRUE =AVERAGEIF(K2:K6,">"&MIN(K2:K6),L2:L6) pencil-case not 180 180 TRUE =AVERAGEIF(K2:K6,"<"&LARGE(K2:K6,2),L2:L6) 35 35 TRUE =AVERAGEIF(J2:J6,"pen",K2:K6) 27.5 27.5 TRUE =AVERAGEIF(J2:J6,"pen.*",K2:K6) 18.5 18.5 TRUE =AVERAGEIF(J2:J6,".*book.*",K2:K6) 18.5 18.5 TRUE =AVERAGEIF(J2:J6,".*"&M1&".*",K2:K6) 145 145 TRUE =AVERAGEIF(K2:K6,"<"&M2,L2:L6) 14000 14000 TRUE =AVERAGEIF(P2:P5,"<23000") A B 150000 150000 TRUE =AVERAGEIF(O2:O5,"<250000") 1 with leading ' (cell standard) 1 2 #DIV/0! #DIV/0! TRUE =AVERAGEIF(O2:O5,"<95000") numeric 1 (cell standard) 1 24500 24500 TRUE =AVERAGEIF(O2:O5,">250000",P2:P5) 1 in cell formatted as text 1 3 cell as text with numeric 1 1 4 5 =SUMIF(K14:K17,1,L14:L17) Tdf#96923 10.0000000 =SUMIF(K14:K17,"1",L14:L17) 4