adityanshardul
New member
- Joined
- Jul 4, 2014
- Messages
- 8
- Reaction score
- 0
- Points
- 0
View attachment sample.xlsx
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