Addition formula

[snjpverma]

I want to calculate the total number of shortage. Please help me with a formula to do so. The excess should get adjusted with the forthcoming shortages. I am OK with using helper cells.

	A	B	C
1	Day	To be given	received
2	1st Day	3	4
3	2nd Day	3	2
4	3rd Day	3	2
5	4th Day	3	1
6
7		Total Shortage

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:97px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[jtakw]

Hi,

I'm probably misunderstanding your question:

Excel 2010
	A	B	C
1	Day	To be given	received
2	1st Day	3	4
3	2nd Day	3	2
4	3rd Day	3	2
5	4th Day	3	1
6
7		Total Shortage	3
Sheet18

Cell Formulas
Range	Formula
C7	=ABS(SUM(C2:C5)-SUM(B2:B5))

 

[snjpverma]

jkatw, thanks for the response. it works wonder.

 

[jtakw]

You're welcome, glad that's what you're looking for.

 

[snjpverma]

Jkatw, your formula serves my purpose but I was curious to know why the formula gives me a wrong outcome in the scenario when the Total of "Received" is more than the total of "To be given".
For example, in below scenario. the shortage is showing as 3 instead of 0.
In actual, there an excess of 3 here. So the shortage should be zero..

	A	B	C
	Day	To be given	received
1	1st Day	3	4
2	2nd Day	3	5
3	3rd Day	3	3
4	4th Day	3	3
		Total Shortage	3

<tbody>
</tbody>

 

[jtakw]

You didn't specify what you wanted when there's no shortage.
If you just want 0 to show when there's no shortage, use this updated formula, take out the ABS function if you want the shortage to show as a negative number.
But if there is an "Overage", what do you want happened?

Excel 2010
	A	B	C
1	Day	To be given	received
2	1st Day	3	4
3	2nd Day	3	5
4	3rd Day	3	3
5	4th Day	3	3
6
7		Total Shortage	0
Sheet22

Cell Formulas
Range	Formula
C7	=ABS(MIN(0,SUM(C2:C5)-SUM(B2:B5)))

 

[snjpverma]

jkatw, the latest formula you gave doesn't work in the below scenario.
on the first and second day, there is a shortage of 1 each.. which means the total shortage should be 2 but it is giving zero as a result.

	A	B	C
	Day	To be given	received
	1st Day	3	2
	2nd Day	3	2
	3rd Day	3	4
	4th Day	3	4
		Total Shortage	0

<tbody>
</tbody>

​

 

[jtakw]

I thought you said in your OP "The excess should get adjusted with the forthcoming shortages", so the 3rd and 4th day overages would offset the shortages from 1st and 2nd day?

 

[jtakw]

Maybe you want this:

Excel 2010
	A	B	C	D	E
1	Day	To be given	received	received	received
2	1st Day	3	4	4	2
3	2nd Day	3	2	5	2
4	3rd Day	3	2	3	4
5	4th Day	3	1	3	4
6
7		Total Shortage	3	0	2
Sheet22

Cell Formulas
Range	Formula
C7	=SUMPRODUCT(--(B2:B5>C2:C5))

 

[jtakw]

Ok, I Think I understand what you want now, so if and only if the Overage happens Prior to a Shortage, the overage would compensate for the shortage, but if the shortage happens before the overage, it is still counted, is that correct?

I've used a helper column if that's the case.

Excel 2010
	A	B	C	D	E	F	G	H
1	Day	To be given	received	received	received
2	1st Day	3	5	4	2	2	1	-1
3	2nd Day	3	2	5	2	1	3	-2
4	3rd Day	3	2	3	4	0	3	-1
5	4th Day	3	1	3	4	-2	3	0
6
7		Total Shortage				2	0	2
Sheet22

Cell Formulas
Range	Formula
F2	=C2-$B2+F1
F7	=ABS(MIN(0,F2:F5))