formula to average GI results grid/data field columns

I think I know the issue -- the values in the combo are 1-5 but are being treated as char/text and not ints. Is there a way to convert these to integers and then sum and average them?

Sum() is an aggregate function, which is why you’re getting that error. Try this:

=(CInt([VendorR.AttributeVSCOREDELV])+CInt([VendorR.AttributeVSCOREPRIC])+CInt([VendorR.AttributeVSCOREQUAL])+CInt([VendorR.AttributeVSCORERESP])+CInt([VendorR.AttributeVSCORETAT])+CInt([VendorR.AttributeVSCOREVARI]))/6

Notice that the additions are combined with () around them to indicate the addition should happen before the division. This is important as well.

=(CInt([VendorR.AttributeVSCOREDELV])+CInt([VendorR.AttributeVSCOREPRIC])+CInt([VendorR.AttributeVSCOREQUAL])+CInt([VendorR.AttributeVSCORERESP])+CInt([VendorR.AttributeVSCORETAT])+CInt([VendorR.AttributeVSCOREVARI]))/6

Ahhh, makes sense! But I get an error 406 when I save… need to look at this deeper.

You could try without converting to int, but make sure the block of additions is enclosed in parenthesis to force that to calculate before dividing.

Yeah removing the Cint eliminated the 406 but it’s still not averaging correctly. Dang it.

If you remove the division, does it sum properly?

Ahh, didn’t think to try that. No, doesn’t sum… it just concatenates the chars from the score:

Try this: 

=(CInt(CStr([VendorR.AttributeVSCOREDELV]))

If that works, try this:

=CInt(CStr([VendorR.AttributeVSCOREDELV]))+CInt(CStr([VendorR.AttributeVSCOREPRIC]))

The first one threw a 406. :/

I’m wondering if there’s a way… it’s trying to ‘math’ the description, when the value id (I’m guessing) is an actual int. So if there’s a way for me to display the attibute value id and not the description it may sum and average properly.

Dude it worked!

This test you had me do had an extra leading paren (

=(CInt(CStr([VendorR.AttributeVSCOREDELV]))

When I removed that, I did this:

=CInt(CStr([VendorR.AttributeVSCOREDELV]))+10

And got this:

The Value ID is a string also. In my testing, I can convert a combo attribute to an int, although it converts the Value ID, not the Description.

=CInt([Customer.COMPSIZE_Attributes])+1

Dude it worked!

This test you had me do had an extra leading paren (

=(CInt(CStr([VendorR.AttributeVSCOREDELV]))

When I removed that, I did this:

=CInt(CStr([VendorR.AttributeVSCOREDELV]))+10

And got this:

 

The error comes in when wrapping the () to get it to divide /6 properly. Now I think I can just cheat, make a total score column that is hidden, and then just divide that hidden field by 6 for a visible Avg Score