Hi guys,
I'm having some difficulty coming up with how to average all (non-blank) cells within the 1st percentile of my data array.
As a more concrete example; in an investment scenario, I basically want to know what the average negative return is within the worst 1% of monthly returns (using monthly returns data).
I used: =AVERAGEIF('1b. Import Morningstar Data'!AF2:HL2,('1b. Import Morningstar Data'!AF2:HL2<PERCENTILE.INC('1b. Import Morningstar Data'!AF2:HL2,0.01))) but this returns #DIV/0!. So I'm thinking I'm perhaps doing something wrong about not accounting for blank cells. I want to ignore the blank cells.
Can you guys help me ?
Thank you!
I'm having some difficulty coming up with how to average all (non-blank) cells within the 1st percentile of my data array.
As a more concrete example; in an investment scenario, I basically want to know what the average negative return is within the worst 1% of monthly returns (using monthly returns data).
I used: =AVERAGEIF('1b. Import Morningstar Data'!AF2:HL2,('1b. Import Morningstar Data'!AF2:HL2<PERCENTILE.INC('1b. Import Morningstar Data'!AF2:HL2,0.01))) but this returns #DIV/0!. So I'm thinking I'm perhaps doing something wrong about not accounting for blank cells. I want to ignore the blank cells.
Can you guys help me ?
Thank you!
