Simple Calc, but can't get forumula right

I have some numbers but trying to get them to work backwards..

Sales Net Adjustment = 65.6%
What would you call this adjustment?

Sales	Net	AVG Commission	Commission Paid
70,519.40	24,258.67	9.09%	2,367.71

These were all added up from a bunch of lines from the lower example.


There are a few gotchas but this is the basic.
averaged the commission because it ranges from 8% and 10% across 10 accounts

Now if I was looking only at commission paid
2,367.71

and know that it was ~9% of X
X= (2367.71/.09) (correct?)
why does the number not match -
is close but not exact - even doing 9.09


Now X = Z-65.6%

So my Formula is missing the last part to Find Z - going in reverse - knowing there should be a slight difference - I missing why my math is going so far off-course...

I can use the opposite of 65.6% (34.4%) and come close

2367.71 / 9.09% = 26,044.81 (figure this is due to averaging)

now 26,044.81/34.4% = 75,711.66

is all the difference just the average causing that? 5k off is quite a bit though


another better / cleaner example without averaging would be:

gross	net	com%	paid
3,816.74	1,312.96	8%	105.04

gross net comm paid
3,816.74 1,312.96 8% 105.04

this comes much closer..

with formulas
A B C
8.00% 65.60%
105.04 1313 3816.86
A2/B1 B2/34.4%

A	B	C
	8.00%	65.6%
105.04	1313.00	3816.86
	A2/B1	B2/34.4%

is there a formula that allows me to use the original %65.6?

Originally Posted by candybg

Somehow the INITIAL numbers don't seen to work.
Take the net sales of 24,258.67 and divide by the commission amount of 2,367.71, and you get an average commission of 9.7603%
This is much higher than your calculated 9.09%

spaces got removed when I saved then ran errands - extremely sorry that was not originally done in a table so it would be more clear...

end of the day the results are simple 3% of gross / 2.4% of gross... I'm just trying to figure out how to work in reverse to get the same numbers. More educational as I have all numbers available but was attempting to work from once side until I received the rest of the data... and couldn't get things to work correctly.

Gross - Sales adjustment(Gross-(Gross-65.6%) = Net
=Gross-(Gross-65.6%)
Net * Comm% = Paid commission

there is a real kicker that I've found, that eliminates commission payment based on what I believe is worse math than my own!


Contract Language originally - next contract reworded to clarify.
(both statements are in the same section)

Must increase the net revenue of the account by at least $1,000 and 30% over the previous 3 months to qualify.

The account must have increased by 30% above the average of the previous 3 months revenue before it qualifies.

New Contract: (new for all sales after new date)

Must increase the net revenue of the account by at least $1,000 and 10% gross revenue over the previous 3 months to qualify.

Kicker - the math
2nd Row is (-65%)

Sales	Existing Base	%Increase	$Increase
15,000.00	10,000.00	50.00%	5,000.00
4,500.00	3,000.00	50.00%	1,500.00

originally the math / formula looks like this and was used for the entire year until the last 2-3 months:

Sales	Base (existing sales)	New Sales	Base+%	Min	Sales Amount	Net Sales	Commission
15000	10000	5000	3000	1000	5000	1720	172.00
		Sales-Base	30%		IF(AND(NewSales>=Min,NewSales>=Base+%),NewSales,0)	Sales-(sales*65.6%)	Netsales*10%

Then this came out to correct their error - I think... but was wrong... can you spot it?

Sales	Base (existing sales)	New Sales	Base+%+base	Min+base	Sales Amount	Net Sales	Commission
15000	10000	5000	13,000	11,000	0	0	0
		Sales-Base	30%+Base	+Base	IF(AND(NewSales>=Min,NewSales>=Base+%),NewSales,0)	Sales-(sales*65.6%)	Netsales*10%

Latest release was this below to add 1,000 Net Minimum as Net
again can you spot why it doesn't give commission...

Sales	Base (existing sales)	New Sales	Base+%+base	NetMin+base	Sales Amount	Net Sales	Commission
15000	10000	5000	13,000	13,333.30	0	0	0
		Sales-Base	30%+Base	+Base	IF(AND(NewSales>=Min,NewSales>=Base+%),NewSales,0)	Sales-(sales*65.6%)	Netsales*10%

- The definition is escaping me...

Basically they are comparing the Change against the Gross - which will only succeed after 130/150% depending on the amount of the base

In my words (which I know are not correct)

The were comparing the differences and now they are comparing Sales Diff vs %Change+BaseGross

To correct - Re-add Base Gross back to sales Diff and the numbers stabliize
but you have to re-correct the formula for the IF(AND) statement to calculate the Correct New Sales #

IF(AND(NewSales>=Min,NewSales>=Base+%),NewSales,0)
Now
IF(AND(NewSales>=Min,NewSales>=Base+%),(NewSales-Base),0)

then commission correctly calculates...

again the language is escaping me on the exact definition ..

Currently they are Comparing Apples to Oranges and the qualification for commission fails even thought it passes both ways when corrected.

there are lower numbers that will not pass due to the 3333.33 obviously... but that becomes a contract language interpretation vs calculation error.