Microsoft Excel - data analysis - Asked By Cherifa Hima on 19-May-12 10:14 PM

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%

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? 
Somesh Yadav replied to Cherifa Hima on 21-May-12 12:28 AM
check this,
http://www.qualitydigest.com/magazine/1997/oct/article/using-excel-data-analysis.html#
Cherifa Hima replied to Somesh Yadav on 21-May-12 09:35 AM
That's good. But did not help.
Cherifa Hima replied to Shan P on 21-May-12 09:38 AM
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.
Donald Ross replied to Cherifa Hima on 21-May-12 05:13 PM
Cherifa,

Is this part of what you are looking for.

Don
Copy of Data analysis.zip
Cherifa Hima replied to Donald Ross on 21-May-12 05:34 PM

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%
Pichart Y. replied to Cherifa Hima on 21-May-12 11:47 PM
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
Hope this help.

pichart Y
Cherifa Hima replied to Pichart Y. on 22-May-12 08:46 AM
 Thanks Pichart. This is really what I was looking for. Thanks again.
Cherifa Hima replied to Pichart Y. on 22-May-12 09:12 AM

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

  • 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
Hope it is fine now.

Let me know your feedback.

Pichart Y.
Cherifa Hima replied to Pichart Y. on 22-May-12 11:49 AM
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...

see you tomorrow

Pichart Y.
Cherifa Hima replied to Pichart Y. on 22-May-12 01:25 PM
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.
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.

pichart Y.
Cherifa Hima replied to Pichart Y. on 04-Jun-12 09:26 AM
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%
Pichart Y. replied to Cherifa Hima on 06-Jun-12 09:37 PM
Hi Cherifa,

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.
  • 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.
Cherifa Hima replied to Pichart Y. on 11-Jun-12 12:41 PM
Nice it is working. Thank you so much.

Pichart Y. replied to Cherifa Hima on 12-Jun-12 11:13 AM
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.