PDA

View Full Version : If Formula - Multiple Quite Complex



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

Bob Phillips
2014-07-04, 02:58 PM
That formula says

IF C5:C9 contains "Self" OR "Spouse" AND G5:G9<5000
OR C5:C( = "Child" AND G5:G9<3000
Then take 10% of value in H5:H9

Can you explain what you mean in a similar manner?

adityanshardul
2014-07-04, 03:07 PM
It means for Child Formula is Same
For a Child if either self or spouse anyone has cost greater than or = 5000, Child Value is greater than or = 3000 then 0,)10%)*IF($G$11="Y",0.9,1),0)

adityanshardul
2014-07-04, 03:08 PM
i mean to say all 3 Child Rows & Formulas will be same

adityanshardul
2014-07-04, 03:42 PM
Can you please work on J7 please if either or both of G5 or G6 is greater than 5000 & G7 is greater than 3000,0),H7*10%)*IF($G$11="Y",0.9,1),0)



That formula says

IF C5:C9 contains "Self" OR "Spouse" AND G5:G9<5000
OR C5:C( = "Child" AND G5:G9<3000
Then take 10% of value in H5:H9

Can you explain what you mean in a similar manner?

adityanshardul
2014-07-04, 04:07 PM
Can you please work on J7 please if either or both of G5 or G6 is greater than 5000 & G7 is greater than 3000,0),H7*10%)*IF($G$11="Y",0.9,1),0)


That formula says

IF C5:C9 contains "Self" OR "Spouse" AND G5:G9<5000
OR C5:C( = "Child" AND G5:G9<3000
Then take 10% of value in H5:H9

Can you explain what you mean in a similar manner?

adityanshardul
2014-07-04, 04:26 PM
Only masters can solves this??????????????????

adityanshardul
2014-07-04, 05:07 PM
Bingo - My problem is solved individually
Can anybody can solve H13 by reading the thread which is very much clear

Bob Phillips
2014-07-04, 11:45 PM
My problem is solved, can anyone solve it?

You post the same response over and over.

What are you playing at?

adityanshardul
2014-07-05, 06:13 AM
I m Sorry
New to this site & while sending it was showing an error every time i submit reply.
why should i play Sir???


My problem is solved, can anyone solve it?

You post the same response over and over.

What are you playing at?