Help to mark Revenue Stores "Good", "Bad" or Nomal" with conditions

nhnn1986

Board Regular
Joined
Oct 12, 2017
Messages
92
Hi all:

I have sheet Revenue with: Column(A) = "Store_code", Column(B) = "Store_name", Column(C) = "Revenue", Column(D) = "Profit"

Now I want to have macro to do that:

with i = 2 to last row
If Average(C2:Ci)>STDEV.P(C2:Ci) then
if Ci-(Average(C2:Ci)+STDEV.P(C2:Ci))>0 then Ei.value = "GOOD"
if Ci-(Average(C2:Ci)-STDEV.P(C2:Ci))<0 then Ei.value = "BAD"
Else Ei.value = "NOMAL"
Else If Average(C2:Ci)<=STDEV.P(C2:Ci) then
if Ci-(Average(C2:Ci)+STDEV.P(C2:Ci))>0 then Ei.value = "GOOD"
if Ci-Average(C2:Ci)<0 then Ei.value = "BAD"
Else Ei.value = "NOMAL"
End if

Please help me to do with code VBA, thanks
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
I think you will want your average and your standard deviation to be over all values, not just the values up to a particular store row, (Average(c2:c(lastrow) and Stdev.P(C2:C(lastrow)). Otherwise, your rating will depend on the position of the store in your list. You can also simplify your IF clause as regardless of whether Stdev is greater than Avg or not, if the revenue is greater than the sum of those two the store is good. If you want your macro to put the results in the tab with your data, you can simply use a cell formula instead of VBA to do the comparison.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

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