Help for Nested Conditionals (IF/AND/OR/SUMIF etc.)

ACAyeras

New Member
Joined
Sep 24, 2020
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
Hello and Good Day!

I have an issue with writing a proper conditional formula in Excel, so please help me

Elaborating 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...

A. If A is "Oranges"
(A1) On a certain client, $5 would be deducted from gross income once if his total order is below $50
(A2) Once the total orange purchases of a client breaches the $50 mark, additional charges will be deducted so that 10% of the total order is reduced

B. If A is "Apples", gross income should be multiplied to 0.8, regardless of the situation

C. If A1 is "Pears," regardless of the client,
(A1) If the total value gross income is below 500, it should be multiplied 0.8
(A2) Once its between 500 and 10,000, gross income must be multiplied to 0.9
(A3) Once it exceeds 10,000, gross income should be multiplied to 0.95

D. If no fruit is placed in a row, net income should be empty, too

I need a formula that operates with all of this.

Just think of it that Oranges rely on the amount of a certain client's total order, while Pears works for total orders of all clients.

And Oranges are the one that breaks my mind, since I don't know the right function to look for all the orders of a certain client if it meets the criteria, while applying to all possible clients

I know it's confusing, 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! :):):)
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

ACAyeras

New Member
Joined
Sep 24, 2020
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
Manual Calculation
 

Attachments

  • Screenshot_20200924-231111.jpg
    Screenshot_20200924-231111.jpg
    250.8 KB · Views: 2

Watch MrExcel Video

Forum statistics

Threads
1,113,928
Messages
5,545,076
Members
410,652
Latest member
Zot
Top