formula for calculating quintiles if there is a value in adjecent cell

**Daniello1** · 08-31-2012, 04:01 AM

Hi.
I have a set of data in a table: names in column A (all cells are filled), and respective datapoints in two columns B and C.
Some detapoints in B and in C are missing (cells are empty but with linking formula that returns blank cells). Important - empty cells in B and C are in different rows.
I need to calculate average by quintiles (and by quartiles and terciles) of values in column B, but only for those datapoints, that do have data in both B and C column. So to (a) disregard the data in B cell, if C cell in the same row is empty and also (b) to disregard the empty cells in B.
Doable? Please help!
Daniello1

**zbor** · 08-31-2012, 04:02 AM

Can you upload example workbook with your inputs and desired output?

**Daniello1** · 08-31-2012, 04:33 AM

Hi zbor

Here it is - I converted links to data (as file was very heavy), so in original file you would have links to cells in another spreadsheet (so all blank cells would have formulas in it).
In original file there is >1000 rows and I have ca 120 identical files to run this, so need automatization.

What I need to do is to put companies (ISIN) into 3, 4 or 5 same-sized subgroups (terciles, quartiles and quintiles), ranked from highest to lowest % sales change yoy (column C) and than to calculate average return per each subgroup (column D) and also average sales change yoy in each subgroup.
But I need to disregard all rows, where one of data (C or D) is missing. Afterwards, I would like to do the same but within each sector separatelly.
Thanks for any help !

**zbor** · 08-31-2012, 05:01 AM

You didn't put your expected result so do you mean like this:

=IF(COUNT(C6:D6)=2, C6*D6/5, "")

**Daniello1** · 08-31-2012, 05:20 AM

sorry, youre right.
Its not exactly what I needs - please see revised file attached, with the desired result.
Only rows with data in both C and D are taken into calculations.
Thanks !