adityanshardul

2014-07-04, 10:47 AM

2464

IF(I2="Source",SUMPRODUCT(((($C$5:$C$9="Self")+($C$5:$C$9="Spouse"))*($G$5:$G$9<5000))+(($C$5:$C$9="Child")*($G$5:$G$9<3000)),$H$5:$H$9*10%),0)*IF(G11="Y",0.9,1)

Formula Half Completed

Further Formula require

Result in H13 is prefect but want that if either G5 or G6 is <5000, then child <3000 *0% or its 10%) one more to be added in G5:G6 if"Fresh" *F5:F6<55,20%

Individual Formula Cell - J5:j6 Correct

Need to rectify in J7:J9

Combined Formula in H9

Further More Details in word to understand what i want

A Company is charging 10% loading on charge i.e. H5:H7

They came with new plan The business existing with them where product cost is <5000 for Self & Spouse loading of 10% is waived where as in case of child If

child is also there then his product cost should be <3000 his loading will also waived. But in case Self & Spouse is less than 5000 then waiver will not be applicable to child hence 10% loading to be applied on child product charge.

(Formula will be applicable to all child, product above line is applicable)

In Case if a customer is fresh to me & above 55 years then we are charging 20% loading for self & spouse whose age is <55 & again in case of child it is same but loading 10%

Again with *IF($G$11="Y",0.9,1),0) means

Y= if Family (one or more person) then 10% Discount applicable on whatever loading amount or 0

IF(I2="Source",SUMPRODUCT(((($C$5:$C$9="Self")+($C$5:$C$9="Spouse"))*($G$5:$G$9<5000))+(($C$5:$C$9="Child")*($G$5:$G$9<3000)),$H$5:$H$9*10%),0)*IF(G11="Y",0.9,1)

Formula Half Completed

Further Formula require

Result in H13 is prefect but want that if either G5 or G6 is <5000, then child <3000 *0% or its 10%) one more to be added in G5:G6 if"Fresh" *F5:F6<55,20%

Individual Formula Cell - J5:j6 Correct

Need to rectify in J7:J9

Combined Formula in H9

Further More Details in word to understand what i want

A Company is charging 10% loading on charge i.e. H5:H7

They came with new plan The business existing with them where product cost is <5000 for Self & Spouse loading of 10% is waived where as in case of child If

child is also there then his product cost should be <3000 his loading will also waived. But in case Self & Spouse is less than 5000 then waiver will not be applicable to child hence 10% loading to be applied on child product charge.

(Formula will be applicable to all child, product above line is applicable)

In Case if a customer is fresh to me & above 55 years then we are charging 20% loading for self & spouse whose age is <55 & again in case of child it is same but loading 10%

Again with *IF($G$11="Y",0.9,1),0) means

Y= if Family (one or more person) then 10% Discount applicable on whatever loading amount or 0