Average multiple MIC correctly if one is missing

[former_member1101352]

Hi,

I have a process that generates either 2 or 3 values depending on the specific lot. I need to track these values separately, but also need to have an average of all the valid results recorded. Of course I could have the 3 separate line items and 1 for entering all the items collected a 2nd time for the averaging, but it will result in duplicate entries by an operator and may cause typing errors if entered the same data in multiple places; or if they need update something and it is only updated in 1 place.

If all 3 values are entered then the problem is simple and I can use the formula: (C00010 + C00020 + C00030)/3. I tried to generalize this to account for the case where I have only 2 entries, but it does not work for 2 entries, only if there are all 3 entries: (C00010 + C00020 + C00030)/(C10010 + C10020 + C10030).

My logic was if there is all 3 valid entries, then the denominator would sum to 3 (i.e. 1 + 1 + 1), and if only 2 valid entries it would sum to 2 (e.g. 1 + 1 + 0). I think that the denominator behavior is working correctly, but the numerator behavior is not working correctly. If I force close line 0030 with no value then SAP gives a formula error. I can only assume that it is trying sum numeric values (lines 0010 and 0020) with a non-numeric type (line 0030 with no value). Is this a correct assumption of the error behavior in the numerator of the formula?

Does anyone have a solution to average only lines with values?

Regards,

Joe

[former_member34]

Thank you for visiting SAP Community to get answers to your questions. Since you're new in asking questions here, I recommend that you familiarize yourself with https://community.sap.com/resources/questions-and-answers (if you haven't already), as it provides tips for preparing questions that draw responses from our members.

Should you wish, you can revise your question now by selecting Actions, then Edit.

Finally, if you're hoping to connect with readers, please consider adding a picture to your profile. Here's how you do it: https://www.youtube.com/watch?v=F5JdUbyjfMA&list=PLpQebylHrdh5s3gwy-h6RtymfDpoz3vDS . By personalizing your profile with a photo of you, you encourage readers to respond.

Best regards

Jennifer

SAP Community moderator

[sourabh_maheshwari2 · Accepted solution]

Maybe you can just keep one MIC (Single Results) and enter the results as necessary (2 / 3 or more)

You can find the individual values in QASE and the summarized ones in QAMR/QASR

with proper reporting, you can still track the results individually

PS: It will be helpful if you can attach a screenshot of the error you get (including the detailed error)