Populate a cell percentage based on amount of dates in a range of cells.

[nathandavies]

Hi All,
i'm after some help on a formula i cannot seem to get it to work correctly.

This is what i'm trying to achieve.

If there is a date (DD/MM/YYYY), In a range of cells (AF16:AK16) it then looks at what column is populated and returns a percentage based on the column title (AF15:AK16) to a cell (AE16)

Can anyone assist?

[p45cal]

I'm not at all clear; perhaps a small workbook with sample data and expected rersults?

[nathandavies]

Hi P45cal,

i have created a workbook with the example data as requested.

The column i want to populate automatically is Column B, this will be populated based on what cells are populated in Columns C:H

[p45cal]

In the attached is a simplistic offering, but it can be developed, of course.
I've added data in A6:A12 and formulae in B16:B23 which is:
=VLOOKUP(COUNT($C16:$H16),$A$6:$B$12,2,FALSE)
in cell B16 copied down.
COUNT counts the numbers of cells in a range which contain numbers (dates are numbers - but so are plain numbers, so be aware).
Your expected results were a bit odd, as there were two rows with the same dates but different values in column B.
Do you have to take account of whether the dates are in the future or not?

[nathandavies]

Hi Pascal,
I don't need to take into account the what the date is, just that there is a number in there.

Your example works great, but it doesn't take into account the column headings.

Each percentage is related to the column heading, so it should only go to that percentage if that column and all the has a date.

To Get to 10% it would require a date in Column F
To get to 25% it would require a date in Column D, Column F
To get to 50% it would require a date in Column D, Column F, Column H
To get to 75% it would require a date in Column D, Column E, Column F, Column H
To get to 90% it would require a date in Column D, Column E, Column F, Column G, Column H
To get to 100% it would require a date in Column C, Column D, Column E, Column F, Column G, Column H

I hope this makes a bit more sense, apologies i don't think i explained it very well to start with.

ND

[p45cal]

OK. A longish and lazy formula which adheres to your conditions in your last message for you to test. In cell B16:

=IF(COUNT($C16:$H16)=6,1,IF(COUNT($D16:$H16)=5,0.9,IF(COUNT($D16:$F16,$H16)=4,0.75,IF(COUNT($D16,$F16,$H16)=3,0.5,IF(COUNT($D16,$F16)=2,0.25,IF(COUNT($F16)=1,0.1,0))))))

copied down.
There's no need for the numbers in A6:A12 anymore.
See attached.
I haven't got time to look right now, but I will look into making it shorter by trying to find a bit more logic behind the results.

[p45cal]

but I will look into making it shorter by trying to find a bit more logic behind the results.

but not without feedback.

[nathandavies]

Hi Pascal,

Sorry for the delay in getting back to you.

I have tried your formula and it worked perfectly, i have used the formula in a few different places already.

Thank you for your continued help on this!

ND