Forum Discussion

Cliff Fox's avatar
Cliff Fox
Copper Contributor
Jan 18, 2017
Solved

Formula no longer working

Note: The link below has been corrected:

 

The formula in column U is only supposed to produce a result if 5 or more scores have been inserted. It was given to me in this forum, and adds the lowest 3 of the last 5 scores if 5 or more scores exist. It worked fine for several months but as columns have been added as scores have been added, it has begun to add return results even if 3 or 4 scores exist. Can you tell me why and what to do to fix this? See onedrive file: https://1drv.ms/x/s!Auz1HD21AZ7ZhY9Z7oQ50rN4RQe5bA

Formulas & Functions
Need Help
  • Jamil's avatar
    Jamil
    Jan 19, 2017

    Hi Cliff Fox,

     

    please put this formula in U3 and enter with Control + Shift + Enter then drag it down.

     

    =IF(V3<5,"",SUM(SMALL(IF((SUBTOTAL(2,OFFSET(S3,,,1,(COLUMN($B3:$S3)-COLUMN(T3))))<=U$1)*($B3:$S3)=0,FALSE,(SUBTOTAL(2,OFFSET(S3,,,1,(COLUMN($B3:$S3)-COLUMN(T3))))<=U$1)*($B3:$S3)),{1,2,3})))

    this will work, if the scores are lower than 5 it will result blank.

     

    BTW:  it looks like the column headers in row 2 are names of Afghan Calendar months :-)

  • Jamil's avatar
    Jamil
    Bronze Contributor
    Jan 19, 2017

    Hi Cliff Fox,

     

    please put this formula in U3 and enter with Control + Shift + Enter then drag it down.

     

    =IF(V3<5,"",SUM(SMALL(IF((SUBTOTAL(2,OFFSET(S3,,,1,(COLUMN($B3:$S3)-COLUMN(T3))))<=U$1)*($B3:$S3)=0,FALSE,(SUBTOTAL(2,OFFSET(S3,,,1,(COLUMN($B3:$S3)-COLUMN(T3))))<=U$1)*($B3:$S3)),{1,2,3})))

    this will work, if the scores are lower than 5 it will result blank.

     

    BTW:  it looks like the column headers in row 2 are names of Afghan Calendar months :-)

Resources