formula for adjustments to first figures to net to 0

[SSPoulin23]

Hi all,

I need a formula to accomplish the following and I can't figure this out (having a weird week).

Column E has the original contract amount. If there is an adjustment, the new amount is entered into Column F. All adjustments need to net to 0. I need a formula that will tell me if the adjustments net to 0 or not. it can really be in any cell. I have a total at the end of each column that can be used in the formula if you think it's useful. I don't have room for another column that says what the adjustment is for each line.

...E ..... F
1 5000 20000
2 15000 5000
3 50000
4 10000 15000
5 20000 10000
6
7 100000 50000 (totals)


I know this is probably very simple but I'm just off point today - I can't even think of how to word my simple question to search for an answer. Any help will be greatly appreciated! Have a great day!

thanks,

sarah

 

[oldbrewer]

5000	20000
15000	5000
50000				column totals are different
10000	15000
20000	10000
				formula in E3 is
				=IF(SUM(A1:A100)<>SUM(B1:B100),"column totals are different","column totals are equal")

<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[SSPoulin23]

thanks for the response - but that doesn't quite do it. The totals will always be different because some lines won't have any changes made to them.

 

[oldbrewer]

please define what you mean - all adjustments have to net to zero

so if one line is up 10000 and two lines are each down 5000 that is netting to zero?

 

[oldbrewer]

100	110
300	320
400	370
600
700	750
800	850
900	800
				OK, nets to zero
				formula in E12 (where the message is)
				=IF(SUM(B1:B7)<>SUM(A1:A7)-SUMPRODUCT((A1:A7)*(B1:B7="")),"does not net to zero","OK, nets to zero")

<colgroup><col span="15"></colgroup><tbody>
</tbody>

 

[SSPoulin23]

awesome. THANK YOU!!!!!!

 

[oldbrewer]

glad this retired oldie could help......