Excel Formula with Condition

[NoviceKB]

Hi Team,

I am in a bit of a fix with a formula I want to set up.

Goal is to calculate the total meal allowance (column E).

The intended formula is: Meal allowance rate (column B) * days in Germany (column C) * ((no of children)+1 if the number of days in Germany is more than zero).

I am struggling to capture the last bit of the formula (...*((no of children)+1 if the number of days in Germany is more than zero). The plus 1 only happens as long as the value in column C is more than zero.

Example is as shown in row 3 in my screenshot(Fred):

6.41* 16 * (0+1) = 384.6 (Here I added 1 because the value in C3 is more than zero.

Example in row 4 (Jane) is also seeing a +1 because even though the number of children is zero, the value in column C4 is more than zero.

Hope my explanation is clear enough.

Thanks in advance.

Regards,

Novice Kay

 

[Yongle]

Try this

=B2*C2*(D2+(C2>1))

 

[gaz_chops]

Something like

=B*C*(No of Children+if(C>0,1,0)

 

[NoviceKB]

Something like

=B*C*(No of Children+if(C>0,1,0)

Hi Yongle,

I just tried that in my first row and arrived at 384.6 instead of 576.9.

Where is the plus 1 in the formula?

THanks.

KB

 

[Fluff]

Cross posted Help on defining an Excel formula based on a special condition

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[NoviceKB]

Something like

=B*C*(No of Children+if(C>0,1,0)

yay!!!!!!!!!!! I have a winner!!!!!!!!!!!!!!! Thanks a lot Gaz Chops!!!

 

[gaz_chops]

You're welcome, thanks for the feedback.

 

[Yongle]

Hi Yongle,

I just tried that in my first row and arrived at 384.6 instead of 576.9.

Where is the plus 1 in the formula?

OOPS - a typo

=B2*C2*(D2+(C2>0))

Book1
	A	B	C	D	E
1	Name	Allowance per day	Days	Children
2	Jack	6.41	30	2	576.9
3	Fred	6.41	30	1	384.6
4	Jane	6.41	16	0	102.56
Sheet4

Cell Formulas
Range	Formula
E2:E4	E2	=B2*C2*(D2+(C2>0))

Where is the plus 1 in the formula?
Try these formula
=1+(100=100) [ returns 2 ]
=1+("A"="B") [ returns 1 ]
TRUE equates to 1 and FALSE equates to zero

so this is where your 1 comes from
+(C2>0)

 

[NoviceKB]

OOPS - a typo

=B2*C2*(D2+(C2>0))

Book1
	A	B	C	D	E
1	Name	Allowance per day	Days	Children
2	Jack	6.41	30	2	576.9
3	Fred	6.41	30	1	384.6
4	Jane	6.41	16	0	102.56
Sheet4

Cell Formulas
Range	Formula
E2:E4	E2	=B2*C2*(D2+(C2>0))

Where is the plus 1 in the formula?
Try these formula
=1+(100=100) [ returns 2 ]
=1+("A"="B") [ returns 1 ]
TRUE equates to 1 and FALSE equates to zero

so this is where your 1 comes from
+(C2>0)

Many many thanks!! Really appreciate the detailed explanation! THANKS!