Excel Lookup Formula required, I think.l

[FANNINGT]

Hi everyone,

Just trying to build a excel report. It’s a simple enough (i think) team performance output. However I am struggling with creating a formula that will lookup Sheet2 and give me a true score for

% Completed >3 Target
% Completed >3 Actual


E.g. Keith Roche should be on 100% as he has reached two Level3 modules by 05/12/15.

Hope I have explained myself correctly, look forward to the response.

Thanking you all kindly.

[Richard Buttrey]

Hi,

It's a little difficult to know how and which numbers that are in the workbook you think should be used to calculate 100% in the example you give.
Please indicate which cells on which sheet constitute the numerator and which the denominator.
At the moment your G14+G16 denominator looks suspect since G16 includes the values 1 & 2 which are also included in the G14 number. i.e. you're double counting.

[FANNINGT]

Hi richard,

Thank you for your response. Sheet2 is really the training plan and Team01 is the actual training assessment competency (1-4, the data is captured and entered manually. They are 9 assessments to complete,however each team member needs to have at least 4 assessments completed to a competency level of 3 within a 12 month period. I'm trying to create metrics that are a true reflection of each team members performance.

I was trying to work out a formula that would check the records captured on Team01 and lookup Sheet2 to see if on target.

I hope i'm making sense.

Regards
Tom

[Richard Buttrey]

Hi Tom,

But can you explain how you calculate 100% for Keith. i.e tell me which cells are involved in the calculation and why you believe those meet whatever criteria is necessary to get 100%. Where does the date of 5/12/2015 enter the equation and what difference would some other date make?

[FANNINGT]

Hi Richard,

I'll start again, i was trying to run before i could walk :-).

See attached Spreadsheet Book1.
What formula do i need to enter in the cell highlighted in yellow, the answer for Ashley is 3 and the answer for Mike is 2. I need a formula to query the current date against planned completion date.

Thanks again Tom

[Richard Buttrey]

Hello Mike,

There was no highlighted yellow cell but I'll assume you're asking how you get the value 3 for Ashley in D17.

When you mention the 'current date' do you mean the current system date of the date in C15. I know that today they are both the same but tomorrow they won't.

So assuming you are wanting to compare the C15 date against dates in E5:E10 how do you want this to work. According to the Table all 4 of Ashley's modules were planned to be completed by the latest date 28 Sept in E8. How therefore do you calculate 3?

[FANNINGT]

Hello,

Not sure if the file was corrupt, but E8 should read 26/01/15. If i use the date in Cell C:15 (today()) to query the list E5:E10 then i should get 3.

Just want to run a query using the variable date in C15 against dates in E5:E10 for each person.

Regards
Thanks