# HELP for IF STATEMENT, possibly with OR and AND Functions

1. ## 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!

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  Register To Reply

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

Try in D1: ``Please Login or Register  to view this content.``  Register To Reply

3. ## 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.  Register To Reply

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

Please post a sample sheet ( see yellow banner) Thx  Register To Reply

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

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

7. ## 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!  Register To Reply

8. ## 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!  Register To Reply

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

why is e8 45 500 has not been passed?  Register To Reply

10. ## 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,"")))))  Register To Reply

11. ## Re: HELP for IF STATEMENT, possibly with OR and AND Functions Originally Posted by davsth 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  Register To Reply

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