Getting a cell to fill or not using another cells details

[iandb]

Folks

Hopefully this makes some sense!!! Not sure what I should be searching for if there was another thread with a similar problem.

Can any one help with changing a formula currently being used so that it can separate or distinguish between 2 types of data and then put the result

It's basically an order book for our company consisting of a range of dates (received, shipped, etc) and formulas to calculate number of days from receipt to shipping etc. This has been fine with what I've got at the moment as we only had a single product type but we've recently taken over another company so now have 2 product types and I'm trying to use the current formulas but have it distinguish between the two.

The below is a snap shot of the spreadsheet in use. The columns with data in (K onwards) are the previous used (ie single product) and these are all fine. The Yellow headed ones are the new ones I want to fill. The distinguishing factor is a word in column C and is either Red or White (as an example)

The end result I'm hoping for is if you take the first line of data, ROW 4, if the word in column C is White then I want the formulas to work as following:

M4 - cell to be empty
N4 - cell to have a number

I have got K & L and also O 7 P to work as you can see but am really struggling with a couple of columns in the middle, been trying pretty much all day on and off

The current formulas used in are:
K & L - =IF(C7="RED",(NETWORKDAYS(D4,J4,BH!B39:B68)),"") - This is working ok

M - =IF(H4=J4,0,NETWORKDAYS(H4,J4,BH!B3:B68)) - This is where I need help. If I add (C7="RED", at the start and the subsequent closing at the end it just gives me an error message

Need to do the same for column N as well.

Many thanks

 

[AlphaFrog]

Is this what you want but it gives you an error?
=IF(C4="Red",IF(H4=J4,0,NETWORKDAYS(H4,J4,BH!B3:B68)),"")

In your question, you test if C7 is "Red". Is that a typo? Otherwise I don't understand the relationship between C7 and a formula in M4

 

[iandb]

In your question, you test if C7 is "Red". Is that a typo?

Sorry, yep typo. The cell numbers in the formula will all be the same for each row. ie as in bold below.

In my OP ref columns K & L the formula is =IF(C4="RED",(NETWORKDAYS(D4,J4,BH!B39:B68)),"")

Is this what you want but it gives you an error?

=IF(C4="Red",IF(H4=J4,0,NETWORKDAYS(H4,J4,BH!B3:B68)),"")

I've not had the bold bit above in the formula I've been using just the (C4="RED",

Is this likely to be the reason. Am at home now but will give it a go with the extra IF

Mnay thanks

 

[AlphaFrog]

I've not had the bold bit above in the formula I've been using just the (C4="RED",

Is this likely to be the reason. Am at home now but will give it a go with the extra IF

Yes. The new formula first tests if C4 is "Red". If true, it then tests if H4=J4. If that is true then return zero or if false calculate the networkdays.

I don't know if that's what you want, but that's what it does.

 

[iandb]

Yes. The new formula first tests if C4 is "Red". If true, it then tests if H4=J4. If that is true then return zero or if false calculate the networkdays.

I don't know if that's what you want, but that's what it does.

Thanks again. I'm sure that's what I need. If not I'll be back on Monday

The results are used to drive a number of charts, a different problem!!

 

[iandb]

Thanks again. I'm sure that's what I need. If not I'll be back on Monday

The results are used to drive a number of charts, a different problem!!

Thanks for your help, this solved that problem, but i now have another

I have the following formula which is giving me a wrong answer

=IF(P4>15,1,0) and this is giving answer in cell AZ4 as 1

If you look at the screen shot in post 1 cell P4 is empty, so I would expext the anser of the formula to be 0.

If you look at column P the cells which have 0 in them I get the correct answer I need in the corresponding AZ... cell ie a 0

Is this because the cell is actually empty and needs to be populated? Is the formula used correct or should there be a different formula.

Any help greatly appreciated as always.

Ian

 

[AlphaFrog]

If there is a space in P4 or if P4 contains a formula that returns "", then it will evaluate to greater than 15.

Try something like this...
=IF(AND(ISNUMBER(P4),P4>15),1,0)

 

[iandb]

If there is a space in P4 or if P4 contains a formula that returns "", then it will evaluate to greater than 15.

Try something like this...
=IF(AND(ISNUMBER(P4),P4>15),1,0)

Many thanks again.

It looks as though that has fixed it.

Ian