# Kibana Formula using ifelse with multiple conditions **URL:** \ **Category:** Kibana\ **Tags:** lens\ **Created:** [November 23, 2024, 3:33pm UTC](https://discuss.elastic.co/t/kibana-formula-using-ifelse-with-multiple-conditions/370983 "2024-11-23T15:33:11Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![bjornbear](https://avatars.discourse-cdn.com/v4/letter/b/e68b1a/32.png) [@bjornbear](https://discuss.elastic.co/u/bjornbear)\ **Post date:** [November 23, 2024, 3:33pm UTC](https://discuss.elastic.co/t/kibana-formula-using-ifelse-with-multiple-conditions/370983/1 "2024-11-23T15:33:11Z")
How can I combine multiple conditions in an ifelse statement? I need to avoid a situation where I divide by zero e.g. any of the following values: count() and count(shift='1w')+count(shift='2w') may not be 0 to avoid a N/A value which causes the wrong Dynamic coloring to be applied to it... I am calculating the deviation of my current transactions with historic values: count()-((count(shift='1w')+count(shift='2w'))/2). Negative percentages show a decrease, positive an increase... ---
**Author:** ![Tortoise](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tortoise/32/147587_2.png) [@Tortoise](https://discuss.elastic.co/u/Tortoise)\ **Post date:** [November 24, 2024, 5:03am UTC](https://discuss.elastic.co/t/kibana-formula-using-ifelse-with-multiple-conditions/370983/2 "2024-11-24T05:03:26Z")
Hello, For deviation can we try using derivatives ? > **[Derivative aggregation | Elasticsearch Guide \[8.16\] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-pipeline-derivative-aggregation.html)** ---
**Author:** ![bjornbear](https://avatars.discourse-cdn.com/v4/letter/b/e68b1a/32.png) [@bjornbear](https://discuss.elastic.co/u/bjornbear)\ **Post date:** [November 26, 2024, 11:54am UTC](https://discuss.elastic.co/t/kibana-formula-using-ifelse-with-multiple-conditions/370983/3 "2024-11-26T11:54:19Z")
Alas - not an option for me: I am working in Lens and am limited to the formulas available to me there (using 8.6.2 at present) ---
**Author:** ![Marco\_Liberati](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marco_liberati/32/82953_2.png) [@Marco\_Liberati](https://discuss.elastic.co/u/Marco_Liberati)\ **Post date:** [December 6, 2024, 11:08am UTC](https://discuss.elastic.co/t/kibana-formula-using-ifelse-with-multiple-conditions/370983/4 "2024-12-06T11:08:29Z")
Do you have the final formula? ---
**Author:** ![bjornbear](https://avatars.discourse-cdn.com/v4/letter/b/e68b1a/32.png) [@bjornbear](https://discuss.elastic.co/u/bjornbear)\ **Post date:** [December 6, 2024, 11:10am UTC](https://discuss.elastic.co/t/kibana-formula-using-ifelse-with-multiple-conditions/370983/5 "2024-12-06T11:10:52Z")
No - I did not manage this. Still hoping someone can answer. Doc is poor in this regard. ---
**Author:** ![Marco\_Liberati](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marco_liberati/32/82953_2.png) [@Marco\_Liberati](https://discuss.elastic.co/u/Marco_Liberati)\ **Post date:** [December 13, 2024, 1:57pm UTC](https://discuss.elastic.co/t/kibana-formula-using-ifelse-with-multiple-conditions/370983/6 "2024-12-13T13:57:12Z")
sorry, I meant if you can write a draft formula, just to make us better understand your problem that would be useful. I'll assume a general case here, so I would recommend 2 possible solutions: - if a function can return `null` (so not 0 like `count()`) then theres the `defaults( fn(...), 1)` to coalesce `null` values to `1` - the alternative for functions who never returns `null` but you want to avoid `0`, there's the `ifelse` function that can be used For instance a simple 0 fallback to avoid 0 division can be the following: ```auto 5 / ifelse( count() > 0, count(), 1) ```