# Excel Question

Occasional Contributor

# Excel Question

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?

Novice Kay

5 Replies

# Re: Excel Question

As variant that could be

``=[@[Meal Allowance]] * [@[Days in Germany]] * ([@[No. of Children]]+([@[No. of Children]]>0))``

So, no children - no meal allowance, correct?

# Re: Excel Question

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.

# Re: Excel Question

So, if Stay in Germany is equivalent to one more kid, when

``=[@[Meal Allowance]] * [@[Days in Germany]] * ([@[No. of Children]]+([@[Days in Germany]]>0))``

# Re: Excel Question

@Sergei Baklan worked perfectly!

Thanks!

# Re: Excel Question

@NoviceKB , you are welcome