How to Calculate "Average %" dependent on Cell Value

sourabh_ajmera

New Member
Joined
Jul 17, 2014
Messages
36
Hello Everybody,

I'll try my best to explain this complex situation. There is data in column A and Column D. Data in column A is account no and data in Column D is some % value.
Each account can have multiple % (please refer table below for example)
Now I am trying to write a code to see if the next cell in Column A is empty, if empty than we know that we have to average the % values in Column D to get the average for the account no. This average value is stored in Column E.
E.g: So for account 12345 average % value from column D shoud be 0.5% only displayed in Column E. But, for 56789 the average should be (1.31+2.67)/2 and so on..

Column A (Account no)Column B...Column C (Random data)Column D (% values)Column E (Average %)
12345xyz0.5%0.5%
56789fgyhd1.31%1.99%
2.67%
84289dghdfg5.4%2.33%
0.034%
1.56%
8936734fjdfsgsjkf4.9%4.9%

<tbody>
</tbody>

Here's the code that I tried but eventually fails at the IsEmpty condition. Please help me for this as I am confused and out of ideas.
Please let me know if I can help further clearing your doubts. Thanks in advance.

Rich (BB code):
Sub Test()
    Dim AvgNIM As Double, AvgNIM1 As Double, AvgNIM2 As Double, FindAvg As Double
    Dim VerifyEmpty As Integer


    For VerifyEmpty = 2 To 100
    Sheets("Rate Calculator").Select
    If (Range("A" & VerifyEmpty).Value = Range("B" & VerifyEmpty).Value) Then
        If Not Range("A" & VerifyEmpty).Value = "" Then
            AvgNIM = Range("D" & VerifyEmpty).Value
        End If
        If Range("A" & VerifyEmpty + 1).Value = "" Then
            AvgNIM1 = Range("D" & VerifyEmpty + 1).Value
            If Range("A" & VerifyEmpty + 2).Value = "" Then
                AvgNIM2 = Range("D" & VerifyEmpty + 2).Value
            End If
        End If


        If Not IsEmpty(AvgNIM) And Not IsEmpty(AvgNIM1) And Not IsEmpty(AvgNIM2) Then     <---- Error (it executes this 'If' block even if the value is empty)
            FindAvg = (AvgNIM + AvgNIM1 + AvgNIM2) / 3
            Range("E" & VerifyEmpty).Value = FindAvg
        ElseIf Not AvgNIM = "" And Not AvgNIM1 = "" Then
            FindAvg = (AvgNIM + AvgNIM1) / 2
            Range("E" & VerifyEmpty).Value = FindAvg
            'End If
        Else
            Range("E" & VerifyEmpty).Value = AvgNIM
        End If
    End If
    Next VerifyEmpty
End Sub
 

WarPigl3t

Well-known Member
Joined
May 25, 2014
Messages
1,599
It is not that complicated. Not at all. It's actually very easy. So easy that the below code I wrote from my head and didn't even have to test it to make sure it works.
Code:
lastRow = Range("D" & Rows.Count).End(xlup).Row
i = 2
Do Until i > lastRow
     If Range("A" & i).value  <> "" Then
          x = Range("D" & i).value
          c = 1
          ii = i + 1
          Do While Range("A" & ii).value = ""
               x = x + Range("D" & ii).value
               c = c + 1
               ii = ii + 1
          Loop
          Range("E" & i).value = x / c
     End If
     i = i + 1
Loop
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,166
IsEmpty only returns meaningful information for variants. It doesn't work well for a numeric data type like Double.

Your AvgNIM# variables are data type Double. IsEmpty will not evaluate them as "empty" as they equal zero or some other numeric value.

Maybe try testing if they equal zero.
 

sourabh_ajmera

New Member
Joined
Jul 17, 2014
Messages
36
Thanks alot WarPiglet....it worked.
Apparently this wasn't as difficult as I thought it is. May be I was over thinking it. Thanks again
 

sourabh_ajmera

New Member
Joined
Jul 17, 2014
Messages
36
Thanks AlphaFrog...Now I can rest assure that my code wasnt that worng. It was just that I was using wrong conventions to test.
Every mistake is a learning process for amatuers like me. Thanks again. Cheers!
 

sourabh_ajmera

New Member
Joined
Jul 17, 2014
Messages
36
Hey WarPiglet and AlphaFrog,

Need your help again, Now I have to add another condition in it.
Column C has 3 values either IN/SW/TD

so now I want to further differentiate between them
E.g: Column A Column C Column D
_____12345_____IN______0.5%
_______________SW_____1.2%
_______________IN______0.14%
_____56789_____TD______3.45%
_______________IN______1.08%

As usual, I am not able to get it :(

Tried something crazy like;
Code:
c = 1
    VerifyEmpty = 2
    Do Until VerifyEmpty > LastRow
        If (Range("A" & VerifyEmpty).Value = Range("B" & VerifyEmpty).Value) And (Range("G" & VerifyEmpty).Value) = "IN" Then
              AvgNIM = Range("D" & VerifyEmpty).Value
              vEmpty = VerifyEmpty + 1
                Do While (Range("A" & vEmpty).Value = Range("A" & VerifyEmpty).Value)
                    AvgNIM = AvgNIM + Range("D" & vEmpty).Value
                    vEmpty = vEmpty + 1
                    c = c + 1
                Loop
              Range("E" & VerifyEmpty).Value = AvgNIM / c
              Range("E" & VerifyEmpty).Select
              Selection.NumberFormat = "0.00%"
              VerifyEmpty = vEmpty + 1
        ElseIf (Range("A" & VerifyEmpty).Value = Range("B" & VerifyEmpty).Value) And (Range("G" & VerifyEmpty).Value) = "SW" Then
              AvgNIM = Range("D" & VerifyEmpty).Value
              vEmpty = VerifyEmpty + 1
                Do While (Range("A" & vEmpty).Value = Range("A" & VerifyEmpty).Value)
                    AvgNIM = AvgNIM + Range("D" & vEmpty).Value
                    vEmpty = vEmpty + 1
                    c = c + 1
                Loop
              Range("E" & VerifyEmpty).Value = AvgNIM / c
              Range("E" & VerifyEmpty).Select
              Selection.NumberFormat = "0.00%"
              VerifyEmpty = vEmpty + 1
        ElseIf (Range("A" & VerifyEmpty).Value = Range("B" & VerifyEmpty).Value) And (Range("G" & VerifyEmpty).Value) = "TD" Then
              AvgNIM = Range("D" & VerifyEmpty).Value
              vEmpty = VerifyEmpty + 1
                Do While (Range("A" & vEmpty).Value = Range("A" & VerifyEmpty).Value)
                    AvgNIM = AvgNIM + Range("D" & vEmpty).Value
                    vEmpty = vEmpty + 1
                    c = c + 1
                Loop
              Range("E" & VerifyEmpty).Value = AvgNIM / c
              Range("E" & VerifyEmpty).Select
              Selection.NumberFormat = "0.00%"
              VerifyEmpty = vEmpty + 1
        Else
            MsgBox "Account numbers do not match from the Report"
            Exit Sub
        End If
    Loop
But all in vain :(
Can you please add this condition to your code. Sorry for the trouble.
Thanks alot again!
 
Last edited:

WarPigl3t

Well-known Member
Joined
May 25, 2014
Messages
1,599
I wasn't completely sure what you meant. Check out the table below to illustrate my points.

A
B
C
D
E
1
(Account no)
random dataIN/SW/TD(% values) (Average %)
2
12345xyz
IN0.50%IN: 0.50%
3
56789fgyhdIN1.31%IN: 1.31%
4
SW2.67%SW: 2.67%
5
84289dghdfgSW
5.40%
SW: 2.72%
6
SW0.03%TD: 1.56%
7
TD1.56%
8
8936734fjdfsgsjkfIN4.90%IN: 4.90%

<tbody>
</tbody>
The output from the vba code is in column E. Take special notice of the orange text in the dataset.
(5.40% + 0.03%) / 2 = 2.72%

Code:
lastRow = Range("D" & Rows.Count).End(xlUp).Row
Range("E2:E" & lastRow).ClearContents
i = 2
Do Until i > lastRow
    xIN = Empty
    xSW = Empty
    xTD = Empty
    If Range("A" & i).Value <> "" Then
        cIN = 0
        cSW = 0
        cTD = 0
        If Range("C" & i).Value = "IN" Then
            xIN = Range("D" & i).Value
            cIN = 1
        ElseIf Range("C" & i).Value = "SW" Then
            xSW = Range("D" & i).Value
            cSW = 1
        Else
            xTD = Range("D" & i).Value
            cTD = 1
        End If
        ii = i + 1
        Do While Range("A" & ii).Value = "" And ii <= lastRow
            If Range("C" & ii).Value = "IN" Then
                xIN = xIN + Range("D" & ii).Value
                cIN = cIN + 1
            ElseIf Range("C" & ii).Value = "SW" Then
                xSW = xSW + Range("D" & ii).Value
                cSW = cSW + 1
            Else
                xTD = xTD + Range("D" & ii).Value
                cTD = cTD + 1
            End If
            ii = ii + 1
        Loop
        If cIN = 0 Then
            cIN = 1
        End If
        If cSW = 0 Then
            cSW = 1
        End If
        If cTD = 0 Then
            cTD = 1
        End If
        o = i
        If xIN <> "" Then
            Range("E" & o).Value = "IN:  " & Format(xIN / cIN, "0.00%")
            o = o + 1
        End If
        If xSW <> "" Then
            Range("E" & o).Value = "SW:  " & Format(xSW / cSW, "0.00%")
            o = o + 1
        End If
        If xTD <> "" Then
            Range("E" & o).Value = "TD:  " & Format(xTD / cTD, "0.00%")
        End If
     End If
     i = i + 1
Loop
 

WarPigl3t

Well-known Member
Joined
May 25, 2014
Messages
1,599
... and actually I think you can delete this part of the code...
Code:
[COLOR=#333333]If cIN = 0 Then[/COLOR]
            cIN = 1
End If        
If cSW = 0 Then
            cSW = 1       
End If        
If cTD = 0 Then
            cTD = 1 [COLOR=#333333]        
End If[/COLOR]
I made modification after creating this code that should render this code obselete. Try running the code without this part of the code and make sure it still works.
 

Forum statistics

Threads
1,082,139
Messages
5,363,363
Members
400,731
Latest member
Jackserver

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top