Crazy Subtraction 1

[tj007]

Can this be done? I have been confronted with a user and inventory tracking form.

Column A has the total for the month (50), column C has the total after the first week (40), column E has the total for the second week, and column G has the total for the third week, and column I has the total for the fourth week. Now, column K is used to total the amount used for the month, and column L has the amount left for the month.

I have explained to the user that is best to enter the amount used in columns C,E,G & I, and use column K to sum those columns and column L to subtract from A to get his amount left for the month. The user is trying to use this formula =SUM(C3-E3)+(G3-I3). Of course this is giving him some numbers which are not balancing out. Any suggestions, he just do not seem grasp entering the amoun used.

 

[SkipVought]

tj007,

Tables, source data, if you will, ought NOT to contain SUMMARY values. SUMMARY VALUES are a reporting technique.

You ought to enter net +/- values by date and the REPORT the summary values.

In fact, the implied structure of the table is not normailzed, since you have similar data (inventory values) repeated in columns (by summary date)

You TABLE structure at a minimum, ought to be something like...

InventoryItem
TansactionDate
TracsactionValue

From a table like this, you will be able to analyze/report in a FAR EASIER and COMPREHENSIVE manner than you could with the stated format. PivotTables are a VERY POWERFUL tool for reporting. Your staff needs to learn how to employ this tool.

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[tj007]

Thanks Skip

That is good advice, and I will do my best to explain that to the user. Wish me luck.

 

[SkipVought]

tj,

I just took the past 10 minutes dummying up some sample data and 5 SECONDS making the report.

InventoryItem 	TansactionDate 	TracsactionValue 
Screw         	37622          	1000             
Nut           	37622          	900              
Washer        	37622          	1100             
Screw         	37642          	70              
Screw         	37662          	29              
Washer        	37682          	67              
Nut           	37702          	94              
Screw         	37722          	9              
Nut           	37742          	-49              
Nut           	37762          	-41              
Screw         	37782          	44              
Washer        	37802          	-23              
Nut           	37822          	-10              
Nut           	37842          	-30              
Screw         	37862          	-72              
Screw         	37882          	93              
Washer        	37902          	37              
Nut           	37922          	4

report

Sum of TracsactionValue 	Years 	TansactionDate 	    	    	    
                     	2003  	               	    	    	Grand Total
InventoryItem           	Qtr1  	Qtr2           	Qtr3	Qtr4	    
Nut                     	994   	-90            	-40 	4   	868 
Screw                   	1099  	53             	21  	    	1173
Washer                  	1167  	-23            	    	37  	1181
Grand Total             	3260  	-60            	-19 	41  	3222

Sorry I could not get it to line up properly.


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[tj007]

skip

Bravo, other sections can use this as well. The problem is getting the users to understand. They want to push one button and the computer is suppose to know everything they are thinking. Thanks again.

 

[SkipVought]

Once you spend the time doing a proper design and construction, it then becomes merely a push-of-a-button to refresh the report!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[tj007]

I understand, I have pivot reports from Oracle data. In Crystal reports they are referred to as Cross Tab reports. Man, I just hat the leg work.