Need negative value to return exact value, and not accumulative value

[Goddess]

Hi,


Date file (Sheet1)

	A	B	C	D	E	F	G	H
1​	Insertion	Start	May	Jun	Jul	Aug	Sep	Oct
2​	Needed	50	20	20	20	20	20	20
3​
4​	Stock		0	0	0	0	0	0
5​	Total		30	10	-10	-30	-50	-70

Basically, C5=B2+C4-C2, D5=C5+C4-D2 and so forth


The result I want is, if it's >0, then it will return 10, but if it's <0, then it will only return the actual 'short' amount, ie, May and Jun is 10 each, Jul is (10), Aug is (20), etc.



Also, in Sheet2, after each month there will be a blank column for remarks. Anyway to copy the formula across, other than key in manually?

Result file (Sheet2)

A

B

C

D

E

F

G

H

I

J

K

L

M

N

1​

Requirement

May

Jun

Jul

Aug

Sep

Oct

2​

10

10

(10)

(20)

(20)

(20)

Thanks!

 

[etaf]

i would use a lookup on the 2nd sheet
Also
=($C$3+SUM($D$5:D5))-SUM($D$3:D3)
Should be a formula that can copy across for total
BUT WHY is August -20
When the result is -30

I have used TEXT as shown for the MONTH - BUT if real dates , then need to be the same in both sheets

chemistry_v1.xlsx
	A	B	C	D	E	F	G	H	I
1	A	B	C	D	E	F	G	H
2	1	Insertion	Start	May	Jun	Jul	Aug	Sep	Oct
3	2	Needed	50	20	20	20	20	20	20
4	3
5	4	Stock		0	0	0	0	0	0
6	5	Total		30	10	-10	-30	-50	-70
Sheet5

Cell Formulas
Range	Formula
D6:I6	D6	=($C$3+SUM($D$5:D5))-SUM($D$3:D3)

And then on a seperate sheet

chemistry_v1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1		May		Jun		Jul		Aug		Sep		Oct
2		10	#N/A	10	#N/A	-10	#N/A	-30	#N/A	-50	#N/A	-70
3
4
5
6		The result I want is, if it's >0, then it will return 10, but if it's <0, then it will only return the actual 'short' amount, ie, May and Jun is 10 each, Jul is (10), Aug is (20), etc.
7
8
9
10
11
12
13
14
15
Sheet6

Cell Formulas
Range	Formula
B2:L2	B2	=IF(INDEX(Sheet5!$D$6:$I$6,MATCH(Sheet6!B1,Sheet5!$D$2:$I$2,0))>0,10,INDEX(Sheet5!$D$6:$I$6,MATCH(Sheet6!B1,Sheet5!$D$2:$I$2,0)))

 

[Goddess]

i would use a lookup on the 2nd sheet
Also
=($C$3+SUM($D$5:D5))-SUM($D$3:D3)
Should be a formula that can copy across for total
BUT WHY is August -20
When the result is -30

I have used TEXT as shown for the MONTH - BUT if real dates , then need to be the same in both sheets

chemistry_v1.xlsx
	A	B	C	D	E	F	G	H	I
1	A	B	C	D	E	F	G	H
2	1	Insertion	Start	May	Jun	Jul	Aug	Sep	Oct
3	2	Needed	50	20	20	20	20	20	20
4	3
5	4	Stock		0	0	0	0	0	0
6	5	Total		30	10	-10	-30	-50	-70
Sheet5

Cell Formulas
Range	Formula
D6:I6	D6	=($C$3+SUM($D$5:D5))-SUM($D$3:D3)

And then on a seperate sheet

chemistry_v1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1		May		Jun		Jul		Aug		Sep		Oct
2		10	#N/A	10	#N/A	-10	#N/A	-30	#N/A	-50	#N/A	-70
3
4
5
6		The result I want is, if it's >0, then it will return 10, but if it's <0, then it will only return the actual 'short' amount, ie, May and Jun is 10 each, Jul is (10), Aug is (20), etc.
7
8
9
10
11
12
13
14
15
Sheet6

Cell Formulas
Range	Formula
B2:L2	B2	=IF(INDEX(Sheet5!$D$6:$I$6,MATCH(Sheet6!B1,Sheet5!$D$2:$I$2,0))>0,10,INDEX(Sheet5!$D$6:$I$6,MATCH(Sheet6!B1,Sheet5!$D$2:$I$2,0)))

Hi, August will be 20 as I need the actual shortfall, not accumulated from previous months.

Sheet1 will show the accumutated value throughout the months, whereas Sheet2 will show the actual amount that I need.

Thanks!

 

[etaf]

I think this is what you need
BUT maynot - I think it needs you to try a few different values - adding stock etc as i'm not sure it will work in all cases

i have added a helper row to calculate the shortfall
This helper row - difference between previous month if the amount is below 0, otherwise if > 0 then 10
This can be hidden if not wanting on the same sheet - but that seemed the best way, although I'm sure ian be done with various lookups - I look into that and reply

chemistry_v1.xlsx
	A	B	C	D	E	F	G	H	I
1	A	B	C	D	E	F	G	H
2	1	Insertion	Start	May	Jun	Jul	Aug	Sep	Oct
3	2	Needed	50	20	20	20	20	20	20
4	3
5	4	Stock		0	0	0	0	0	0
6	5	Total		30	10	-10	-30	-50	-70
7
8		Helper Shortfall		10	10	-20	-20	-20	-20
Sheet5

Cell Formulas
Range	Formula
D6:I6	D6	=($C$3+SUM($D$5:D5))-SUM($D$3:D3)
D8:I8	D8	=IF(D6>0,10,D6-C6)

AND for the separate sheet, still used a lookup , to lookup the month and return the results of the helper column

chemistry_v1.xlsx
	B	C	D	E	F	G	H	I	J	K	L
1	May		Jun		Jul		Aug		Sep		Oct
2	10		10		-20		-20		-20		-20
3
4
5
6	The result I want is, if it's >0, then it will return 10, but if it's <0, then it will only return the actual 'short' amount, ie, May and Jun is 10 each, Jul is (10), Aug is (20), etc.
7
8
9
10
11
12
13
14
15
Sheet6

Cell Formulas
Range	Formula
B2:L2	B2	=IFERROR(INDEX(Sheet5!$D$8:$I$8,MATCH(Sheet6!B1,Sheet5!$D$2:$I$2,0)),"")

 

[Goddess]

Hi Thanks!

However, for July (F6), the result should be -10, as that is the actual shortfall for the month. This is where I'm stuck...

 

[etaf]

sorry , i'm confused now , and not following

However, for July (F6), the result should be -10, as that is the actual shortfall for the month.

F6 is -10 on my posts on the Total Row

F8 Helper column on that sheet "Shortfall" is -20

F2 Shortfall on the 2nd sheet is -20

can you give the expected results again , and the reason calculation, if only in words

 

[Goddess]

sorry , i'm confused now , and not following

F6 is -10 on my posts on the Total Row

F8 Helper column on that sheet "Shortfall" is -20

F2 Shortfall on the 2nd sheet is -20

can you give the expected results again , and the reason calculation, if only in words

Hi,

The expected result I want on Sheet2 is as per my 1st post. The logic is, if the result is more than 0 (meaning I still have stock on hand), I will only order 10 pcs for that month. Hence, if the result is >0, it should return 10. However, if the stock falls below 0, I only need to order as per the shortfall for that month. Hence, I need the actual shortfall for that particular month.

So in my example below, July's shortfall is -10, August is -20 and so forth.

Hope I'm not confusing you

A

B

C

D

E

F

G

H

I

J

K

L

M

N

1

Requirement

May

Jun

Jul

Aug

Sep

Oct

2

10

10

(10)

(20)

(20)

(20)

 

[etaf]

can you add some stock , row 4 - and then expected results , for various months , so I can work out the full formulas for various scenarios

 

[Goddess]

Hi! Sorry for the late response. I managed to work it out. Not sure if it makes sense or not

	B	C	D	E	F	G	H	I	J	K
2​	Insertion	Start	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
3​	Needed	50	60	20	50	20	30	20	30	20
4​
5​	Stock In		40	10	0	40	0	60	0	0
6​	Total		30	20	-30	-10	-40	0	-30	-50

Result

	A	B	C	D	E	F	G	H
1​	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2​	10​	10​	-30​	-10​	-30​	10​	-30​	-20​

Formula I used on A2 (for the month of May)

=IFS(D6>=0,10,AND(D6<0,C6>=0),D6,AND(D6<0,C6<0,D5<=0),-D3,AND(D6<0,C6<0,D5>0),C6+D5-D3)

 

[etaf]

I managed to work it out.

glad you managed to resolve the issue and have a solution