Using If statement with a unique ID

[hannah-c]

Hello,

I am having trouble setting up a document. I am currently using Concatenate with an agents name and date to form a unique ID for that week. I am then trying to bring that data into a new sheet which marks the percentage 1-5 based on ranges.

I have attached an example sheet but I can quite work out my 1-5 if statement formula with Unique IDs if anyone has any ideas, I would really appreciate the help.

Thank you!

[Pete_UK]

I presume this is the formula in D2 of Sheet2 that you are talking about:

=IF(DATA!S2>=85%,"5",IF(DATA!S2>=70%,"4",IF(DATA!S2>=60%,"3",IF(DATA!S2>=50%,"2",IF(DATA!S2>=40%,"1")))))

and this is producing some FALSE results down the column. The problem is that you do not have a value_if_false parameter in the final IF function, so you might want to change it to this:

=IF(DATA!S2>=85%,"5",IF(DATA!S2>=70%,"4",IF(DATA!S2>=60%,"3",IF(DATA!S2>=50%,"2",IF(DATA!S2>=40%,"1","")))))

Please note that because you have put quotes around the numbers 5 - 1, these will be text values, so remove the quotes if you want them to be proper numbers.

Hope this helps.

Pete

[hannah-c]

Hi Pete,

Yes I am trying to work out a formula for D2, however, the above and the formula in my example excel doc isn't based off the unique ID. How would I make sure it is only matching it to that?

Thanks,

[Pete_UK]

You need to use this term:

INDEX(DATA!S:S,MATCH(A2,DATA!A:A,0))

instead of this one:

DATA!S2

wherever it occurs in the formula (5 times), OR, you could change the formula in D2 to this:

=IF(INDEX(DATA!S:S,MATCH(A2,DATA!A:A,0))="","",MATCH(INDEX(DATA!S:S,MATCH(A2,DATA!A:A,0)),{0.4;0.5;0.6;0.7;0.85}))

which does the same thing and will be a lot shorter.

Hope this helps.

Pete

[hannah-c]

That worked great. Would this work with a time duration? For example, rather than adherence, using follow up? So 00:30:00 is 5, 01:00:00, 4 and so on.

[Pete_UK]

Note that the numbers in curly brackets, i.e. 0.4, 0.5 etc. are the percentages 40%, 50% etc. which determine the start of each range, and that they are listed in the correct order relating to 1, 2, 3 etc. If you wanted 5 for the shortest time then the "times" would still have to be in order, but you would need a term like:

6-MATCH(...

in order to return 5 for the smallest time. Also, you need to be aware that times in Excel are really fractions of a 24-hour day, so 12 hours is stored internally as 0.5, and 6 hours as 0.25, and so on, so if you wanted 00:30:00 as the first break-point (actually the first number would be zero, as the range would be from zero time up to 00:30:00) then this would be the equivalent of 1/48, or 0.020833333, which is a bit awkward to deal with, and you would have to do this conversion for the other break-points in your ranges.

It might be better to do the conversions on the DATA sheet, and then you can just bring the appropriate data across to the summary sheet.

Hope this helps.

Pete