# What formula can speed up my monthly reporting

#### mark9988

##### Board Regular
Hello,

Each month I receive monthly data from my manager, here you see Oct & Sep. data. I proceed to make a variance report like what you see under the yellow heading manually by going down each monthly list.

So for example I go down the Oct list and I see that the same business is in the Sep. list so I calculate the variance as copy and past the other fields such as business name, group, and group name, but you can see that business 5 is in the Oct list, but not in the Sept list so I wouldn't be able to calculate the variance. This is very time consuming and I was hoping I can get some kind of formula to do the work for me.

I included an example of what my report looks like under the yellow heading, the variance formula in cell N3 is =(I3-D3)/D3.

Thank you for your help!!
Dealer_Variance_Example.xls
ABCDEFGHIJKLMN
1SeptemberOctoberOct vs Sept
2Business NameGroup NoGroup NameDeal SizeBusiness NameGroupGroup NameDeal SizeBusiness NameGroupGroup NameVariance %
4Business 2Group 4inc/dec mix1,987,751.14Business 2Group 4inc/dec mix127,941.64
8Business 7Group 4inc/dec mix97,203.25Business 8Group 4inc/dec mix107,149.74
Sheet1

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
By no means am I an expert, but try this and tell me if it works for you. You can remove the N/A error messages with an error trap. Just shows up because there is no match.
Book1
ABCDEFGHIJKLMNO
1SeptemberOctoberOct vs Sept
2Business NameGroup NoGroup NameDeal SizeBusiness NameGroup NoGroup NameDeal SizeBusiness NameGroup NoGroup NameNEW Variance %YOUR Variance %
Sheet2

Do just your business names have to match, or do the business group number and group name have to match also before there is a variance calculation? The way I see what you have is that you have a business name of say 1, but the group number and group name varies. Whyat do you do then?

Hi

Assuming that there is only a match on Business name then
N3: =IF(SUMPRODUCT(--(\$A\$3:\$A\$12=K3)),I3/SUMPRODUCT(--(\$A\$3:\$A\$12=K3),(\$D\$3:\$D\$12))-1,"")
Adjust the ranges and copy down.

If there has to be a match on Business Name and Group then
N3: =IF(SUMPRODUCT(--(\$A\$3:\$A\$12=K3),--(\$B\$3:\$B\$12=L3)),I3/SUMPRODUCT(--(\$A\$3:\$A\$12=K3),(\$D\$3:\$D\$12))-1,"")

HTH

Tony

I'm sorry, I should of been a little more specific on my initial post. The group and group name columns stay constant for each business name meaning they never vary.

So business 1 for example will always be in group number 1 and its group name will always be decrease , business 2 will always be in group number 4 and it will always be in group name inc/dec, etc.

I hope this makes sense.

I appreciate your help and posts and I will give your formulas a try.

have you invesigated using a pivot table?

I'm not familiar with pivot tables, but if it can create this report for me than I'll look into it as an option.

My goal is to be able to paste and copy the September and October data that I receive from my boss and have the report populate automatically.

I have to do this variance report for the last 36 months and there are hundreds of businesses in each monthly data set so I'm hoping to obtain a method that can produce results quickly

Does the following work for you?
This is bringing in your data each month on a separate worksheet named mo. data for reference here. Then creating a variance report worksheet. In this case, all the business names would be listed in column A along with their corresponding data in columns B & C. This would be entered just the one time on this sheet or from another sheet once and looked up, would not have to be repeated after that.

Then from the data sheet you would only be looking up the "deal size" for each business name. Your lookups and calculations can be done ahead of time and would be automatically filled in when you enter your data into the data sheet. I have used dashes to indicate that there was no data for a particular business name for that month. You could then filter the data or perform other analysis per your needs.
testing for mr.excel quest.xls
ABCDEF
1Sep-05Oct-05Nov-05
2Bus. NameDeal SizeBus. NameDeal SizeBus. NameDeal Size
31637,667.73187799.277100,000.00
421,987,751.14222496721217,000.00
5325,642.51329416425,619.00
6446,446.244179465514,732.00
7615,405.885176321115,976.00
8
9Monthly data sheet bringing in data each month
Mo.Data
testing for mr.excel quest.xls
ABCDEFGHI
1Oct. vs. Sep. VariancesDec. vs. Nov. Variances
2NameGroup #Grp. NameSep-05Oct-05Mo. Var.Nov-05Dec-05Mo. Var.
314Decrease637,667.7387,799.27-86%15,976.00
421Increase1,987,751.1422,496,721.001032%17,000.00
532Control25,642.5129,416.0015%-
643Inc/Dec/Mix46,446.24179,465.00286%25,619.00
757Decrease-176,321.00-14,732.00
866Increase15,405.88---
975Control---100,000.00
10810Inc/Dec/Mix----
1198Decrease----
12109Increase----
Variances

Replies
6
Views
363
Replies
0
Views
160
Replies
1
Views
231
Replies
7
Views
380
Replies
0
Views
2K

1,203,250
Messages
6,054,383
Members
444,721
Latest member
BAFRA77

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back