topic Re: Excel expression **URGENT** in QlikView https://community.qlik.com/t5/QlikView/Excel-expression-URGENT/m-p/858516#M1010521 <HTML><HEAD></HEAD><BODY><P>Not all...<IMG src="https://community.qlik.com/legacyfs/online/emoticons/cool.png" /></P></BODY></HTML> Thu, 30 Apr 2015 14:48:18 GMT robert_mika 2015-04-30T14:48:18Z Excel expression **URGENT** https://community.qlik.com/t5/QlikView/Excel-expression-URGENT/m-p/858512#M1010517 <HTML><HEAD></HEAD><BODY><P>Hi,</P><P></P><P>Can someone please help me understand what this excel expression does...</P><P>=AVERAGE((IF(SUBTOTAL(9,OFFSET($L$5,ROW($L$5:$L$706)-ROW($L$5),0))&lt;&gt;0,SUBTOTAL(9,OFFSET($L$5,ROW($L$5:$L$706)-ROW($L$5),0)))))</P><P></P><P></P><P>Thanks!</P></BODY></HTML> Thu, 30 Apr 2015 14:30:16 GMT https://community.qlik.com/t5/QlikView/Excel-expression-URGENT/m-p/858512#M1010517 Anonymous 2015-04-30T14:30:16Z Re: Excel expression **URGENT** https://community.qlik.com/t5/QlikView/Excel-expression-URGENT/m-p/858513#M1010518 <HTML><HEAD></HEAD><BODY><P>Please post sample data...</P></BODY></HTML> Thu, 30 Apr 2015 14:37:22 GMT https://community.qlik.com/t5/QlikView/Excel-expression-URGENT/m-p/858513#M1010518 dcohen13 2015-04-30T14:37:22Z Re: Excel expression **URGENT** https://community.qlik.com/t5/QlikView/Excel-expression-URGENT/m-p/858514#M1010519 <HTML><HEAD></HEAD><BODY><P>This is array formula winch is taking Average from filtered column</P><P></P><P></P><P>Before</P><P><IMG alt="30-Apr-15 3-46-18 PM.jpg" class="jive-image image-1" src="/legacyfs/online/85430_30-Apr-15 3-46-18 PM.jpg" style="height: auto;" /></P><P></P><P>after filter has been apply</P><P><IMG alt="30-Apr-15 3-46-42 PM.jpg" class="jive-image image-2" src="/legacyfs/online/85431_30-Apr-15 3-46-42 PM.jpg" style="height: auto;" /></P></BODY></HTML> Thu, 30 Apr 2015 14:45:45 GMT https://community.qlik.com/t5/QlikView/Excel-expression-URGENT/m-p/858514#M1010519 robert_mika 2015-04-30T14:45:45Z Re: Excel expression **URGENT** https://community.qlik.com/t5/QlikView/Excel-expression-URGENT/m-p/858515#M1010520 <HTML><HEAD></HEAD><BODY><P>Why ask in a QlikView forum? Don't we all "hate" Excel? <IMG src="https://community.qlik.com/legacyfs/online/emoticons/happy.png" /></P></BODY></HTML> Thu, 30 Apr 2015 14:47:07 GMT https://community.qlik.com/t5/QlikView/Excel-expression-URGENT/m-p/858515#M1010520 Peter_Cammaert 2015-04-30T14:47:07Z Re: Excel expression **URGENT** https://community.qlik.com/t5/QlikView/Excel-expression-URGENT/m-p/858516#M1010521 <HTML><HEAD></HEAD><BODY><P>Not all...<IMG src="https://community.qlik.com/legacyfs/online/emoticons/cool.png" /></P></BODY></HTML> Thu, 30 Apr 2015 14:48:18 GMT https://community.qlik.com/t5/QlikView/Excel-expression-URGENT/m-p/858516#M1010521 robert_mika 2015-04-30T14:48:18Z Re: Excel expression **URGENT** https://community.qlik.com/t5/QlikView/Excel-expression-URGENT/m-p/858517#M1010522 <HTML><HEAD></HEAD><BODY><P>Can you please help me write this in qlikview</P><P></P><P>if(sum([CC.Total Hotel Cost])/sum([CC.Rooms per Night]) &lt;&gt; '0' , sum([CC.Total Hotel Cost])/sum([CC.Rooms per Night]))</P><P></P><P></P><P>am trying to exclude 0 values from average</P></BODY></HTML> Thu, 30 Apr 2015 14:55:55 GMT https://community.qlik.com/t5/QlikView/Excel-expression-URGENT/m-p/858517#M1010522 Anonymous 2015-04-30T14:55:55Z Re: Excel expression **URGENT** https://community.qlik.com/t5/QlikView/Excel-expression-URGENT/m-p/858518#M1010523 <HTML><HEAD></HEAD><BODY><P>Hi,</P><P></P><P>Try this,</P><P></P><P>=if((sum([CC.Total Hotel Cost])/sum([CC.Rooms per Night])) &gt; 0 , (sum([CC.Total Hotel Cost])/sum([CC.Rooms per Night])))</P><P></P><P>Thanks...</P></BODY></HTML> Thu, 30 Apr 2015 18:36:39 GMT https://community.qlik.com/t5/QlikView/Excel-expression-URGENT/m-p/858518#M1010523 shree909 2015-04-30T18:36:39Z Re: Excel expression **URGENT** https://community.qlik.com/t5/QlikView/Excel-expression-URGENT/m-p/858519#M1010524 <HTML><HEAD></HEAD><BODY><P>No that didn't work...</P></BODY></HTML> Thu, 30 Apr 2015 19:12:03 GMT https://community.qlik.com/t5/QlikView/Excel-expression-URGENT/m-p/858519#M1010524 Anonymous 2015-04-30T19:12:03Z Re: Excel expression **URGENT** https://community.qlik.com/t5/QlikView/Excel-expression-URGENT/m-p/858520#M1010525 <HTML><HEAD></HEAD><BODY><P>Can you describe your problem,post some data or add your qvw?</P></BODY></HTML> Fri, 01 May 2015 04:14:07 GMT https://community.qlik.com/t5/QlikView/Excel-expression-URGENT/m-p/858520#M1010525 robert_mika 2015-05-01T04:14:07Z Re: Excel expression **URGENT** https://community.qlik.com/t5/QlikView/Excel-expression-URGENT/m-p/858521#M1010526 <HTML><HEAD></HEAD><BODY><P>just to add an idea: one way to exclude zero values could be done in set analysis:</P><P></P><P>Avg({&lt;MyField={"&gt;0"}&gt;} MyField) </P></BODY></HTML> Fri, 01 May 2015 05:23:07 GMT https://community.qlik.com/t5/QlikView/Excel-expression-URGENT/m-p/858521#M1010526 danieloberbilli 2015-05-01T05:23:07Z Re: Excel expression **URGENT** https://community.qlik.com/t5/QlikView/Excel-expression-URGENT/m-p/858522#M1010527 <HTML><HEAD></HEAD><BODY><P>it is not giving me any values</P></BODY></HTML> Fri, 01 May 2015 15:46:06 GMT https://community.qlik.com/t5/QlikView/Excel-expression-URGENT/m-p/858522#M1010527 Anonymous 2015-05-01T15:46:06Z