AVG (days until client) and if

Jörg Schmidt1

Dear Smartsheet Community,

I wonder how I can fix this issue:

I want to show in my dashboard the average number of days until a lead becomes a customer. So far, so easy. But I only need the cases above zero days

Would it be =AVG(Collect[interested since]; >zero

days until client.jpg

P.S. I cannot put the 2 brackets behind the above formula.

Jörg Schmidt1

Hi @Paul Newcome ,

just solved problem this way: Thanks for your help :-)

=AVERAGEIF(({Tage Leads}); >0)

Paul Newcome

It would be more like this:

=AVG(COLLECT([interested since]:[interested since]; [interested since]:[interested since]; @cell > 0))

Jörg Schmidt1

Hi @Paul Newcome , could you please check once again. Thanks

I got "incorrect argument as default - {Tage Leads} is a cross sheet reference

=AVG(COLLECT({Tage Leads}); >0)

Jörg Schmidt1

Hi @Paul Newcome ,

just solved problem this way: Thanks for your help :-)

=AVERAGEIF(({Tage Leads}); >0)

Paul Newcome

@Jörg Schmidt1 The problem with your AVG/COLLECT is that you are missing a range. The first range is the range you want to collect. There should be a second range which is used as the criteria range. If this is the same as the range you want to collect, you still need to double up on it as in my previous example.