CODEWITHSUNDEEP
vbaexcel
Rich Prag
Rich Prag

Reputation: 133

VBA alternative to Application.Quotient that includes decimals

I am trying to use Application.AverageIfs and I need to divide the answer by 3. I tried this way: 

Range("C1:C676") = (Application.IfError(Application.AverageIfs(Sheets(modelName).Range("R:R"....
                    Sheets(modelName).Range("U:U"), "OGV"), "0") / 3)

and also without the brackets round the first application and the final 3 but this gives a type mismatch error.

Nesting it within Application.Quotient works but it only gives the integer part of the answer and I need the decimal as well. Is there a decimal-friendly alternative? I would prefer to continue to use the application syntax rather than putting range().formula = "=averageifs( if its possible.

Edit: after J.Fox's suggestion I have broken the formula parts into variables. The problem seems to be the criZFrom and criZTo variables which use a range in a separate sheet as criteria. The formula works fine if I replace these variables with "1" and "2" respectively. The code is now:

Set rng = Sheets(wsName).Range("C1:C676")
    Set avgCol = Sheets(modelName).Range("M:M")
    Set colZFrom = Sheets(modelName).Range("G:G")
    Set criZFrom = Sheets(wsName).Range("A1:A676")
    Set colZTo = Sheets(modelName).Range("H:H")
    Set criZTo = Sheets(wsName).Range("B1:B676")
    Set colTime = Sheets(modelName).Range("V:V")
    Set colVType = Sheets(modelName).Range("U:U")

    criVType = "OGV"
    criAM = "AM"

        Range("A1:A676").Formula = "=roundup(row()/26,0)"

        Range("B1:B676").Formula = "=if(mod(row(),26)=0,26,mod(row(),26))"

            rng = Application.AverageIfs(avgCol, colZFrom, criZFrom, colZTo, criZTo, colTime, criAM, colVType, criVType) / 3

Here is some sample data:

from sheets(modelName), this has the data that I am trying to average and most of the criteria ranges: enter image description here

From sheets(wsName), this has the criteria for the problem variables and is where I want the result to appear (in column C): enter image description here

Upvotes: 1

Views: 316

Answers (1)

barvobot
barvobot

Reputation: 897

Looks like you're missing a closing parenthesis after "OGV") to close out the AverageIfs function, i.e.:

Range("C1:C676") = Application.IfError(Application.AverageIfs(Sheets(modelName).Range("R:R", Sheets(modelName).Range("U:U"), "OGV")), 0) / 3

Also, not sure if .... was just for on here or in your code, but you'd want to use _ instead, as in:

Range("C1:C676") = _ 
Application.IfError(Application.AverageIfs(Sheets(modelName).Range("R:R", _
Sheets(modelName).Range("U:U"), "OGV")), 0) / 3

Edit: If you're still getting an error, I suggest breaking up your formula into component parts and assigning each part to a variable so you can troubleshoot exactly where the issue is, like so:

Sub test()
Dim rng As Range, col1 As Range, col2 As Range, str As String, modelName As String

modelName = "Sheet1"

Set rng = Range("C1:C676")
Set col1 = Sheets(modelName).Columns(18)
Set col2 = Sheets(modelName).Columns(21)
str = "OGV"

rng = Application.IfError(Application.AverageIfs(col1, col2, str), 0) / 3

End Sub

Can we see some sample data? It might be an issue of what order the arguments are being passed for the AverageIfs function.

Edit 2: I think I might see what the problem is. You're using the AverageIfs function with the intention of validating each line separately based on the specific criteria for each line by using a range for Arg3 and Arg5 instead of single values, which AverageIfs doesn't like. Criteria for Ifs functions will always need to be a single value instead of a range of values. Instead, I think you would need to iterate each line separately using a loop, like this:

Set avgCol = Sheets(modelName).Range("M:M")
Set colZFrom = Sheets(modelName).Range("G:G")
Set colZTo = Sheets(modelName).Range("H:H")
Set colTime = Sheets(modelName).Range("V:V")
Set colVType = Sheets(modelName).Range("U:U")

criVType = "OGV"
criAM = "AM"

Range("A1:A676").Formula = "=roundup(row()/26,0)"
Range("B1:B676").Formula = "=if(mod(row(),26)=0,26,mod(row(),26))"

Dim x as Long
Dim t as Variant

For x = 1 To 676
    Set criZFrom = Sheets(wsName).Range("A" & x)
    Set criZTo = Sheets(wsName).Range("B" & x)
    Set Rng = Sheets(wsName).Range("C" & x)

    t = Application.WorksheetFunction.AverageIfs(avgCol, colZFrom, criZFrom.Value, colZTo, criZTo.Value, colTime, criAM, colVType, criVType)
    t = CDbl(t / 3)
    Rng.Value = t
Next x

Upvotes: 1

Related Questions