What formula can speed up my monthly reporting


Board Regular
Sep 30, 2005

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!!
1SeptemberOctoberOct vs Sept
2Business NameGroup NoGroup NameDeal SizeBusiness NameGroupGroup NameDeal SizeBusiness NameGroupGroup NameVariance %
3Business 1Group 1Decrease637,667.73Business 1Group 1decrease87,799.27Business 1Group 1decrease-86.23%
4Business 2Group 4inc/dec mix1,987,751.14Business 2Group 4inc/dec mix127,941.64
5Business 3Group 2Control25,642.21Business 3Group 3increase12,176.17
6Business 4Group 1Decrease46,444.24Business 4Group 2control81,865.19
7Business 6Group 1decrease15,405.88Business 5Group 1decrease46,587.15
8Business 7Group 4inc/dec mix97,203.25Business 8Group 4inc/dec mix107,149.74
9Business 9Group 1decrease55,154.64Business 9Group 1decrease240,171.26
10Business 10Group 4inc/dec mix18,800.00Business 11Group 2control1,200.00
11Business 12Group 1Decrease52,950.16Business 12Group 4inc/dec mix22,998.03
12Business 13Group 1decrease21,717.92Business 13Group 1decrease326,476.07

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.
1SeptemberOctoberOct vs Sept
2Business NameGroup NoGroup NameDeal SizeBusiness NameGroup NoGroup NameDeal SizeBusiness NameGroup NoGroup NameNEW Variance %YOUR Variance %
3Business 1Group 1Decrease637,667.73Business 1Group 1Decrease87,799.27Business 1Group 1Decrease-86.23%-86.23%
4Business 2Group 4inc/dec mix1,987,751.14Business 2Group 4inc/dec mix127,941.64Business 2-93.56%-93.56%
5Business 3Group 2Control25,642.21Business 3Group 3increase12,176.17Business 3-52.52%-52.52%
6Business 4Group 1Decrease46,444.24Business 4Group 2Control81,865.19Business 476.27%76.27%
7Business 6Group 1Decrease15,405.88Business 5Group 1Decrease46,587.15Business 5#N/A202.40%
8Business 6Group 2Decrease46,587.15Business 6202.40%
Upvote 0
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?
Upvote 0

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,"")


Upvote 0
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.
Upvote 0
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
Upvote 0
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
2Bus. NameDeal SizeBus. NameDeal SizeBus. NameDeal Size
9Monthly data sheet bringing in data each month
testing for mr.excel quest.xls
1Oct. vs. Sep. VariancesDec. vs. Nov. Variances
2NameGroup #Grp. NameSep-05Oct-05Mo. Var.Nov-05Dec-05Mo. Var.
Upvote 0

Forum statistics

Latest member

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?

Disable AdBlock

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