Excel: Calculate Discount Based on Order Amount, IF Function & Conditional Functions

Hello, I have a few doubts about Excel. I am not asking for a ready-made solution, but for a hint and explanation of what, where, and what.

In the first order, I have to adjust the amount of the discount to the amount of the order. So, for example, from PLN 4,000 the discount is 1%, from 8,000 - 2% and so on. I don't know what pattern to use.

In the second command, I am supposed to calculate the discount amount based on the order amount using only the if function.


Besides, I don't know what it means:

It should be remembered that the sheet generated in tasks 1, 2 and 4 is to react appropriately to any entered values. You should not use hard assignments, only conditional functions.

So if I create such a conditional function, the sheet will automatically calculate everything for me?

If I understand correctly, the solution to the problem is to use the IF () conditional function nested on as many levels as there are consecutive discount groups (note, because Excel has a limit on the number of levels of nesting this function).

I shot the screen and like this:
in cell B2 there is an entry: = IF (A2> 8000, 0.02, IF (A2> 4000, 0.01, 0))
in cell C2 there is an entry: = A2- (A2 * B2)

Such a sheet will automatically count the amounts for any amount entered in cell A2.

= IF (B2> = 4000, 0.01, IF B2> = 8000, 0.02; IF B2> = 12000, 0.05; IF B2> = 20000, 0.07; IF B2> = 24000, 0, 09; IF B2> = 30000; 0.10; IF B2> = 50000; 0.12; IF B2> = 100000; 0; 0))

I tried to adjust it to my requirements, but something does not come into play. With the amount of 100,000 thousand when i type to display a text message in a cell it will result in something?

Added after 42 [minutes]:

I've already dealt with it , the solution was:

= IF (B2> = 4000, 0.01, IF (B2> = 8000, 0.02, IF (B2> = 12000, 0.05, IF (B2> = 20000, 0.07, IF (B2> = 24,000, 0.09, IF (B2> = 30,000, 0.1, IF (B2> = 50,000, 0.12, IF (B2> = 100,000,0,0,0))))))))

There is only a problem, namely that each amount of the order amount is assigned a value of 0; 01

In my opinion, the first point of this task concerns the creation of a discount table.
This is where we enter both the value and the discount percentage for a given value. Such a horizontal (two-line) or vertical (two-column) table.

This is what will be changed because "the sheet should respond appropriately to any entered values"

Now for the second thing. You don't quite understand the function if ()
If (condition; if true; if false)
In such functions it helps if you just (even aloud) read the function.

= IF (B2> = 4000, 0.01, IF B2> = 8000, 0.02; IF B2> = 12000 etc.

Listen to what you wrote:
If B2 is greater than 4,000,000 then 0.01 and if smaller (from 4,000) then if the same B2 is greater than 8,000,000 then ...

You see the error.
After all, any amount above 4,000, i.e. 8,000 and 12,000. etc will meet first condition, unless it will be smaller from 4000 but then there is no need to check further if they are greater than 4000 so it will be fulfilled in the last if the condition for false, i.e. 0


And now the most important thing is what the teacher ordered. these values were not supposed to be rigidly 8000 12000 etc. and 1% 2% etc. were only supposed to refer to the values in the table that you were supposed to create in the first point.

I just thought about it now. I will create a rebate table, a two-column "rebate amount" and "rebate amount", but how will the program be able to differentiate what rebate amount to choose?

In my opinion, you enter data in this table manually, here and here

Only in "if" you make everything dependent on the cells in this table.
The condition is that the table has to be either in ascending or descending values (it cannot be mixed because it will not come out with "if" nesting then instead of if () you would have to use search.vert () or search.level ())
Based on this table, you have to build if ()


edit
----------------
There is one more thing in point 2 you have to calculate discount amount and not give a discount.

For the same information in excel 2007 and above, you can nest if w if up to 64 times in excel 2003 and below only up to 7 times

Well then you explained a little to me, but where do I use the if function? After all, I can calculate the rebate using the normal function.



I entered all the values from the "finger".

If you want to show the discount amount separately (C15 and below), here will be the if () functions. And then you can actually apply the usual multiplication.
But (I thought) point 2 says that you have to calculate the discount amount. So instead of separately showing the percentage for a given amount paid by a given company, immediately multiply it by the base amount already inside the formulas if (). But that could only be my illusion.

I don't really understand, can you give an example?

Added after 3 [minutes]:

= IF (A9

I can give you an example, but then I will solve the problem for you.
On a margin, for 100,000, it cannot be "negotiated", but only a percentage should be given, because it will not pass.

If you want to leave the discount percentage in column c then:
Enter C15
= if (B15> = A11; B11; if (B15> = A10; B10; and so on


If only the rebate amount is to be (this is how I understand task 2 - but I can be wrong) then:
WC 14, change the header to "rebate amount".
Enter C15
= if (B15> = A11; B15 * B11; if (B15> = A10; B15 * B10; and so on
I think you can handle it, because I gave you the solution on a tray - I don't know if the teacher will be satisfied.


If you want to copy this formula downwards, you will have to add the $ sign in a few more places

I dealt with it before, namely I arranged the conditions in ascending order instead of descending. Thanks for your help anyway.

Now there is a problem with these stamps, my function for calculating the amount of discounts as a percentage looks like this:

= IF (B15> = A11; "negotiable"; IF (B15> = A10; B10; IF (B15> = A9; B9; IF (B15> = A8; B8; IF (B15> = A7; B7; IF (B15> = A6, B6, IF (B15> = A5, B5, IF (B15> = A4, B4 )))))))

Where do I add $ marks so I can drag it down?

I will give you a theory, otherwise you will not learn (absolute address).
The $ character in an address stops row or column numbering or both during copying.
Example

1. Cell says = A $ 10
if I copy sideways, only the letter of the address column will change. Frankly speaking, copying such an example only to the right or left, you do not have to "stop" the number of the line because it will not change. The same will come out when you type = A10
However, when we copy such an entry down / up, "A" does not change (because we copy in the same column), but when there is a dollar in front of the line number, the line number will not change either. Without the dollar, the row number will change. CHECK .

2. Cell says = $ A10
If we copy sideways, column "A" remains constant / unchanged. Copying down / up will change the number of the line (and the A lock when copying down / up is not necessary because the column does not change) CHECK

3. The cell says = $ A $ 10
Here, when copying both sideways and down or up, neither the column nor the address line will change. CHECK

I suggest you copy your formula one cell down and double click on it (or click on the formula bar). All references will be highlighted in color.
Check which cell addresses are left shifted unnecessarily and add dollars in the formula above to block the addressing. Copy - check again. Without your own practice, you won't learn it. I think you can handle it.

My hint is that you only copy down and not all addresses need to be blocked.



-------------------
One more thing:
If you do not multiply inside if (), but only indicate the addresses of discounted cells, then instead of permanently entering "to be agreed" also indicate the cell containing this inscription (after fulfilling the condition, what is entered into cell B11 will appear in the result)


And a second one:
enter an amount lower than 4000 see what will appear in the cell with discounts. It should be? can he enter "0"?

Thanks, I coped. As for the second issue, it seems to me that the "0" at the end of the formula is not needed because 0% in the first and second cases anyway.