Excel Question

Brass Contributor

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

5 Replies

@NoviceKB 

As variant that could be

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

So, no children - no meal allowance, correct?

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.

@NoviceKB 

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.

@Sergei Baklan worked perfectly! 

 

Thanks!

 

:)

@NoviceKB , you are welcome