I have an excel file with four columns : location, futur date, planned margin, and current margin. The margin is for product A. The purpose of this file is to check if the planned margin for product A match or mismatch the current margin. If the planned margin matched the current margin than I need to add a comment saying keep. If the planned margin is different from the current margin than I need to change the current margin. Now which amount I will be using to change the current margin is going to be based on the following formula:
(average + Median+mode)/3

1 - for each location, where the planned margin is within +-3 %, I need to add average, Median, and the mode
2- Add another column named updated margin which will be (average + Median+mode)/4
3. keep the master sheet, and copy all locations with keep comment to a new sheet and the locations with updated margin in another sheet.

I attached a sample file. VBA or excel formula. Thanks.

Planned Margin

Location

Date

Planned Margin

Current Margin

Average

Median

Mode

Collegeville

7/1/2012

17.0%

17%

Collegeville

7/2/2012

18.0%

17%

Collegeville

7/3/2012

16.5%

17%

Collegeville

7/4/2012

17.0%

17%

Collegeville

7/5/2012

17.0%

17%

Collegeville

7/6/2012

19.0%

17%

Collegeville

7/7/2012

17.5%

17%

insert a row and add average, median, and mode for the planned margin for each location where the planned margin is within +-3pts (eg.17%,18%,16.5%,19%..).

Doylstown

7/1/2012

25.0%

24%

Doylstown

7/2/2012

24.0%

24%

Doylstown

7/3/2012

23.0%

24%

Doylstown

7/4/2012

24.0%

24%

We can insert two rows in doylstown locations because there are two planned margins: 24%+-3 and 32% +-3

Doylstown

7/5/2012

24.0%

24%

Doylstown

7/6/2012

32.0%

27%

Doylstown

7/7/2012

33.0%

27%

Doylstown

7/8/2012

35.0%

27%

Doylstown

7/9/2012

30.0%

27%

Doylstown

7/10/2012

32.0%

27%

Fair view

7/1/2012

15.0%

16%

Fair view

7/2/2012

19.0%

16%

Fair view

7/3/2012

12.0%

16%

Fair view

7/4/2012

16.0%

16%

Fair view

7/5/2012

24.0%

24%

Fair view

7/6/2012

26.0%

24%

Fair view

7/7/2012

24.0%

24%

Fair view

7/8/2012

30.0%

30%

Fair view

7/9/2012

32.0%

30%

Fair view

7/10/2012

19.0%

18%

Fair view

7/11/2012

18.0%

18%

Fair view

7/12/2012

17.0%

18%

Fair view

7/13/2012

19.0%

18%

Shan P replied to Cherifa Hima on 20-May-12 10:20 PM

What is your specific question? Are you looking for someone to complete and return your work task to you? Or is there a specific part of your requirement that you're unable to do that you're asking about?

Thanks. you made it sound so easy. Here it is: it is like when you do subtotal in excel by location. What will happen: after each location, we can use average as a subtotal for planned margin. The only difference here is that I need the average, mode, median to be added for each location and within each location check if the planned margin is withtin +-3% and add the average, the mode, and the median like in the example below : the planned margin for each day is within +-3%.

Hi Pichart,
It looks like we have the same mode 24% in the below categories. We should have 24% in the first and no mode in the second ( two different numbers). In the case where there is no mode, then we should exlude it from computation. Thus (average+ median + mode)/3 would become (average+ median )/2. Is it possible to add this?

Thanks.

Fair view

7/5/2012

24.0%

24%

Fair view

7/6/2012

26.0%

24%

Fair view

7/7/2012

24.0%

24%

24.67%

24.00%

24.00%

Keep

Fair view

7/8/2012

30.0%

30%

Fair view

7/9/2012

32.0%

30%

31.00%

31.00%

24.00%

28.67%

Pichart Y. replied to Cherifa Hima on 22-May-12 10:42 AM

Hi Cherifa,

I see, it's my fault, I forgot to clear the value of AvgMG, MedMG and ModMG, so put this 3 lines below do.... like this...

Thanks it worked now.
Is it possible to add the counts we are using to compute the average let's say in column K in the line where we put average. The database is so big and I need to reject the change if the count is less than 3 and if there is too much difference between one date and another within a location. For example:
Location 1
Date
06/12/2012 13%
06/25/2012 80%

For us and from the pricing point of view, we should say keep and not make any changes becuase most likely there is an error. I can add excel formulas to do this I just need the count.
Thanks for your help.

Pichart Y. replied to Cherifa Hima on 22-May-12 12:03 PM

Hi,

yes of course, we can but...

What to be counted, I know to put there, same line with average but count what?

and what is the tolerant, of the too much change...we need to specify.

And we also can say that this line should be "Exempt" or "Ignore Change"..

By the way, feedback in your file with the expected result...I will come back again tomorrow...here in Thailand it is almost midnight...so Good night...

I attached the file with my comments on it. The count is for the numbers of variables for planned margin used within each location to compute the average. Also, I need to be able to adjust the vba code to any type of file. I know I can chnage B, E6, and F. What about the current margin? Is it possbile to add named ranges.

Thanks for your help.

Pichart Y. replied to Cherifa Hima on 25-May-12 12:47 PM

Hi cherifa,

Sorry for late feedback, I am very busy with my work at office :(

by the way, your attachment may be wrong, it was the same with previous one...

for the point of current margin...we can not fix to the range because it will change always when we move to another price range....actually no need to worry about it, the code will always find and store it in the variable for you.

pichart Y.

Cherifa Hima replied to Pichart Y. on 25-May-12 01:09 PM

Thanks for the answer. Is there a way to add a condition in the planned margin so whenever the mode is repeated in half of the variables than we should take it as the adjusted margin and not use the formula. Thanks.

Pichart Y. replied to Cherifa Hima on 26-May-12 04:31 AM

Hi Cherifa,

Please attach your file..., it will be more clear for me to understand.

I have solution for you now, but so busy...possible to give you the answer within this 1-2 days.. :)

pichart Y.

Pichart Y. replied to Cherifa Hima on 08-Jun-12 10:49 PM

Hi Cherifa,

Finished now...

1st plan margin group, there are 7 cells.

I want to easily check this new criteria, so I changed 4 cells of 1st plan margin to be 16%, then with this new code, you will get the 16% instead of 17%

Actually your real file should have only 1 worksheet "Vba", the sheet "BackUpSource" is just the back up of source for testing, you can remove it and of course you can rename the name of sheet "Vba" to any name you want.

In this new I count the number of plan margin compare to the count of mode,

then check if the mode count more than or equal to number of plan margin>2 then we use the mode as new propose margin.

you are welcome. Always see you here when you have problem or question about excel.

pichart Y

Cherifa Hima replied to Pichart Y. on 24-Jul-12 11:54 AM

Hi,

I start using the macro with huge data. I am having some issues with the results. Can you send me your email so I can send you a sample file. My email is: djoghlalnacera@hotmail.com.
Thanks in advance.