Formula with missing data

[pascos88]

Hello everyone,

I have this problem.

In my chart I calculated a theoretical value like this :

=sum([KWh Real]/kWp)/Count(DISTINCT Company).

Whan I have all data, my theoretical value is good, the problem is whan the data missing.

The problem is that I always take the count(Company), but whan the data missing I should take just the number of the company that have the datas.

Someone can help me?

Regards

Pasquale

[alexandros17]

Try something like:

If(isnull([KWh Real]),

Count(DISTINCT Company).,

sum([KWh Real]/kWp)/Count(DISTINCT Company)

)

Let me know

[sunny_talwar · Accepted solution]

May be this:

=Sum([KWh Real]/kWp)/Count(DISTINCT {<[KWh Real] = {"=Len(Trim([KWh Real])) > 0"}>} Company)

or

=Sum([KWh Real]/kWp)/Count(DISTINCT {<[KWh Real] = {"=Len(Trim([KWh Real])) > 0"}, kWp = {"=Len(Trim(kWp)) > 0"}>} Company)

[pascos88]

Hello Alessandro thanks for the response,

but with this formula I will have just a value, but I need to divide this count(Company) that have a value with

sum([KWh Real]/kWp).

[pascos88]

Thanks Sunny

[sunny_talwar]

No problem