How to Calculate "Average %" dependent on Cell Value

S

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

[TABLE="class: outer_border, width: 600"]
<tbody>[TR]
[TD]Column A (Account no)[/TD]
[TD]Column B...Column C (Random data)[/TD]
[TD]Column D (% values)[/TD]
[TD]Column E (Average %)[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]xyz[/TD]
[TD]0.5%[/TD]
[TD]0.5%[/TD]
[/TR]
[TR]
[TD]56789[/TD]
[TD]fgyhd[/TD]
[TD]1.31%[/TD]
[TD]1.99%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]2.67%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]84289[/TD]
[TD]dghdfg[/TD]
[TD]5.4%[/TD]
[TD]2.33%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0.034%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]1.56%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8936734[/TD]
[TD]fjdfsgsjkf[/TD]
[TD]4.9%[/TD]
[TD]4.9%[/TD]
[/TR]
</tbody>[/TABLE]

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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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:
Upvote 0
I wasn't completely sure what you meant. Check out the table below to illustrate my points.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD] (Account no)
[/TD]
[TD]random data[/TD]
[TD]IN/SW/TD[/TD]
[TD](% values)[/TD]
[TD] (Average %)
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: right"]12345[/TD]
[TD]xyz
[/TD]
[TD]IN[/TD]
[TD="align: right"]0.50%[/TD]
[TD]IN: 0.50%[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: right"]56789[/TD]
[TD]fgyhd[/TD]
[TD]IN[/TD]
[TD="align: right"]1.31%[/TD]
[TD]IN: 1.31%[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD]SW[/TD]
[TD="align: right"]2.67%[/TD]
[TD]SW: 2.67%
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: right"]84289[/TD]
[TD]dghdfg[/TD]
[TD]SW
[/TD]
[TD="align: right"]5.40%
[/TD]
[TD]SW: 2.72%[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD]SW[/TD]
[TD="align: right"]0.03%[/TD]
[TD]TD: 1.56%
[/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD][/TD]
[TD][/TD]
[TD]TD[/TD]
[TD="align: right"]1.56%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD="align: right"]8936734[/TD]
[TD]fjdfsgsjkf[/TD]
[TD]IN[/TD]
[TD="align: right"]4.90%[/TD]
[TD]IN: 4.90%[/TD]
[/TR]
</tbody>[/TABLE]
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
 
Upvote 0
... 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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
How to calculate annual average growth (i.e., price appreciation) rate for an investment portfolio.
Replies
3
Views
107
kvsrinivasamurthy
K
N
  • Solved
how to automatically clear text if text in another cell is removed
Replies
1
Views
107
Joe4
Joe4
K
  • Question Question
how to copy above cell values down
Replies
5
Views
340
kamranyd
K
P
  • Question Question
How to obtain average values on columns filter
Replies
3
Views
207
StephenCrump
S
Agnarr
  • Solved
Calculation Problem VBA
Replies
1
Views
545
Joe4
Joe4

Forum statistics

Threads
1,223,705
Messages
6,173,996
Members
452,542
Latest member
Bricklin

Share this page

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