Excel query

Lleucu

Hi hopefully simple excel query.

I am building several sheets to monitor key performance indicators for a project.

One indicator is that the project should have 4 project meetings a year. I have all the relevant dates.

How do I input the dates so that it calculates whether the outcome was achieved?

I hope to create a dashboard so that all the KPIs can be summarised on a front sheet.

Thanks

ThemeOne

I can't quite follow the scenario you're outlining, but have you looked at IF statements?

bod1467

How are you planning to record that any given meeting actually happened? Have 4 cells where the meeting date is entered each time it happens? Example:

Formula is in cell A2. Cells B2, C2, D2 and E2 contain the dates of the 4 meetings once they are held.

=IF(ISBLANK(B2),"NO",IF(ISBLANK(C2),"NO",IF(ISBLANK(D2),"NO",IF(ISBLANK(E2),"NO","YES"))))

If any of B2 to E2 are blank then NO is returned. If ALL have data in them then YES is returned. (Code tags because the forum is inserting random spaces).

AndyPix

What is the indicator of the outcome being achieved ?

Is that information in a cell somewhere , or are you trying to calculate it ?

If the latter, then what are the deciding factors ? and are they in cells somewhere ?

You haven't given us much to go on .....

Lleucu

I think the answer has been given sorry not to have given enough detail.

The kpi is 'to have 4 project meetings each year' the target is 100 per cent, therefore 3 meetings would be 75 per cent.

I guess the if suggestion would work so I will try it. thanks everybody

grumbler

Lleucu wrote: »

One indicator is that the project should have 4 project meetings a year. I have all the relevant dates.

How do I input the dates so that it calculates whether the outcome was achieved?

How about using COUNTIFS(range, criteria)>=4?
For criteria use something like, >2014 and <2016 if you have the year in separate cells.
If it's the full dates, then it can be done too, but a little more complicated.

bod1467

Simplified version ...
A2 has the KPI formula

=C2+E2+G2+I2

B2 has the date for meeting 1
C2 has a check for if meeting 1 happened

=IF(ISBLANK(B2),0%,25%)

D2 has the date for meeting 2
E2 has a check for if meeting 2 happened

=IF(ISBLANK(D2),0%,25%)

F2 has the date for meeting 3
G2 has a check for if meeting 3 happened

=IF(ISBLANK(F2),0%,25%)

H2 has the date for meeting 4
I2 has a check for if meeting 4 happened

=IF(ISBLANK(H2),0%,25%)

(You could hide columns C, E, G and I).

As each meeting happens then the KPI value will rise from 0%, 25%, 50%, 75% and then 100%.

Lleucu

thanks bod1467 will try this it looks really straightforward!!