I need help with this formula I created. Right now, it is finding a variable called NPN, which will equate for example to 35 cents.

NPN = (((Cover Price * Sale) * (1-.45)) - ((Sale * Cover Price) * .04) - (Draw * .30) - (Promo Money)) / Sale

What I need to rearrange is to now find an unknown of Sale if I want NPN to equal $1.00. So in other words, what does my sale have to be if I want NPN to equal $1.00. Problem is Sale is used in multiple places that I can't figure out how to isolate it.

Any suggestions?

Thanks!

Unknown008

NPN = (((C*S) * (1-0.45)) - ((S*C)*0.04) - (D*0.30) - (P)) / S

The bolded part is the one that i'm not sure. Is that 'one minus 0.45'?

Blakeschi

I figured it out. I ended up coding it in Excel VBA. For anyone interested, here is what I ended up with:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim NPN As Double, Sale As Long, CoverPrice As Currency, Draw As Long, Promo As Currency, x As Long

x = 2

Do Until x = 20
If x = 10 Or x = 11 Then GoTo nextx

NPN = Worksheets("Sheet1").Cells(42, x)
Sale = Worksheets("Sheet1").Cells(32, x)
CoverPrice = Worksheets("Sheet1").Cells(4, x)
Draw = Worksheets("Sheet1").Cells(6, x)
Promo = Worksheets("Sheet1").Cells(41, x)

Select Case Worksheets("Sheet1").Cells(42, x)
Case Is < 1
Do Until NPN >= Worksheets("Sheet1").Range("A44")
Sale = Sale + 1
NPN = (((CoverPrice * (Sale) * (1 - 0.5)) - ((Sale * CoverPrice) * 0.04) - (Draw * 0.3) - (Promo))) / Sale
Loop
Case Is > 1
Do Until NPN <= Worksheets("Sheet1").Range("A44")
Sale = Sale - 1
NPN = (((CoverPrice * (Sale) * (1 - 0.5)) - ((Sale * CoverPrice) * 0.04) - (Draw * 0.3) - (Promo))) / Sale
Loop
End Select
Worksheets("Sheet1").Cells(43, x) = Sale
nextx:
x = x + 1
Loop

End Sub