• 0

# Excel formula to Capsio

## Question

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

## Recommended Posts

• 0

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

##### Share on other sites

• 0

Thank You, it will help

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.