Excel formula to Capsio

[capsio88]

Hi I am using this formula on my excel worksheet; 

=IF(AND(I11<>"",I12<>"",I13<>"",I9<=3),AVERAGE(I11:I13),"") and

=IF(AND(K13<>"",K12<>"",K13<>"",I11<>"",I12<>"",I13<>"",AND(I9>3,I9<=6)),AVERAGE(I11:I13,K11:K13),"")

I would like to calculate average. If the field is blank, blank cells should not be calculated as shown in excel. How can I do that in Caspio? Also how can I add and statement like rules in Caspio?

Many thanks

[CoopperBackpack]

Hello @capsio88,

To replicate your formula, please, use the CASE expression with its CASE WHEN THEN ELSE construct in T-SQL.

Also, please note that AVG SQL function is available only in the Totals&Aggregations on the Report DataPages and has specific syntax https://howto.caspio.com/datapages/reports/advanced-reporting/totals-and-aggregations/
As a workaround you may sum all required columns and divide them to get the average value.

Your first formula should look like this:

CASE WHEN [@field:field_1] IS NOT NULL AND [@field:field_2] IS NOT NULL AND [@field:field_3] IS NOT NULL AND [@field:field_4] <=3
THEN ([@field:field_1]+[@field:field_2]+[@field:field_3])/3
ELSE ' '
END

[capsio88]

Thank You, it will help