Possible Circular Reference?

[jdmurphy42]

Hi guys,

i'm wondering if anyone can help, as this has got me completely confused... i'm not sure if there is a simple way to do this, or if I need to delve into the murky waters of circular referencing?

I have a spreadsheet that look like this...

[TABLE="width: 497"]
<colgroup><col width="107" style="width:80pt"> <col width="89" style="width:67pt"> <col width="78" style="width:59pt"> <col width="93" style="width:70pt"> <col width="130" style="width:98pt"> </colgroup><tbody>[TR]
[TD="width: 107, bgcolor: #008CA8"]Warranty Target[/TD]
[TD="width: 89, bgcolor: #008CA8"]Current Units[/TD]
[TD="width: 78, bgcolor: #008CA8"]Warranty %[/TD]
[TD="width: 93, bgcolor: #008CA8"]Units Needed[/TD]
[TD="width: 130, bgcolor: #008CA8"]Warranties Needed[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]20%[/TD]
[TD="bgcolor: transparent"]50[/TD]
[TD="bgcolor: transparent"]18%[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

I need the sheet to work out how many units I need, with warranties to achieve the target. Obviously I can use =SUM(Current Units*Warranty Target) to give me the number I should be on, and take off the number of warranties I already have, but as I sell more units, the number required to achieve target will change so I need it to work out how many extra units with warranties I need.

I hope that makes sense? I've been going round and round trying to work this one out for hours so I may not be explaining it very well, but I really hope it's enough for someone to help?

Thanks

 

[burns14hs]

I feel like you are looking for:

=(roundup(warranty target*current units,0))-(current units*warranty %)

although i feel in order for you to be calculating the current warranty % you should have a column that also shows the "current units with warranty" somewhere, which you could use in place of everything after the minus sign in the above equation.

 

[jdmurphy42]

Ok, maybe this will explain it better (I've had some coffee now)... so this is the actual spreadsheet rather than a quick example...

[TABLE="width: 637"]
<colgroup><col width="107" style="width:80pt"> <col width="61" style="width:46pt" span="2"> <col width="75" style="width:56pt"> <col width="61" style="width:46pt"> <col width="75" style="width:56pt"> <col width="61" style="width:46pt" span="2"> <col width="75" style="width:56pt"> </colgroup><tbody>[TR]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 197, bgcolor: #FFFF00, colspan: 3"]Week to Date[/TD]
[TD="width: 197, bgcolor: #92D050, colspan: 3"]Today[/TD]
[TD="width: 136, bgcolor: #AFF2FF, colspan: 2"]Required[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Target[/TD]
[TD="bgcolor: transparent"]Units[/TD]
[TD="bgcolor: transparent"]%[/TD]
[TD="bgcolor: transparent"]Warranties[/TD]
[TD="bgcolor: transparent"]Units[/TD]
[TD="bgcolor: transparent"]Warranties[/TD]
[TD="bgcolor: transparent"]Live %[/TD]
[TD="bgcolor: transparent"]Units[/TD]
[TD="bgcolor: transparent"]Warranties[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]40%[/TD]
[TD="bgcolor: transparent"]50[/TD]
[TD="bgcolor: transparent"]18.00%[/TD]
[TD="bgcolor: transparent"]9[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent, align: right"]20.00%[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

So as you can see, excel knows my target... how many I've done so far and what % I've sold with warranties, and therefore how many units of warranty I've done. It also knows how many units I've sold today and how many warranties, and from that can work out what my warranty % is right now. I should explain that this runs in excel on an iPad while i'm working so my live data is available to me wherever I am. What I now want it to tell me in the last 2 columns is how many more units I need to sell (with warranties) to achieve the target in the first cell...

so in the example above, if I sold 18, all with warranties, i'd be on 39.71% and still missing target, but if I sold 19 with warranties that would be 40.58% and exactly where I need to be.

The problem is, if I use the solution you've given, that only tells me what i'm missing so far, and doesn't factor in the additional units into the calculation, so it will tell me I need to sell 11... but if I sell 11 with warranties, that puts me on 61 units with 20 warranties, leaving me on 32.7% and way off target, because the calculation now contains an extra 11 actual units as well as the 11 warranties and obviously that means the whole calculation has changed.

So, in more VB terms (which I can't use as it won't run VBA on an iPad)... I want to increase the units by 1, and the warranties by 1 until the live % = 40%, then I want it to display how many units and warranties its counted to get to 40%

I hope that makes more sense, and i'm sure at the same time makes it a far more complicated solution... i'm sure it can be done (although, as stated in the title, I fear it may require some form of circular reference), but I just can't get my head around what's required to give me the right solution.

Thanks again to any and all who tackle this with me!

 

[burns14hs]

I understand what you're saying and I can give you a formula that would fix it - however the formula would be so long that it would be longer than the formula character limit of 8192 characters. This would require you to create multiple extra columns that i could walk you through in order to complete your task - you will need a minimum of 7 iterations in order to successfully achieve your number - If you would like to go through all these steps, let me know and I can help you out. Also, is your "40%" in A3 and each number next to it one column over to the right?

 

[StephenCrump]

Isn't this just simple algebra?

H3: =MAX(0,ROUNDUP((A3*(B3+E3)-(D3+F3))/(1-A3),0))

Excel 2010
	A	B	C	D	E	F	G	H	I
1		Week to Date			Today			Required
2	Target	Units	%	Warranties	Units	Warranties	Live %	Units	Warranties
3	40%	50	18.00%	9	5	2	20.00%	19
Sheet1

 

[jdmurphy42]

Simple algebra to you would have taken me the next week to work out!

That seems to have worked absolutely perfectly... thanks so much!

 

[StephenCrump]

Simple algebra to you would have taken me the next week to work out!

Then I'm glad I could save you some time.