formula help - Is this possible?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a worksheet that calculates the new year budgets. The budgets are a
specific percentage. So far, the total budget number is coming out correct.
However, I need to disregard the sales for the misc rep. I'm not sure how to
do this without losing my sum (90,539) or possibly the %Inc (.175). Could
anyone shed some light on this?
Thanks,
Mel


Rep Item % Inc Sales Budget (D2+(d2*C2))
1 Item X 0.175 6,703 7874
2 Item X 0.175 7,078 8315
3 Item X 0.175 7,183 8438
4 Item X 0.175 590 693
5 Item X 0.175 17,936 21070
6 Item X 0.175 6,347 7455
7 Item X 0.175 5,455 6408
8 Item X 0.175 13,268 15586
Misc Item X 0.175 12,514 14700
77,074 90,539
 
You've lost me. Are the numbers at the foot hard-values, or calculated by
Excel formulae?

What do you mean by lose the .175?

BTW, according to me, 6,703*1.175 is 7876 not 7874.
 
Hi Bob,

They are both. I have two sheets I am working off of. When I did the 17.5%
increase, I needed to verify that it totaled our company total. The values
at the bottom are excel calculations and it is matched with our company’s
totals. In essence I need to keep the bottom value.

Loss mean that I need to have a .175 increase.

Is my formula wrong for the % increase?
 
If you want to keep the bottom values, but also want a total to exclude
misc, why not just use

=D11-D10

etc.

The formula looks correct (=(D2+(D2*C2))), although personally I would use
=D2*(1+C2), but the numbers in column E don't agree with that formula. They
seem to be calculated at 17.4xx%, all different.
 
Hi Mel

It sounds like the company want an increase in sales from 77,074 to 90,539
which is a 17.5% increase.
However, the contribution from Misc is to be ignored.
Since Misc contributed 12,514 the remaining reps contributed 64,56 to the
years total.
In order to get 90,539 from this year's sales, those same reps would have to
have their contribution increased by 40% rather than 17.5%
So, substitute 0.4 in your cells C2:C9 and delete cell D10
 
I make that 17.47%, not 17.5% :-(

Roger Govier said:
Hi Mel

It sounds like the company want an increase in sales from 77,074 to 90,539
which is a 17.5% increase.
However, the contribution from Misc is to be ignored.
Since Misc contributed 12,514 the remaining reps contributed 64,56 to the
years total.
In order to get 90,539 from this year's sales, those same reps would have to
have their contribution increased by 40% rather than 17.5%
So, substitute 0.4 in your cells C2:C9 and delete cell D10
Click to expand...
 
Back
Top