+ Reply to Thread
Results 1 to 11 of 11

HELP for IF STATEMENT, possibly with OR and AND Functions

  1. #1
    Registered User
    Join Date
    09-13-2020
    Location
    Malabon City, Philippines
    MS-Off Ver
    MS 2013
    Posts
    5

    HELP for IF STATEMENT, possibly with OR and AND Functions

    I have a problem that my boss sent to me and I'm in a deep problem for three days already
    I tried to solve this on my own and asked other forums but to no avail, and now, my boss wants the file back

    Here's my issue:

    Column A have either "Oranges", "Apples", or "Pears"
    Column B have either "Fresh", "Moderate", or "Mature"
    I'll input a number on C
    Result will be flashed on D


    In a row...

    If A is "Oranges"
    (a) If B is "Fresh",
    (a1) If C is greater than or equal to 50, C should multiply to 0.9
    (a2) If C is between 0 and 50, C should be subtracted by 5
    (b) If B is "Moderate", the value of C is the same as the result in D1
    (c) If B is "Mature", the value of C should also be multiplied to 0.9

    If A is "Apples", it should multiply B to 0.8, regardless of B and C's values

    If A1 is "Pears"
    (a) Regardless of B's value
    (a1) If the total value of Cs with "Pears" in their As (across the sheet) is below 500, C should be multiplied to 0.8
    (a2) Once the total value of Cs with "Pears" in their As (across the sheet) is between 500 and 10,000, C should be multiplied to 0.9
    (a3) Once the total value of Cs with "Pears" in their As (across the sheet) is greater than 10,000, then the C should be multiplied to 0.95

    If A is empty, D should be empty, too


    It's really confusing for me and I can't do anything about it!
    Please help me and I would really appreciate it!

    Thank You and God Bless to you all!


    P.S. Tell me if you need the original file, but it's really chaotic and I almost did nothing about it

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    6,501

    Re: HELP for IF STATEMENT, possibly with OR and AND Functions

    Try in D1:

    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    09-13-2020
    Location
    Malabon City, Philippines
    MS-Off Ver
    MS 2013
    Posts
    5

    Re: HELP for IF STATEMENT, possibly with OR and AND Functions

    May I ask a few questions, Ma'am/Sir! I'm also trying to decipher the formula and learn it myself

    1. I think there is no condition in the formula if the Oranges are Moderate? I checked the formula and tried to input the values, but it doesn't retain and copy the value of C to D

    2. Why does the first condition of the formula is ","? Because I didn't encounter that before.


    By the way, Thank You for your answer, Sir! I'm already close to submitting this.

  4. #4
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,281

    Re: HELP for IF STATEMENT, possibly with OR and AND Functions

    Please post a sample sheet ( see yellow banner) Thx
    Be very, very careful using IFERROR ! It hides ALL errors which is not always what you want to get correct results

  5. #5
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    2,213

    Re: HELP for IF STATEMENT, possibly with OR and AND Functions

    I agree a sample sheet would be helpful and you can then check all of your outcomes are as you expect, perhaps tweaking bebos formula

    =IF(A1="","",IF(A1="Oranges",IF(B1="Mature",C1*0.9,IF(B1="Moderate",C1,IF(C1<50,C1-5,C1*0.9))),IF(A1="Apples",C1*0.8,IF(A1="Pears",LOOKUP(SUMIF($A$1:$A$4,A1,$C$1:$C$4),{0,500,10001},{0.8,0.9,0.95})*C1))))

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    6,501

    Re: HELP for IF STATEMENT, possibly with OR and AND Functions

    =IF(A1="","",IF(A1="Oranges",IF(A1="Mature",C1*0.9,IF(C1<50,C1-5,C1*0.9)),IF(A1="Apples",C1*0.8,
    IF(A1="Pears",LOOKUP(SUMIF($A$1:$A$4,A1,$C$1:$C$4),{0,500,10001},{0.8,0.9,0.95})*C1))))

    My mistake. Change A1="Mature" into B1="Mature"

    IF(A1="Mature",C1*0.9,IF(C1<50
    the bold and underline statement is under condition of FALSE (A1<>"Mature"==>equal "Fresh" or "Moderate" (both have same condition)

  7. #7
    Registered User
    Join Date
    09-13-2020
    Location
    Malabon City, Philippines
    MS-Off Ver
    MS 2013
    Posts
    5

    Re: HELP for IF STATEMENT, possibly with OR and AND Functions

    Thank You for your insights, everyone!
    Your suggestions worked and I was able to submit my document to my boss!

    All of you are my life savers! Once again, Thank You for your generosity and help!

  8. #8
    Registered User
    Join Date
    09-13-2020
    Location
    Malabon City, Philippines
    MS-Off Ver
    MS 2013
    Posts
    5

    Re: HELP for IF STATEMENT, possibly with OR and AND Functions

    Hello, Everyone!

    It's humiliating, but I need your help yet again
    There were some changes in the calculations and it now focuses on the client and not on the fruit quality

    To elaborate:
    Here's my issue:

    Column A are Fruits; They either have "Oranges", "Apples", or "Pears"
    Column B are Client's Names
    I'll input the gross income on C
    Result for net income will be flashed on D


    In a row...

    If A is "Oranges"
    In a client, $5 would be deducted if the order is below $50, and it doesn't care whether it's the from a single order or multiple ones
    Once the total orange purchases of a client breaches the $50 mark, 10% of the total orders from the beginning will be deducted

    If A is "Apples", it should multiply B to 0.8, regardless of B and C's values

    If A1 is "Pears"
    (a) Regardless of B's value
    (a1) If the total value of Cs with "Pears" in their As (across the sheet) is below 500, C should be multiplied to 0.8
    (a2) Once the total value of Cs with "Pears" in their As (across the sheet) is between 500 and 10,000, C should be multiplied to 0.9
    (a3) Once the total value of Cs with "Pears" in their As (across the sheet) is greater than 10,000, then the C should be multiplied to 0.95

    If A is empty, D should be empty, too


    I know it's confusing and I don't want to stir chaos like last time, so I'll attach a sample with my manual calculations and notes.
    Please don't hesitate to tell me if you need some clarifications

    Thank You everyone and God Bless!
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    2,213

    Re: HELP for IF STATEMENT, possibly with OR and AND Functions

    why is e8 45 500 has not been passed?

  10. #10
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    2,213

    Re: HELP for IF STATEMENT, possibly with OR and AND Functions

    in e2
    =IF(AND(B2="oranges",COUNTIFS($B$1:B1,"oranges",$C$1:C1,C2 )=0,SUMIFS($D$1:D2,$B$1:B2,"oranges",$C$1:C2,C2 )<50),D2-5,IF(AND(B2="oranges",SUMIFS($D$1:D2,$B$1:B2,"oranges",$C$1:C2,C2 )<50),D2,IF(AND(B2="oranges",SUMIFS($D$1:D2,$B$1:B2,"oranges",$C$1:C2,C2 )>=50),D2-MIN(SUMIFS($D$1:D2,$B$1:B2,"oranges",$C$1:C2,C2 )-50,D2)*0.1,IF(B2="apples",D2*0.8,IF(B2="pears",D2*0.8+MEDIAN(0,SUMIFS($D$1:D2,$B$1:B2,"pears" )-500,D2)*0.1+MEDIAN(0,SUMIFS($D$1:D2,$B$1:B2,"pears")-10000,D2)*0.05,"")))))

  11. #11
    Registered User
    Join Date
    09-13-2020
    Location
    Malabon City, Philippines
    MS-Off Ver
    MS 2013
    Posts
    5

    Re: HELP for IF STATEMENT, possibly with OR and AND Functions

    Quote Originally Posted by davsth View Post
    why is e8 45 500 has not been passed?
    Because the total of gross income of pears already exceeded the 500 mark (Look at row 4, 7, and 8)

    If Oranges depend on the total of every client, Pears depend on the total of all clients

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to combine multiple functions possibly an if/or OR if/and statment
    By Kim75 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-26-2016, 06:00 PM
  2. Nesting Vlookup Functions with IF Functions and possibly more
    By Anitarizzo in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 03-27-2014, 12:50 AM
  3. Replies: 1
    Last Post: 07-16-2012, 12:22 PM
  4. [SOLVED] Combining IF, ISERROR, and possibly OR or AND functions
    By loumarday in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-04-2012, 01:30 PM
  5. Possibly If/Then Statement
    By mchekmareva in forum Excel General
    Replies: 1
    Last Post: 09-19-2011, 06:55 PM
  6. Using text functions??? possibly anyway...
    By mburgess08 in forum Excel General
    Replies: 2
    Last Post: 02-19-2009, 07:41 PM
  7. If functions and possibly or
    By facmess1 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-03-2006, 02:13 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1