Forum Discussion

Iron Contributor

Re: AVERAGE and GET RID Off ZEROs

I use IFERROR to get rid of #VALUE for rank:

I also noticed you used nested if to convert number grade into "Appréciations", I think you can simplify that by using XLOOKUP with -1 as match mode:

https://support.microsoft.com/en-au/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929

For RANK and MISSING in report card, I also use INDIRECT. (similar to class average):

RACHEL-CHOOSE-which-TEST-to-DISPLAY-HELP-ANSWER---FOLLOWING-RANK.xlsx178 KB

44 Replies

Boulakgnobot
Brass Contributor
Dec 26, 2023
rachel Thanks !

Have a problem with how to CALCULATE "Annual AVERAGE" (the 3 Tests AVERAGE)

IMAGE below:

File link below:
https://www.cjoint.com/c/MLAtXUk72pI
- rachel
  Iron Contributor
  Dec 27, 2023
  Boulakgnobot
  
  I updated the formula to calculate annual average:
  
  RACHEL-CHOOSE-which-TEST-to-DISPLAY-HELP-ANSWER---FOLLOWING-RANK-CALCULATE-ANNUAL-AVERAGE.xlsx190 KB
  - Boulakgnobot
    Brass Contributor
    Dec 27, 2023
    rachel Thanks !
    
    1- Here, I want to KEEP FORMULA in (D10:D21) but :
    (E10) & (G10) and (E17) & (G17) to be EMPTY because (D10) and (D17) have NO NOTES
    
    IMAGE below:
    
    2- Because some of the student does not ALL the TEST (some missings), I want also "ToTal Maxi" (E23) to SUM(E10:E21) taking in compte NOTE = O Like in (D14) & (D20) and EMPTY NOTES like in (D10) & (D17)
    
    FILE link below:
    https://www.cjoint.com/c/MLBpFjRvKcD

Resources