Results 1 to 10 of 10

Thread: If Formula - Multiple Quite Complex

  1. #1

    If Formula - Multiple Quite Complex



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    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?

  3. #3
    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)

  4. #4
    i mean to say all 3 Child Rows & Formulas will be same

  5. #5
    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)


    Quote Originally Posted by Bob Phillips View Post
    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?

  6. #6
    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)

    Quote Originally Posted by Bob Phillips View Post
    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?

  7. #7
    Only masters can solves this??????????????????

  8. #8
    Bingo - My problem is solved individually
    Can anybody can solve H13 by reading the thread which is very much clear

  9. #9
    My problem is solved, can anyone solve it?

    You post the same response over and over.

    What are you playing at?

  10. #10

    If Formula

    I m Sorry
    New to this site & while sending it was showing an error every time i submit reply.
    why should i play Sir???

    Quote Originally Posted by Bob Phillips View Post
    My problem is solved, can anyone solve it?

    You post the same response over and over.

    What are you playing at?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •