GET A RESULT BASED ON SPECIFIC Criteria

[enitron]

Hi.

I am new to this forum and i was wondering if someone could help me out with an excel function i need

So i have a table with the sales per product and the remaining stock. I want a function to calculate the appropriate quantity to be returned to the warehouse.
If the sales are lowered than 2 peices i want to leave as stock t peices and the remaining peices to be returned. If sale are more that 2 pieces i want stock equal to the sales.

Here is an exmple table:

The column: QUANTITY TO BE RETURNED is the desired result using a function.

R163	SALES	STOCK	QUANTITY TO BE RETURNED
PRODUCT 1	2	3	1
PRODUCT 2	3	4	1
PRODUCT 3	1	5	3
PRODUCT 4	3	2
PRODUCT 5	2	3	1
PRODUCT 6	0	7	5
PRODUCT 7	0	7	5
PRODUCT 8	1	6	4
PRODUCT 9	0	7	5
PRODUCT 10	0	7	5
PRODUCT 11	1	6	4

Thank you in advance for your help.

 

[ER_Neha]

Hello I hope this helps. The formula i Have used is long but it satisfy the two conditions

When
1. stocks<=2
2. Stock<=sales

The answer can be negative if the outer if condition is removed, this is used just to get zero in place of negative numbers.

Book1
	A	B	C	D
1	R163	SALES	STOCK	QUANTITY TO BE RETURNED
2	PRODUCT 1	2	3	1
3	PRODUCT 2	3	4	1
4	PRODUCT 3	1	5	3
5	PRODUCT 4	3	2	0
6	PRODUCT 5	2	3	1
7	PRODUCT 6	0	7	5
8	PRODUCT 7	0	7	5
9	PRODUCT 8	1	6	4
10	PRODUCT 9	0	7	5
11	PRODUCT 10	0	7	5
12	PRODUCT 11	1	6	4
Sheet1

Cell Formulas
Range	Formula
D2:D12	D2	=IF(IF(B2>=2,C2-B2,C2-2)>0,IF(B2>=2,C2-B2,C2-2),0)

 

[Peter_SSs]

Here is another way.

20 08 20.xlsm
	A	B	C	D
1	R163	SALES	STOCK	QUANTITY TO BE RETURNED
2	PRODUCT 1	2	3	1
3	PRODUCT 2	3	4	1
4	PRODUCT 3	1	5	3
5	PRODUCT 4	3	2	0
6	PRODUCT 5	2	3	1
7	PRODUCT 6	0	7	5
8	PRODUCT 7	0	7	5
9	PRODUCT 8	1	6	4
10	PRODUCT 9	0	7	5
11	PRODUCT 10	0	7	5
12	PRODUCT 11	1	6	4
Stock Returned

Cell Formulas
Range	Formula
D2:D12	D2	=C2-MIN(MAX(B2,2),C2)

 

[enitron]

Thank you so so much!!!
Both of your answers are working perfectly!!!
Your help is greatly appreciated!!

 

[Peter_SSs]

You're welcome. Glad we could help.