simple percentage increase to the quantity in VB

[rebeccaogun]

Hi guys,

Can anyone help me with this ??

My original VB code is


ElseIf brand = "MAC" And year = "2013" Then
Profit = 22 / 100 * cost * quantity


ElseIf brand = "Rimmel LONDON" And year = "2013" Then
Profit = 31 / 100 * cost * quantity


ElseIf brand = "REVLON" And year = "2013" Then
Profit = 23 / 100 * cost * quantity


ElseIf brand = "MaXFactor" And year = "2013" Then
Profit = 26 / 100 * cost * quantity

And now I want to add a 12% increase to the quantity. How do I do this??

Thank you very much

 

[BadgerRoad]

Just multiply the Profit equation by 1.12 so the equation would read

Profit = 22 / 100 * cost * quantity * 1.12
Do this for each Profit equation, or you could add an equation at the beginning as follows:
quantity = quantity * 1.12

 

[rebeccaogun]

Thank you very much!

 

[BadgerRoad]

You didn't mention this, but if quantity is an integer and you want to keep it an integer then you would need to do some rounding after the multiplication.

 

[rebeccaogun]

You didn't mention this, but if quantity is an integer and you want to keep it an integer then you would need to do some rounding after the multiplication.

Yes I remembered to do that. Sorry about this but I've got another problem, I'll try to explain this as clearly as I can.

The whole VB code is this:

Function Profit(brand, year, cost, quantity, increase)


If brand = "MAC" And year = "2012" Then
Profit = 24 / 100 * cost * quantity


ElseIf brand = "Rimmel LONDON" And year = "2012" Then
Profit = 34 / 100 * cost * quantity


ElseIf brand = "REVLON" And year = "2012" Then
Profit = 26 / 100 * cost * quantity


ElseIf brand = "MaXFactor" And year = "2012" Then
Profit = 40 / 100 * cost * quantity




ElseIf brand = "MAC" And year = "2013" Then
Profit = 22 / 100 * cost * quantity * 1.12


ElseIf brand = "Rimmel LONDON" And year = "2013" Then
Profit = 31 / 100 * cost * quantity * 1.12


ElseIf brand = "REVLON" And year = "2013" Then
Profit = 23 / 100 * cost * quantity * 1.12


ElseIf brand = "MaXFactor" And year = "2013" Then
Profit = 26 / 100 * cost * quantity * 1.12

I did what you said and added the 1.12, then I added increase to the list of arguments. Once I did this I copied the function into excel; =Profit(D2, I2, E2, F2, Increase) and my 2012 list keeps changing to #VALUE! I know there is something wrong with the function I put in excel but I can't remember what to do.

Thanks for your help

 

[BadgerRoad]

I'm a little confused on what you are trying to do. Right now, as you have it coded the variable "increase" is not used in the function. If you call it as follows it will work:
=Profit(D2, I2, E2, F2, 1). However, the last parameter is not used anywhere. If you omit the 1 in the list of parameters you will get the VALUE error.
If your intent is to use the variable "increase" as the increase in quantity you would need to change the 1.12 to the variable "increase" in the profit equation as follows:

Profit = 22/100 * cost * quantity * increase

You could further simplify the function by including a parameter for profit percentage (22/100, 31/100, etc) as an argument and passing it from the spreadsheet. That way the function would be reduced to one line of code.

Let me know if this makes sense and if it is your intent. I can help you modify the function if you need it.

 

[rebeccaogun]

Hi BadgerRoad, thanks for your patience.

Okay I need to use the variable increase as the increase in quantity. But how will excel know that the increase is 1.12 if I am replacing that?

 

[BadgerRoad]

Following is the code that I used:

Function Profit(brand, year, cost, quantity, increase)


 If brand = "MAC" And year = "2012" Then
    Profit = 24 / 100 * cost * quantity * (1 + increase)
    
 ElseIf brand = "Rimmel LONDON" And year = "2012" Then
    Profit = 34 / 100 * cost * quantity * (1 + increase)

 ElseIf brand = "REVLON" And year = "2012" Then
    Profit = 26 / 100 * cost * quantity * (1 + increase)

 ElseIf brand = "MaXFactor" And year = "2012" Then
    Profit = 40 / 100 * cost * quantity * (1 + increase)

 ElseIf brand = "MAC" And year = "2013" Then
    Profit = 22 / 100 * cost * quantity * (1 + increase)

 ElseIf brand = "Rimmel LONDON" And year = "2013" Then
    Profit = 31 / 100 * cost * quantity * (1 + increase)

 ElseIf brand = "REVLON" And year = "2013" Then
    Profit = 23 / 100 * cost * quantity * (1 + increase)

 ElseIf brand = "MaXFactor" And year = "2013" Then
    Profit = 26 / 100 * cost * quantity * (1 + increase)
 End If
 
End Function

Since increase is the 12% portion, rather than 1.12 I am passing 12% rather than 1.12. I then removed 1.12 from the code and included (1+increase)
I put 12% or whatever quantity increase you want to use in cell G2.
When calling the function I used the following:
=Profit(D2, I2, E2, F2, G2)

In order to simplify things further I created a new function that passes the percent profit as follows:

Function ProfitRev(pct_profit, brand, year, cost, quantity, increase)

ProfitRev = pct_profit * cost * quantity * (1 + increase)

End Function

I included percent profit in cell H2 and called the function as follows:
=ProfitRev(H2, D2, I2, E2, F2, G2)

Make sense?