simple percentage increase to the quantity in VB

rebeccaogun

New Member
Joined
May 21, 2013
Messages
4
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 :)
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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
 
Upvote 0
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.
 
Upvote 0
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 :)
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
Following is the code that I used:
Code:
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:
Code:
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?
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top