IF Formula

Resolved · High Priority · Version 2013

Tue 7 Jan 2014, 09:30 Delegate Jack said...

Jack has attended:
Excel Intermediate course
Excel Advanced course
Excel Advanced course

IF Formula

The IF formula does not give me the desired outcome.
=IF(R10="Completed",4,IF(R10="Attend 1 LLG",3,2))

In short if the text in the cell states "completed" it should give you a value of 4, If "Attend 1 LLG" value of 3 "Attend 2 LLGs" value of 2 however for "Attend 1 LLG" and "Attend 2 LLGs" it always gives a value of 2. I'm not sure what to do

Thanks

Jack

Tue 7 Jan 2014, 13:07 Trainer Doug said...

RE: IF Formula

Hi Jack

Your IF formula looks perfectly correct!

Probably be quickest thing would be to send the file to
info@stl-training.co.uk

If that's not possible you could check for any extra spaces in the data. For example after the text Attend 1 LLG.

If you do find any then modify the formula as follows:

=IF(TRIM(R10)="Completed",4,IF(TRIM(R10)="Attend 1 LLG",3,2))

Regards
Doug Dunn
Best STL

Tue 7 Jan 2014, 16:08 Trainer Doug said...

RE: IF Formula

Hi Jack

I think I've spotted the problem.

Part of the formula in cell F4 has an extra space after LLG.
"Attend 1 LLG "

Once you delete the space at the end the full formula will be:

=IF(B4>=2,"Completed",IF(B4>=1,"Attend 1 LLG","Attend 2 LLGs"))

It then returns...

2 for Attended 2 LLGs
3 for Attended 1 LLG
4 for Complete

Hope that solves the problem!

Doug