Microsoft Excel - data analysis

Hi    Data.zip

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%

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? 

check this,
http://www.qualitydigest.com/magazine/1997/oct/article/using-excel-data-analysis.html#

That's good. But did not help.

My question is clear. The example I put in here is much simpler compared to what I need to do. I just want your expertise in finding the best way to do it : advise. Thanks.

Cherifa,

Is this part of what you are looking for.

Don
Copy of Data analysis.zip

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%.

Location	Date	Planned Margin	Current Margin
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%

Hi cherifa,

Try this macro..

• Sub MGcal()
  On Error Resume Next
  lastRow = Range("B" & Rows.Count).End(xlUp).Row
  Range("E6").Select
  startRow = ActiveCell.Offset(0, -1).Address
  currentMg = ActiveCell.Value
  
  
  Do Until ActiveCell.Value = ""
  
  
  If ActiveCell.Offset(1, 0) & ActiveCell.Offset(1, -3).Value = currentMg & ActiveCell.Offset(0, -3).Value Then
  ActiveCell.Offset(1, 0).Select
  Else
  endRng = ActiveCell.Offset(0, -1).Address
  ActiveCell.Offset(1, 0).EntireRow.Insert
  AvgMG = WorksheetFunction.Average(Range(startRow & ":" & endRng))
  MedMG = WorksheetFunction.Median(Range(startRow & ":" & endRng))
  ModMG = WorksheetFunction.Mode(Range(startRow & ":" & endRng))
  ActiveCell.Offset(1, 1).Value = AvgMG
  ActiveCell.Offset(1, 2).Value = MedMG
  ActiveCell.Offset(1, 3).Value = ModMG
  newProposeMg = (AvgMG + MedMG + ModMG) / WorksheetFunction.Count(Range("F" & ActiveCell.Offset(1, 0).Row & ":H" & ActiveCell.Offset(1, 0).Row))
      If currentMg + (currentMg * 0.03) >= newProposeMg And currentMg - (currentMg * 0.03) <= newProposeMg Then
      ActiveCell.Offset(1, 4).Value = "Keep"
      Else
      ActiveCell.Offset(1, 5).Value = newProposeMg
      End If
  ActiveCell.Offset(2, 0).Select
  currentMg = ActiveCell.Value
  startRow = ActiveCell.Offset(0, -1).Address
  End If
  Loop
  
  
  End Sub
• attachment for you.--->Vba_Data analysis.zip

 Thanks Pichart. This is really what I was looking for. Thanks again.

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%

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...

• Sub MGcal()
  On Error Resume Next
  lastRow = Range("B" & Rows.Count).End(xlUp).Row
  Range("E6").Select
  startRow = ActiveCell.Offset(0, -1).Address
  currentMg = ActiveCell.Value
  
  Do Until ActiveCell.Value = ""
  AvgMg = Nothing
  MedMG = Nothing
  ModMG = Nothing
  If ActiveCell.Offset(1, 0) & ActiveCell.Offset(1, -3).Value = currentMg & ActiveCell.Offset(0, -3).Value Then
  ActiveCell.Offset(1, 0).Select
  Else
  endRng = ActiveCell.Offset(0, -1).Address
  ActiveCell.Offset(1, 0).EntireRow.Insert
  AvgMg = WorksheetFunction.Average(Range(startRow & ":" & endRng))
  MedMG = WorksheetFunction.Median(Range(startRow & ":" & endRng))
  ModMG = WorksheetFunction.Mode(Range(startRow & ":" & endRng))
  ActiveCell.Offset(1, 1).Value = AvgMg
  ActiveCell.Offset(1, 2).Value = MedMG
  ActiveCell.Offset(1, 3).Value = ModMG
  newProposeMg = (AvgMg + MedMG + ModMG) / WorksheetFunction.Count(Range("F" & ActiveCell.Offset(1, 0).Row & ":H" & ActiveCell.Offset(1, 0).Row))
      If currentMg + (currentMg * 0.03) >= newProposeMg And currentMg - (currentMg * 0.03) <= newProposeMg Then
      ActiveCell.Offset(1, 4).Value = "Keep"
      Else
      ActiveCell.Offset(1, 5).Value = newProposeMg
      End If
  ActiveCell.Offset(2, 0).Select
  currentMg = ActiveCell.Value
  startRow = ActiveCell.Offset(0, -1).Address
  End If
  Loop
  End Sub
• Here new attachment with those 3 lines --->Vba_Data analysis.zip

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.

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...

see you tomorrow

Pichart Y.

Data.zip
Hi Pichart,

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.

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.

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.

Hi Cherifa,

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

pichart Y.

Hi,

Please see the attached file. The mode in this case is 17. Thanks.

Data analysis.zip.zip

Location	Date	Planned Margin	Current Margin
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%

Hi Cherifa,

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

pichart Y.

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.
• and this below is the code...
  •  Sub MGcal()
    On Error Resume Next
    lastRow = Range("B" & Rows.Count).End(xlUp).Row
    Range("E5").Select
    startRow = ActiveCell.Offset(0, -1).Address
    currentMg = ActiveCell.Value
    Do Until ActiveCell.Value = ""
    AvgMg = Nothing
    MedMG = Nothing
    modMG = Nothing
    If ActiveCell.Offset(1, 0) & ActiveCell.Offset(1, -3).Value = currentMg & ActiveCell.Offset(0, -3).Value Then
    ActiveCell.Offset(1, 0).Select
    Else
    endRng = ActiveCell.Offset(0, -1).Address
    ActiveCell.Offset(1, 0).EntireRow.Insert
    AvgMg = WorksheetFunction.Average(Range(startRow & ":" & endRng))
    MedMG = WorksheetFunction.Median(Range(startRow & ":" & endRng))
    modMG = WorksheetFunction.Mode(Range(startRow & ":" & endRng))
    ActiveCell.Offset(1, 1).Value = AvgMg
    ActiveCell.Offset(1, 2).Value = MedMG
    ActiveCell.Offset(1, 3).Value = modMG
    
    NumRng = WorksheetFunction.Count(Range(startRow & ":" & endRng)) / 2
    NumIfRng = WorksheetFunction.CountIf(Range(startRow & ":" & endRng), modMG)
    If NumRng < NumIfRng Then
    newProposeMg = modMG
    Else
    newProposeMg = (AvgMg + MedMG + modMG) / WorksheetFunction.Count(Range("F" & ActiveCell.Offset(1, 0).Row & ":H" & ActiveCell.Offset(1, 0).Row))
    End If
        If currentMg + (currentMg * 0.03) >= newProposeMg And currentMg - (currentMg * 0.03) <= newProposeMg Then
        ActiveCell.Offset(1, 4).Value = "Keep"
        Else
        ActiveCell.Offset(1, 5).Value = newProposeMg
        End If
    ActiveCell.Offset(2, 0).Select
    currentMg = ActiveCell.Value
    startRow = ActiveCell.Offset(0, -1).Address
    End If
    Loop
    
    End Sub
• here is the sample file ---> Vba_Data analysis-Ver3.zip

Try it and let me know your feed back.

Pichart Y.

Nice it is working. Thank you so much.

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


pichart Y

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.