[Solved] Formulas: Qucik Question

  • How do you get the average of a group of numbers, if that are over a certain amount only?


    EX:


    Group of number: 100,100,50,100,100


    I want to count as the average only the numbers above 100.


    So the answer would be 100+100+100+100/4 =100.


    Please help:):):)

  • This any use?


    <SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"&gt;</SCRIPT&gt;<CENTER&gt;<TABLE cellSpacing=0 cellPadding=0 align=center&gt;<TBODY&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=7&gt;<TABLE width="100%" align=center border=0&gt;<TBODY&gt;<TR&gt;<TD align=left&gt;<FONT color=white&gt;Microsoft Excel - Book1</FONT&gt;</TD&gt;<TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right&gt;___Running: xl2002 XP : OS = Windows XP </FONT&gt;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=7&gt;<TABLE width="100%" align=center border=0 VALIGN="MIDDLE"&gt;<TBODY&gt;<TR&gt;<TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption"&gt;<U&gt;F</U&gt;ile <U&gt;E</U&gt;dit <U&gt;V</U&gt;iew <U&gt;I</U&gt;nsert <U&gt;O</U&gt;ptions <U&gt;T</U&gt;ools <U&gt;D</U&gt;ata <U&gt;W</U&gt;indow <U&gt;H</U&gt;elp <A onclick=show_popup(); href="#javascript:void(0)"&gt;<U&gt;A</U&gt;bout</A&gt;</TD&gt;<TD vAlign=center align=right&gt;<FORM name=formCb653753&gt;<INPUT onclick='window.clipboardData.setData("Text",document.formFb685520.sltNb127543.value);' type=button value="Copy Formula" name=btCb592414&gt;</FORM&gt;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=7&gt;<TABLE border=0&gt;<TBODY&gt;<TR&gt;<FORM name=formFb685520&gt;<TD style="WIDTH: 60px" align=middle bgColor=white&gt;<SELECT onchange="document.formFb685520.txbFb761591.value = document.formFb685520.sltNb127543.value" name=sltNb127543&gt;<OPTION value='=SUMIF(A1:E1,">=100",A1:E1)/COUNTIF(A1:E1,">=100")' selected&gt;F2</OPTION&gt;</SELECT&gt;</TD&gt;<TD align=right width="3%" bgColor=#d4d0c8&gt;<B>=</B&gt;</TD&gt;<TD align=left bgColor=white&gt;<INPUT size=80 value='=SUMIF(A1:E1,">=100",A1:E1)/COUNTIF(A1:E1,">=100")' name=txbFb761591&gt;</TD&gt;</FORM&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<BR&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;A</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;B</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;C</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;D</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;E</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;F</CENTER&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<CENTER&gt;1</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"&gt;100</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"&gt;100</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"&gt;50</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"&gt;100</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"&gt;100</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"&gt;&nbsp;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<CENTER&gt;2</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"&gt;<A onclick="document.formFb685520.sltNb127543.options[0].selected=true; document.formFb685520.txbFb761591.value = document.formFb685520.sltNb127543.value;" href="#javascript:void(0);"&gt;100</FONT&gt;</A&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" colSpan=7&gt;<TABLE width="100%" align=left VALIGN="TOP"&gt;<TBODY&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; WIDTH: 120pt; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff" align=left&gt;<U&gt;Sheet1</U&gt;</TD&gt;<TD&gt;&nbsp;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;<BR&gt;<FONT color=#339966 size=1&gt;[HtmlMaker light Ver1.10] </FONT&gt;<FONT color=#339966 size=1&gt;To see the formula in the cells just click on the cells hyperlink or click the Name box</FONT&gt;<BR&gt;<FONT color=red size=1&gt;PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</FONT&gt;</CENTER&gt;


    HTH

    &lt;a href="http://www.mrexcel.com/relayforlife.shtml" target="new"&gt;&lt;img src="http://www.myimgs.com/data/vonpookie/anne_relay.gif"&gt;&lt;/a&gt;

  • Evidently not!


    The formula in f2 is =SUMIF(A1:E1,">=100",A1:E1)/COUNTIF(A1:E1,">=100")

    &lt;a href="http://www.mrexcel.com/relayforlife.shtml" target="new"&gt;&lt;img src="http://www.myimgs.com/data/vonpookie/anne_relay.gif"&gt;&lt;/a&gt;

  • RE [Solved] Formulas: Qucik Question


    I need to take this a step further. Let's just say that I have an arrangement of numbers.


    EX:


    Pink RED Blue Yellow Green
    2 2 2 2 2
    3 2 5 8 6
    5 7 8 4 3
    4 8 5 7 3
    5 7 5 6 3


    The first step was as you responded:


    1) Use =SUMIF(A1:A5,">=1")/COUNTIF(A1:A5,">=1") for column Pink


    The 2nd step that I want to do is to take an average of All the rows that was used in Pink which is greater than 1. Basically, I want to ideally throw out the samples that was in the column Pink.


    Please help.

  • If you are trying to average the columns, then use this for column B


    =SUMPRODUCT((A2:A6>=1)*B2:B6)/SUMPRODUCT((A2:A6>=1)*1)


    and this for column C


    =SUMPRODUCT((A1:A5>=1)*C2:C6)/SUMPRODUCT((A2:A6>=1)*1)


    The above assume that if column A qualifies, then there will be a number in the other column that you want to count. If not necessarily true, then change the formula for column B to:


    =SUMPRODUCT((A2:A6>=1)*B2:B6)/SUMPRODUCT((B2:B6>=1)*1)

  • You also could use the SUMIF() functions, but you need to add a thried argument to tell it to sum a range different than the one being evaluated. e.g.,


    SUMIF(A2:A6,"&gt;1",B2:B6)


    The COUNTIF does not offer the same flexibility.

  • From the data you posted none of the rows would be eliminated (all the column A "Pink" numbers are &gt;1). So I am not sure how to advise you. What are the numbers you actually are using?


    Also, what version of Excel are you using? If it's 97 or 2000 (I think this goes away with XP), try selecting one of the cells with a formula that does not seem to be calculating correctly. Then in the menu bar where the formula displays, click inside one of the function names in the formula and then click on the equal sign to the left of the formula. This should bring up the function wizard which will tell you what Excel thinks the function is finding. Click in another function name and the wizard will update dynamically. This technique helps to debug or to understand what formulas are doing.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!