IF and And Help?

[mkuter]

Hey again everyone and anyone.

I am trying to come up with a little automation for my sheet and this is essentially what I am trying to do.

I have a drop down to select a certain status (More Info Needed, Scheduled, Testing, Completed). Essentially what I want to do is I want to be able to select a status and have it automatically put a value in for an overall status percentage. So More Info Needed would be 25%, Scheduled would be 50%, Testing would be 75% and Completed would be 100%.

I managed to do this and get it for the 100% =IF([@[Dev Status]]="Completed",1,0) . I also tried adding some and statements in there and that is where the wheels came off.

Any help would be appreciated.

 

[Special-K99]

=LOOKUP(A1,{0.25,0.50,0.75,1.0},{"More Info Needed","Scheduled","Testing","Completed"})

 

[gaz_chops]

Try using a lookup

=LOOKUP(A1,{"Completed",1;"More Info Needed",0.25;"Scheduled",0.5;"Testing",0.75})

 

[gaz_chops]

=LOOKUP(A1,{0.25,0.50,0.75,1.0},{"More Info Needed","Scheduled","Testing","Completed"})

Think thats the wrong way round.

 

[Special-K99]

Entering 0.53 in A1
produces
0.5 from your reply
and "Scheduled" from mine

I'm sure mine's correct, I've always done it in that format.

 

[gaz_chops]

Entering 0.53 in A1
produces
0.5 from your reply
and "Scheduled" from mine

I'm sure mine's correct, I've always done it in that format.

Mmm maybe I've misread it! Thought OP wanted to return the value from the status?

 

[Special-K99]

Oops, I think you're right!
I need to clean my glasses!

All the same, shouldn't that now be

=LOOKUP(A1,{"Completed","More Info Needed","Scheduled","Testing"},{1.0,0.25,0.50,0.75})

ie descriptions before the numbers ?
Or is it different because you've got semi colons ?

 

[gaz_chops]

Oops, I think you're right!
I need to clean my glasses!

Should have gone to Specsavers! I did last week

 

[mkuter]

Oh wow!! Thank you so much!! I had to make some changes as I couldn't get it to work the way you had it Special-K99 but I am still having some issues.

Right now the formula looks like this =LOOKUP(F2,{"Select","More Info Needed","Scheduled","Testing","Completed"},{0,0.25,0.5,0.75,1})

When I select Select or Scheduled I get 50%, (Select I am trying to have at 0) when I select Testing I get 75%, but when I select More Info Needed and Completed I get a #N/A error. Any thoughts?

 

[gaz_chops]

You need to make sure the 1st part is in alpha order

=LOOKUP(F2,{"Completed","More Info Needed","Scheduled","Select","Testing"},{1,0.25,0.5,0,0.75})