Excel expression **URGENT**

[Anonymous]

Hi,

Can someone please help me understand what this excel expression does...

=AVERAGE((IF(SUBTOTAL(9,OFFSET($L$5,ROW($L$5:$L$706)-ROW($L$5),0))<>0,SUBTOTAL(9,OFFSET($L$5,ROW($L$5:$L$706)-ROW($L$5),0)))))

Thanks!

[dcohen13]

Please post sample data...

[robert_mika]

This is array formula winch is taking Average from filtered column

Before

after filter has been apply

[Peter_Cammaert]

Why ask in a QlikView forum? Don't we all "hate" Excel?

[robert_mika]

Not all...

[Anonymous]

Can you please help me write this in qlikview

if(sum([CC.Total Hotel Cost])/sum([CC.Rooms per Night]) <> '0' , sum([CC.Total Hotel Cost])/sum([CC.Rooms per Night]))

am trying to exclude 0 values from average

[shree909]

Hi,

Try this,

=if((sum([CC.Total Hotel Cost])/sum([CC.Rooms per Night])) > 0 , (sum([CC.Total Hotel Cost])/sum([CC.Rooms per Night])))

Thanks...

[Anonymous]

No that didn't work...

[robert_mika]

Can you describe your problem,post some data or add your qvw?

[danieloberbilli]

just to add an idea: one way to exclude zero values could be done in set analysis:

Avg({<MyField={">0"}>} MyField)