How to Calculate "Average %" dependent on Cell Value

[sourabh_ajmera]

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 %)
12345	xyz	0.5%	0.5%
56789	fgyhd	1.31%	1.99%
		2.67%
84289	dghdfg	5.4%	2.33%
		0.034%
		1.56%
8936734	fjdfsgsjkf	4.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.

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]

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.

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]

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]

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]

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]

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;

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!

 

[WarPigl3t]

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 data	IN/SW/TD	(% values)	(Average %)
2	12345	xyz	IN	0.50%	IN: 0.50%
3	56789	fgyhd	IN	1.31%	IN: 1.31%
4			SW	2.67%	SW: 2.67%
5	84289	dghdfg	SW	5.40%	SW: 2.72%
6			SW	0.03%	TD: 1.56%
7			TD	1.56%
8	8936734	fjdfsgsjkf	IN	4.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%

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]

... and actually I think you can delete this part of the 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.

 

[sourabh_ajmera]

A-W-E-S-O-M-E!!!!
Thanks alot WarPiglet....I just won the war with your help