Excel Question

%3CLINGO-SUB%20id%3D%22lingo-sub-1518546%22%20slang%3D%22en-US%22%3EExcel%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1518546%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Team%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20create%20a%20formula%20for%20my%20HR%20report%20that%20automatically%20calculates%20the%20total%20meal%20allowance%20(column%26nbsp%3B%20E)%20by%20using%20the%20following%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMeal%20Allowance%20rate(column%20B)%20*%20no.%20of%20days%20in%20Germany%20*%20no%20of%20children.%20The%20problem%20is%20regarding%20the%20last%20part%20of%20the%20formula%20(no.%20of%20children).%20I%20want%20to%20create%20a%20condition%20that%20each%20time%20no.%20of%20children%20is%201%20or%20more%2C%201%20should%20be%20added).%20So%20if%20an%20employee%20has%20one%20child%20the%20formula%20should%20look%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMeal%20Allowance%20rate(column%20B)%20*%20no.%20of%20days%20in%20Germany%20*%20(1%2B1).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%20how%20I%20can%20create%20a%20formula%20for%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETHanks%20in%20advance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENovice%20Kay%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1518546%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1518558%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1518558%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F677879%22%20target%3D%22_blank%22%3E%40NoviceKB%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%20that%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%5B%40%5BMeal%20Allowance%5D%5D%20*%20%5B%40%5BDays%20in%20Germany%5D%5D%20*%20(%5B%40%5BNo.%20of%20Children%5D%5D%2B(%5B%40%5BNo.%20of%20Children%5D%5D%26gt%3B0))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3ESo%2C%20no%20children%20-%20no%20meal%20allowance%2C%20correct%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1518593%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1518593%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B-ERR%3AREF-NOT-FOUND-%40Sergei%20Baklan%26nbsp%3B%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20prompt%20feedback.%20Actually%20one%20little%20detail%20I%20just%20realised%20I%20omitted.%20Apologies.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENo.%20of%20Days%20%3D%20No.%20of%20Days%20Spouse%20is%20in%20Germany.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20instance%2C%20in%20row%203%20on%20my%20sample%20sheet%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eit%20should%20look%20something%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMeal%20allowance%20(6.41)*%20days%20in%20Germany%20(20)%20*%20(0%2B1).%20This%20means%20the%20plus%201%20only%20comes%20in%20when%20the%20%22Days%20in%20Germany%22%20is%20more%20than%200.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20makes%20sense%20now.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1518684%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1518684%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F677879%22%20target%3D%22_blank%22%3E%40NoviceKB%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESo%2C%20if%20Stay%20in%20Germany%20is%20equivalent%20to%20one%20more%20kid%2C%20when%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%5B%40%5BMeal%20Allowance%5D%5D%20*%20%5B%40%5BDays%20in%20Germany%5D%5D%20*%20(%5B%40%5BNo.%20of%20Children%5D%5D%2B(%5B%40%5BDays%20in%20Germany%5D%5D%26gt%3B0))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EPlease%20check%20in%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1523493%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1523493%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bworked%20perfectly!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1524991%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1524991%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F677879%22%20target%3D%22_blank%22%3E%40NoviceKB%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional 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
Highlighted

@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?

Highlighted

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.

Highlighted

@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.

Highlighted

@Sergei Baklan worked perfectly! 

 

Thanks!

 

Highlighted

@NoviceKB , you are welcome