• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Expanding if is number search formula

B

bnpdkh

Member
Looking for help to expand the formula below; I am trying to add to formula so that if "S" is located in F7,F8, or F9, 8 will be returned instead of the 12. I have tried several time but it does not work.


Code: 
=IF(ISNUMBER(SEARCH("*S SHIFT*",$D100)),IF(ISNUMBER(SEARCH("*D1*",F$7)),12*$HS100,0),IF(ISNUMBER(SEARCH("*T SHIFT*",$D100)),IF(ISNUMBER(SEARCH("*D1*",F$8)),12*$HS100,0),IF(ISNUMBER(SEARCH("*U SHIFT*",$D100)),IF(ISNUMBER(SEARCH("*D1*",F$9)),12*$HS100,0),IF($D100=0,0,0))))
 
Hi ,

Can you clarify a couple of things ?

1. The following segment of the formula is not doing anything :

IF($D100=0,0,0)

What this tells Excel is , if D100 equals 0 , put 0 in the cell where this formula has been entered , otherwise put 0.

Thus whether D100 equals 0 or not , the cell gets to have 0 in it.

2. At present , your formula has 3 separate segments to it :

  • IF(ISNUMBER(SEARCH("*S SHIFT*",$D100)),IF(ISNUMBER(SEARCH("*D1*",F$7)),12*$HS100,0)
  • IF(ISNUMBER(SEARCH("*T SHIFT*",$D100)),IF(ISNUMBER(SEARCH("*D1*",F$8)),12*$HS100,0)
  • IF(ISNUMBER(SEARCH("*U SHIFT*",$D100)),IF(ISNUMBER(SEARCH("*D1*",F$9)),12*$HS100,0)
Where do you wish to introduce the new check for :

if "S" is located in F7,F8, or F9
Click to expand...

3. What is the meaning of :

8 will be returned instead of the 12
Click to expand...

I think a little more clarity is required.

Narayan
 
Ok, there are three different crews S,T, and U. Their work schedule is loaded into rows 7,8,and 9 respectively. The hours they work in their schedule varies, if D1 is selected they are scheduled for 12 hours, if S is selected they are scheduled for 8 hours, and if R is selected they do not have any hours scheduled. I inherited this spreadsheet and am trying to clean it up.
So, I have a formula that works individually for each crew but I am unable to properly combine them together. This is what I need help with.

This works fine for S shift
Code: 
=IF($E101="S SHIFT",IF(G$8="D1",12*$NR101,IF(G$8="S",8*$NR101,IF(G$8="R",0))))

I need to properly add this code;

Code: 
=IF($E101="T SHIFT",IF(G$9="D1",12*$NR101,IF(G$9="S",8*$NR101,IF(G$9="R",0))))

and this code

Code: 
=IF($E101="U SHIFT",IF(G$10="D1",12*$NR101,IF(G$10="S",8*$NR101,IF(G$10="R",0))))
 
Hi ,

Try this :

=IF($E101="S SHIFT",IF(G$8="D1",12*$NR101,IF(G$8="S",8*$NR101,IF(G$8="R",0))),IF($E101="T SHIFT",IF(G$9="D1",12*$NR101,IF(G$9="S",8*$NR101,IF(G$9="R",0))),IF($E101="U SHIFT",IF(G$10="D1",12*$NR101,IF(G$10="S",8*$NR101,IF(G$10="R",0))))))

Narayan
 
This is exactly what I have just tried and it works when S SHIFT and T SHIFT are entered in E101 but "FALSE" is returned when U SHIFT is enterd in E101. I am cross eyed from staring at this for so long!!!!!
 
Hi ,

I don't think so ; I have verified all the cases.

I am posting the formula once more :
Code: 
=IF($E101="S SHIFT",IF(G$8="D1",12*$NR101,IF(G$8="S",8*$NR101,IF(G$8="R",0))),
IF($E101="T SHIFT",IF(G$9="D1",12*$NR101,IF(G$9="S",8*$NR101,IF(G$9="R",0))),
IF($E101="U SHIFT",IF(G$10="D1",12*$NR101,IF(G$10="S",8*$NR101,IF(G$10="R",0))))))
Narayan
 
I have no idea why but it seems like the issue was with the name "U SHIFT". I moved "U SHIFT" to where "S SHIFT" was in the forrmula and still had "FALSE" being returned. I then changed the name to "SHIFT U" and everything works now. Any ideas on why this would happen.
Also, thank you for your time and effort in helping with this issue, really appreciated.
 
Hi ,

See the uploaded file ; I think it may be more that U SHIFT is not really U SHIFT ; it may contain leading / trailing spaces or space-like characters.

Narayan
 

Attachments

My apologies, I found the issue. E101, was selected from a validation list and when I checked the list I noticed there appeared to be an extra space between the U and S. This was the issue as the formula did not match what was being selected in E101 from the drop down list. Sorry for any time wasted this may have cause you
 
Thanks again Narayan, problem solved. Stay tuned, next work sheet has five crews, A-E with the same criteria and I always seem to run into issues when string multiple IF statements together.
 
You must log in or register to reply here.
Back
Top