Jul 13 2020 04:58 AM
Hi Team,
I am trying to create a formula for my HR report that automatically calculates the total meal allowance (column E) by using the following formula:
Meal Allowance rate(column B) * no. of days in Germany * no of children. The problem is regarding the last part of the formula (no. of children). I want to create a condition that each time no. of children is 1 or more, 1 should be added). So if an employee has one child the formula should look like this:
Meal Allowance rate(column B) * no. of days in Germany * (1+1).
Any ideas how I can create a formula for this?
THanks in advance.
Novice Kay
Jul 13 2020 05:07 AM
As variant that could be
=[@[Meal Allowance]] * [@[Days in Germany]] * ([@[No. of Children]]+([@[No. of Children]]>0))
So, no children - no meal allowance, correct?
Jul 13 2020 05:32 AM
Hi @Sergei Baklan ,
Thanks for the prompt feedback. Actually one little detail I just realised I omitted. Apologies.
No. of Days = No. of Days Spouse is in Germany.
For instance, in row 3 on my sample sheet,
it should look something like this:
Meal allowance (6.41)* days in Germany (20) * (0+1). This means the plus 1 only comes in when the "Days in Germany" is more than 0.
Hope this makes sense now.
Jul 13 2020 06:11 AM
So, if Stay in Germany is equivalent to one more kid, when
=[@[Meal Allowance]] * [@[Days in Germany]] * ([@[No. of Children]]+([@[Days in Germany]]>0))
Please check in attached file.