# How to Calculate "Average %" dependent on Cell Value

#### sourabh_ajmera

##### New Member
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.

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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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``````

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.

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

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!

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
Thanks alot again!

Last edited:
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%

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

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

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

Replies
5
Views
293
Replies
3
Views
166
Replies
1
Views
505
Replies
5
Views
362
Replies
1
Views
170

### Forum statistics

1,221,052
Messages
6,157,630
Members
451,426
Latest member
VinnyDoesntKnowExcelCode

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

### Which adblocker are you using?

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

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